Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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

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 u...
Need Help
Show and Tell
Tips and Tricks
  • CNENFRNL's avatar
    CNENFRNL
    5 years ago
    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"

  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

     

     

edhans's avatar
edhans
Icon for Community Champion rankCommunity Champion
5 years ago

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.