Excel BI Challenge 160 – Brian Julius Solution

gistfile1.txt

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck4sUorViVbyyS9LBTMcS4vyixLBTNeczIpMiLRrSWpRXmZJJURtYnp+fh6YGZSYmZeUXw5mB2fmZOSXppaUAA2KBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Words = _t]),
    NumList = {1..101},
    ToTable = Table.FromList(NumList, Splitter.SplitByNothing(), {"Number"}, null, ExtraValues.Error),
    #"Run R script" = R.Execute("# 'dataset' holds the input data for this script#(lf)#(lf)library(primes)#(lf)df <- dataset#(lf)df$isprime <- is_prime(df$Number)#(lf)df",[dataset=ToTable]),
    #"""df""" = #"Run R script"{[Name="df"]}[Value],
    Filter = Table.SelectColumns(Table.SelectRows(#"""df""", each ([isprime] = true)), "Number"),
    Lookup = Table.FromColumns( {Filter[Number], {"A".."Z"}},{"Value","Letter"}),
    WordTable = Table.DuplicateColumn(Source, "Words", "Word" ),
    Upper = Table.TransformColumns(WordTable,{{"Word", Text.Upper, type text}}),
    AddLetter = Table.AddColumn(Upper, "Letter", each Text.ToList( [Word] )),
    Expand = Table.ExpandListColumn(AddLetter, "Letter"),
    Join = Table.Join( Expand, "Letter", Lookup, "Letter"),
    GroupSum = Table.Group(Join, {"Words"}, {{"Answer", each List.Sum([Value]), type nullable number}})
in
    GroupSum