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

Hi BlueSkies3, i know that slorin solved your task but maybe you will also need this:

 

If you have more than 2 pairs of columns and you want to fill all null columns with value from pair column with higher number, this should work.

 

Result

let
Source = Table.FromRecords({
   [A.1 = 1, B.1 = null, C.1 = 3, A.2 = 1, B.2 = null, C.2 = 3, A.3 = 6, B.3 = 3, C.3 = 8],
   [A.1 = null, B.1 = 2, C.1 = 3, A.2 = 4, B.2 = 5, C.2 = null, A.3 = 7, B.3 = 4, C.3 = 2],
   [A.1 = 1, B.1 = 3, C.1 = null, A.2 = 7, B.2 = null, C.2 = 9, A.3 = 6, B.3 = 9, C.3 = 8]
   }),
    Zip = [ a = Table.ColumnNames(Source),
    occurence = List.Skip(List.Reverse(List.Distinct(List.Transform(a, each Text.AfterDelimiter(_, "."))))),
    column = List.Distinct(List.Transform(a, each Text.BeforeDelimiter(_, "."))),
    result = List.Combine(List.Transform(occurence, each List.Zip({ List.Repeat({_}, List.Count(column)), column })))
  ][result],
    StepBack = Source,
    ReplaceNulls = List.Accumulate(Zip, StepBack,
        (s,c)=> Table.ReplaceValue(s, null, 
                      each Record.Field(_, c{1} & "." & Text.From(Number.From(c{0})+1)),
                      (x,y,z)=> x??z, {c{1} & "." & c{0}} ))
in
    ReplaceNulls