Multiple null replacements from column pairs

[BlueSkies3]

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", ...}, some of which are filled with null - I'd like to replace these values from a corresponding column, let's call them {"A.2", "B.2", "C.2", ...}. I can replace them one by one, like in the toy example here: 

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]
    }),
    #"Replaced nulls in A.1" = Table.ReplaceValue(Source, each [A.1], each if [A.1] = null then [A.2] else [A.1], Replacer.ReplaceValue, {"A.1"}),
    #"Replaced nulls in B.1" = Table.ReplaceValue(#"Replaced nulls in A.1", each [B.1], each if [B.1] = null then [B.2] else [A.1], Replacer.ReplaceValue, {"B.1"}),
    #"Replaced nulls in C.1" = Table.ReplaceValue(#"Replaced nulls in B.1", each [C.1], each if [C.1] = null then [C.2] else [C.1], Replacer.ReplaceValue, {"C.1"})
in
  #"Replaced nulls in C.1"

i.e., I'd like to replace nulls in column A.1 with the corresponding row in column A.2, etc.

But as I have many tens of columns and thousands of rows, I'd like to do the above steps in in a more efficient manner, if possible. Is there a way to fill in the nulls from the paired columns in one step? I was thinking there must be a way to iterate through a paried mapping of columns or similar (such as in a zipped list), but I couldn't get the formula set up correctly. Please can you give me some advice?

[AlienSx]

using Table.TransformRows

let
    s = your_table,
    nullReplacementColumns = {"Cat1 [r]", "Cat2 [r]", "Cat3 [r]"},
    valReplacementColumns = {"TargetCat_No1", "TargetCat_No2", "TargetCat_No3"},
    f = (r) => Record.TransformFields(
        r, List.Transform(
            List.Zip({nullReplacementColumns, valReplacementColumns}),
            (x) => {x{0}, (w) => w ?? Record.Field(r, x{1})}
        )
    ),
    z = Table.FromRecords(Table.TransformRows(s, f))
in
    z

[BlueSkies3]

Thank you both for your suggestions. The column names were actually just examples. Please could you show me how I might be able to map from and to arbitrary columns, where a simple replacement of ".1" --> ".2" won't work? Thank you very much!

[dufoq3]

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.

From	To
Column1	Column5
Column2	Column6
Column3	ColumnXX

[BlueSkies3]

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 · Accepted solution]

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]

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]

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

[dufoq3]

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

[slorin]

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