Brian Julius Solution to Crispo Mwangi January 28 2024 Power BI Challenge

gistfile1.txt

RawData Query = 
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LcvLDQAgDALQXXr2YD9qOkvj/mtooKcXAlRJTLmjxA7wACuTOjjaIyMK+hmLU3bebKDsotPnPg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Quantity = _t]),
    ReType = Table.TransformColumnTypes(Source,{{"Quantity", Int64.Type}}),
    AddIndex = Table.AddIndexColumn(ReType, "Index", 1, 1, Int64.Type)
in
    AddIndex


Table Query
let
    Source = Rawdata,
    #"Run R script" = R.Execute("library(aplpack)#(lf)library(magick)#(lf)#(lf)# Function to capture the console output#(lf)capture_console_output <- function(expr) {#(lf)  temp <- tempfile()#(lf)  sink(temp)#(lf)  on.exit(sink())#(lf)  eval(expr)#(lf)  lines <- readLines(temp)#(lf)  unlink(temp) # Clean up the temp file#(lf)  return(paste(lines, collapse=""\n""))#(lf)}#(lf)#(lf)# Set your working directory and read the data#(lf)setwd(""C:\\Temp"")#(lf)df <- dataset#(lf)QuantList <- df$Quantity#(lf)#(lf)# Capture stem.leaf output and create image for style ""bare""#(lf)output1 <- capture_console_output({#(lf)  stem.leaf(QuantList, style = ""bare"", unit = 1, depths = FALSE, m = 1, printresult = TRUE)#(lf)})#(lf)image1 <- image_blank(width = 800, height = 600, color = ""#FFFFFF"") |>#(lf)  image_annotate(text = output1, size = 18, color = ""#000000"", font = ""Consolas"") |> image_trim()#(lf)image_write(image1, ""xstemleaf1.png"")#(lf)#(lf)# Capture stem.leaf output and create image for style ""Tukey""#(lf)output2 <- capture_console_output({#(lf)  stem.leaf(QuantList, style = ""Tukey"", unit = 1, depths = TRUE, printresult = TRUE)#(lf)})#(lf)image2 <- image_blank(width = 800, height = 600, color = ""#FFFFFF"") |>#(lf)  image_annotate(text = output2, size = 18, color = ""#000000"", font = ""Consolas"") |> image_trim()#(lf)image_write(image2, ""xstemleaf2.png"")#(lf)#(lf)# Capture stem.leaf.backback output and create image#(lf)output3 <- capture_console_output({#(lf)  stem.leaf.backback(QuantList, printresult = TRUE)#(lf)})#(lf)image3 <- image_blank(width = 800, height = 600, color = ""#FFFFFF"") |>#(lf)  image_annotate(text = output3, size = 18, color = ""#000000"", font = ""Consolas"") |> image_trim()#(lf)image_write(image3, ""xstemleaf3.png"")",[dataset=Source])
in
    #"Run R script"

bin1 Query
let
    Source = Folder.Files("C:\Temp\bin1"),
    ConcatFileName = Table.AddColumn(Source, "File", each [Folder Path] & [Name]),
    AddIndex = Table.AddIndexColumn(ConcatFileName, "Index", 0, 1, Int64.Type),
    AddBase64 = Table.AddColumn(AddIndex, "Base64", each "data:image/png;base64," &
Binary.ToText(
File.Contents( [File] ), BinaryEncoding.Base64
)),
    InsertTextLength = Table.AddColumn(AddBase64, "Length", each Text.Length([Base64]), Int64.Type),
    AddStemandLeaf = Table.AddColumn(InsertTextLength, "StemLeafDesc", each if [Index] = 0 then "Bare Stem and Leaf Plot" else if [Index] = 1 then "Tukey Stem and Leaf Plot" else if [Index] = 2 then "Back-to-Back Stem and Leaf Plot" else "Unknown")
in
    AddStemandLeaf