Replace all numeric values in multiple columns using #PowerQuery

A quick post more for my future reference. Hoping it helps some one out there!

While I was working on a Power BI report with Commonwealth Games dataset, I came across an issue where I had to replace all numeric values with a text value. There are some very useful blogs posts about replacing multiple values in Power Query. With my issue, I wanted to replace only numeric values. Instead of replacing each column, I tried to replace all!
My data looked like below:

I looked upon all available Replace functions of Text, Lists and Tables. As Imke mentioned on her blog, I decided to go for List.ReplaceMatchingItems. As I wanted to replace only numeric values, I used Value.Is(Value.FromText(_), type number)  to check the Type of value for each row. I replaced value only when my condition returns true. So I combined all required columns into a single column and used Text.ToList function to convert to a List. The condition I used for Type checking works great. But, when I converted the combined columns to Text, then to a List; each character in Text value converted into List as a different List Item. E.g. 12 would convert to {{1},{2}}. When I replace each by checking the data type, I will be replacing 1 and 2 individually, which will cause duplication in the result, like below:

I have to admit; I searched so much to figure out a way to covert Text value to a List using a delimiter.I felt silly after I looked at one my earlier post where I was using Text.Split() function. After a good number of failed attempts, I used Text.Split() function and then I used that List for replacements. And my condition Value.Is(Value.FromText(_), type number) works great.

Here is the script:

let
    Source = Web.Page(Web.Contents("https://en.wikipedia.org/wiki/2018_Commonwealth_Games")),
    Data4 = Source{4}[Data],
    #"Replaced Value" = Table.ReplaceValue(Data4,"#(cr)#(lf)"," -",Replacer.ReplaceText,{"April", "April2", "4 #(lf)Wed", "5 #(lf)Thu", "6 #(lf)Fri", "7 #(lf)Sat", "8 #(lf)Sun", "9 #(lf)Mon", "10 #(lf)Tue", "11 #(lf)Wed", "12 #(lf)Thu", "13 #(lf)Fri", "14 #(lf)Sat", "15 #(lf)Sun", "Events"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value","#(lf)","",Replacer.ReplaceText,{"April", "April2", "4 #(lf)Wed", "5 #(lf)Thu", "6 #(lf)Fri", "7 #(lf)Sat", "8 #(lf)Sun", "9 #(lf)Mon", "10 #(lf)Tue", "11 #(lf)Wed", "12 #(lf)Thu", "13 #(lf)Fri", "14 #(lf)Sat", "15 #(lf)Sun", "Events"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Replaced Value2", [PromoteAllScalars=true]),
    #"Renamed Columns" = Table.RenameColumns(#"Promoted Headers",{{"April_1", "Type"}}),
    #"Removed Bottom Rows" = Table.RemoveLastN(#"Renamed Columns",1),
    #"Filtered Rows" = Table.SelectRows(#"Removed Bottom Rows", each ([Type] <> null)),
    #"Replaced Value1" = Table.ReplaceValue(#"Filtered Rows", each {"4 -Wed", "5 -Thu", "6 -Fri", "7 -Sat", "8 -Sun", "9 -Mon", "10 -Tue", "11 -Wed", "12 -Thu", "13 -Fri", "14 -Sat", "15 -Sun"} ,each if Value.Is(Value.FromText(_), type number) = true then "_" else "_",Replacer.ReplaceText,{"4 -Wed", "5 -Thu", "6 -Fri", "7 -Sat", "8 -Sun", "9 -Mon", "10 -Tue", "11 -Wed", "12 -Thu", "13 -Fri", "14 -Sat", "15 -Sun"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value1", "Custom", each Value.Is(Value.FromText([#"15 -Sun"]), type number)),
    ColumnNames = List.Select(Table.ColumnNames(#"Filtered Rows"), each Text.Contains(_,"-")= true),
    CombineColumns = Table.CombineColumns(#"Filtered Rows",ColumnNames ,Combiner.CombineTextByDelimiter(",", QuoteStyle.None),"Merged"),
    #"Added Custom1" = Table.AddColumn(CombineColumns, "Custom", each Text.Split([Merged],",")),
  #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.1", each List.Transform([Custom], each if Value.Is(Value.FromText(_), type number) = true then "GME" else _)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom2",{"April", "Type", "Events", "Custom.1"}),
    #"Extracted Values" = Table.TransformColumns(#"Removed Other Columns", {"Custom.1", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Custom.1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), ColumnNames )
in
    #"Split Column by Delimiter"

This may not be worthy to be a blog post especially when there are already some good posts covering the similar topic, but I spent a fair bit of time on this so thought of writing a post!

Keep Smiling,

Prathy 🙂