Forum Discussion

BlueSkies3's avatar
BlueSkies3
Frequent Visitor
2 years ago
Solved

Multiple null replacements from column pairs

Hi, I'm trying to solve a fairly simple problem, but I've not been able to find the right combination of commands in the forum so far.   I have many columns in the format {"A.1", "B.1", "C.1", ...}...
Need Help
Show and Tell
Tips and Tricks
  • dufoq3's avatar
    dufoq3
    2 years ago

    Hi BlueSkies3, try this:

     

    Edit these 3 steps. You can probably delete ReplacedBlankToNull step.

     

    Before

     

    After

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRcjYCEcZAIsQQRIC4IcZKsTrRSs6GIBGwNEQeogCiAq7EEGwIWBSiCKoKqgyoLhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Cat1 [r]" = _t, #"Cat2 [r]" = _t, #"Cat3 [r]" = _t, TargetCat_No1 = _t, TargetCat_No2 = _t, TargetCat_No3 = _t]),
    nullReplacementColumns = {"Cat1 [r]", "Cat2 [r]", "Cat3 [r]"},
    valReplacementColumns = {"TargetCat_No1", "TargetCat_No2", "TargetCat_No3"},
    StepBack = Source,
    ReplacedBlankToNull = Table.ReplaceValue(StepBack,"",null,Replacer.ReplaceValue,{"Cat1 [r]", "Cat2 [r]", "Cat3 [r]"}),
    ReplaceValuesFromTarget = List.Accumulate( 
        List.Zip({ nullReplacementColumns, valReplacementColumns }),
        ReplacedBlankToNull,
        (s,c)=> Table.ReplaceValue(s, null, each Record.Field(_, c{1}) , (x,y,z)=> x??z, {c{0}}) )
in
    ReplaceValuesFromTarget
dufoq3's avatar
dufoq3
Community Champion
2 years ago

You should create column pairs table i.e.:

We can help you but you should create new sample with real column names - just use dummy data.

 

FromTo
Column1

Column5

Column2

Column6

Column3

ColumnXX

BlueSkies3's avatar
BlueSkies3
Frequent Visitor
2 years ago

Thank you very much! As an example, could I map from "nullReplacementColumns" to "valReplacementColumns"? I guess there is a way to zip the columns together or similar? 

 

nullReplacementColumns = {"Cat1 [r]", "Cat2 [r]", "Cat3 [r]"}
valReplacementColumns ={"TargetCat_No1", "TargetCat_No2", "TargetCat_No3"}

 

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    Hi BlueSkies3, try this:

     

    Edit these 3 steps. You can probably delete ReplacedBlankToNull step.

     

    Before

     

    After

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRcjYCEcZAIsQQRIC4IcZKsTrRSs6GIBGwNEQeogCiAq7EEGwIWBSiCKoKqgyoLhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Cat1 [r]" = _t, #"Cat2 [r]" = _t, #"Cat3 [r]" = _t, TargetCat_No1 = _t, TargetCat_No2 = _t, TargetCat_No3 = _t]),
    nullReplacementColumns = {"Cat1 [r]", "Cat2 [r]", "Cat3 [r]"},
    valReplacementColumns = {"TargetCat_No1", "TargetCat_No2", "TargetCat_No3"},
    StepBack = Source,
    ReplacedBlankToNull = Table.ReplaceValue(StepBack,"",null,Replacer.ReplaceValue,{"Cat1 [r]", "Cat2 [r]", "Cat3 [r]"}),
    ReplaceValuesFromTarget = List.Accumulate( 
        List.Zip({ nullReplacementColumns, valReplacementColumns }),
        ReplacedBlankToNull,
        (s,c)=> Table.ReplaceValue(s, null, each Record.Field(_, c{1}) , (x,y,z)=> x??z, {c{0}}) )
in
    ReplaceValuesFromTarget
    • BlueSkies3's avatar
      BlueSkies3
      Frequent Visitor
      2 years ago

      Thank you very much! I will try this out. Can I ask what the reason is for referencing StepBack to Source rather than passing Source directly? Thanks.

      • dufoq3's avatar
        dufoq3
        Community Champion
        2 years ago

        It is not necessary to use StepBack, but it is more readable with it, because you imediately notice that there are some "helper" steps.