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 underneath, all values "null", "blank", "0" & "n/a" should be changes to Not Set, across all columns.

 

SubSegmentSegmentSubCategoryCategory
 accessories0accessories
RYORYORYOn/a
null0 accessories
accessoriesaccessoriesaccessoriesnull

 

= 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.

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.

     

     

5 Replies

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    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"

    • edhans's avatar
      edhans
      Community Champion
      5 years ago

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

      • CNENFRNL's avatar
        CNENFRNL
        Community Champion
        5 years ago

        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's avatar
    edhans
    Community 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.