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
slorin's avatar
slorin
Super User
2 years ago

Hi,

 

let
Source = Table.FromRecords({
   [A.1 = 1, B.1 = null, C.1 = 3, A.2 = 1, B.2 = 2, C.2 = 3],
   [A.1 = null, B.1 = 2, C.1 = 3, A.2 = 4, B.2 = 5, C.2 = 6],
   [A.1 = 1, B.1 = null, C.1 = null, A.2 = 7, B.2 = 8, C.2 = 9]
   }),
Replace_null=List.Accumulate(
     List.Select(Table.ColumnNames(Source), each Text.EndsWith(_,".1")),
     Source,
     (state,current) => Table.ReplaceValue(state, null, 
                   each Record.Field(_, Text.Replace(current, ".1", ".2")), (x, y, z) => x??z, {current}))
in
Replace_null

Stéphane