Help with using Table.ReplaceValue on several row values under several different Columns

[McJasem]

Hi all,

Hope there's a couple of PowerGenuises that can help me.

I wish to use the Table.ReplaceValue funktion to find and change several different row values under several columns. as show underneath, all values "null", "blank", "0" & "n/a" should be changes to Not Set, across all columns.

SubSegment	Segment	SubCategory	Category
	accessories	0	accessories
RYO	RYO	RYO	n/a
null	0		accessories
accessories	accessories	accessories	null

= Table.ReplaceValue(#"Source",null,"Not Set",Replacer.ReplaceValue,{"SubSegment", "Segment", "SubCategory", "Category"})

= Table.ReplaceValue(#"Source",0,"Not Set",Replacer.ReplaceValue,{"SubSegment", "Segment", "SubCategory", "Category"})

= Table.ReplaceValue(#"Source","","Not Set",Replacer.ReplaceValue,{"SubSegment", "Segment", "SubCategory", "Category"})

= Table.ReplaceValue(#"Source",n/a,"Not Set",Replacer.ReplaceValue,{"SubSegment", "Segment", "SubCategory", "Category"})

I was wondering how i could put all these replace values into one Table.ReplaceValue step - keeping in mind the cleaning shouldn't affect the processing of data, because i have millions of rows.

[CNENFRNL · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRSkxOTi0uzi/KTC0G8gzQRGJ1opWCIv2Boshknn4iWAaqQQGLJlRj8fHySnNylGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SubSegment = _t, Segment = _t, SubCategory = _t, Category = _t]),

    #"Replaced Value" = List.Accumulate(
        Table.ColumnNames(Source),
        Source,
        (s,c) => Table.ReplaceValue(
            s, null, null, (x,y,z) => if List.Contains({"", "0", "N/A", null, "null"}, Text.Trim(x), Comparer.OrdinalIgnoreCase) then "Not Set" else x, {c}
        )
    )
in
    #"Replaced Value"

[CNENFRNL · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRSkxOTi0uzi/KTC0G8gzQRGJ1opWCIv2Boshknn4iWAaqQQGLJlRj8fHySnNylGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SubSegment = _t, Segment = _t, SubCategory = _t, Category = _t]),

    #"Replaced Value" = List.Accumulate(
        Table.ColumnNames(Source),
        Source,
        (s,c) => Table.ReplaceValue(
            s, null, null, (x,y,z) => if List.Contains({"", "0", "N/A", null, "null"}, Text.Trim(x), Comparer.OrdinalIgnoreCase) then "Not Set" else x, {c}
        )
    )
in
    #"Replaced Value"

[edhans]

Ahhh... I didn't even think about using list.accumulate here...

[CNENFRNL]

Yep, I remember having read a blog, saying that recursion/List.Accumulate/List.Generate are only ways to loop in PQ. Seems too few choices; but not too bad at all, it's easy to make a decision.

[edhans]

I'll be curious to see if someone can do this. You can use the below code to replace multiple different values with one value, so I did your logic on the SubSegment field:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRSkxOTi0uzi/KTC0G8gzQRGJ1opWCIv2Boshknn4iWAaqQQGLJlRj8fHySnNylGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SubSegment = _t, Segment = _t, SubCategory = _t, Category = _t]),
    #"Replaced Value" = 
    Table.ReplaceValue(
        Source,
        each if [SubSegment] = ""  or [SubSegment] = null or [SubSegment] = 0 [SubSegment] = "n/a" then [SubSegment] else null,
        "Not Set",
        Replacer.ReplaceValue,
        {"SubSegment"}
    )
in
    #"Replaced Value"

However, I am not sure how you can do that logic on multiple fields to pass the right info to the "find" part of the Table.ReplaceValue function.

And even if you could, now you are throwing in a lot of if/then/else constructs and I am not sure that is going to make it very performant

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[McJasem · Accepted solution]

Hi edhans

Thanks for the swift response and tips💪😎you are a powerbi champ!

Meanwhile I tried another workaround that worked, and I would like to hear your thoughts on it.

= Table.TransformColumns(#"Trimmed Text", {{"EAN Code", each if _ = "" then null else _}}, each if List.Contains({null, "", 0, "n/a"}, _) then "Not Set" else _)

Let me hear if this is a viable solution, although I can see it solved my problem with changing several different values under different columns to one value if found.