Power Query Find Replace null across multiple columns return values from other columns

[JediMasterWindu]

 

Within the Power BI (desktop version) Power Query Editor I have a table populated with values across 60 columns. For the purposes of this example I will use 6 columns. 

The goal is to replace null values with value from other columns without adding additional columns.

In essence, perform a Find Replace of null values across the first three columns (Col1A, Col2A, Col3A). The logic is as follows ... FIND null value across ("Col1A", "Col2A", "Col3A"), and REPLACE value w values from other columns ("Col1B", "Col2B", "Col3B").

To accomplish this for a single column I wrote the following M Code:

Table.ReplaceValue(#"Replaced Value", each [Col1A], each if [Col1A] = null then [Col1B] else [Col1A],Replacer.ReplaceValue,{"Col1A"})

This works for a single column, but my question is 

1) How do I combine / write the M Code so it performs the task across all 3 columns in one step?

2) From a refresh performance standpoint, is it more efficient to perform these steps during Power Query Transform as opposed to after the tables have loaded and edit within Table View? The complete dataset will have 800,000+ rows

[ZhangKun · Accepted solution]

let
    源 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY3BDcAwCAN34Z1nA2GWiP3XiONapZItocOGvc2GJezwhKHHapBDIeycL3fRxSRD5PnOS4X8ClOF3sfvgU50PDoeemxVBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A1 = _t, A2 = _t, A3 = _t, B1 = _t, B2 = _t, B3 = _t]),
    更改的类型 = Table.TransformColumnTypes(源,{{"A1", Int64.Type}, {"A2", Int64.Type}, {"A3", Int64.Type}, {"B1", Int64.Type}, {"B2", Int64.Type}, {"B3", Int64.Type}}), 
    DataRecords = Table.ToRecords(更改的类型), 
    ReplaceValue = List.Transform(
        DataRecords, 
        each List.Accumulate(
            {"1".."3"}, _, (s, n) => 
            if Record.Field(s, "A" & n) is null then 
                s & Record.AddField([], "A" & n, Record.Field(s, "B" & n)) 
            else s
        )
    ), 
    CombineRecords = Table.FromRecords(ReplaceValue)
in
    CombineRecords

[AlienSx · Accepted solution]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], 
    to_list = Table.ToList(Source, (x) => List.Split(x, Table.ColumnCount(Source) / 2)), 
    to_table = Table.FromList(
        to_list, 
        (x) => List.Transform(List.Zip({x{0}, x{1}}), (w) => w{0} ?? w{1}) & x{1}, 
        Value.Type(Source)
    )
in
    to_table

[Omid_Motamedise · Accepted solution]

Hi @JediMasterWindu 

an efficent way (if you have lots of data) for solving your problem is using Table.TransformColumns as presented bellow

This is solution for your question, just copy it and past it into the advance editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RY3LDcAgDEN3yZkLqOQzC2L/NWoSl0qxFOw8s5ZIk4AUmlCHHtktA4zR19yPr3Q9L4sYPaMoxsmc58hgkuG537Lyvpq4vv2A8Xc07Rc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Col1A = _t, Col2A = _t, Col3A = _t, Col1B = _t, Col2B = _t, Col3B = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col1A", Int64.Type}, {"Col2A", Int64.Type}, {"Col3A", Int64.Type}, {"Col1B", Int64.Type}, {"Col2B", Int64.Type}, {"Col3B", Int64.Type}}),
    Custom1 = Table.FromRecords(Table.TransformRows(#"Changed Type", each _ & [Col1A =_[Col1A]??_[Col1B] , Col2A =_[Col2A]??_[Col2B],Col3A =_[Col3A]??_[Col3B]]))
in
    Custom1

If this answer helped resolve your issue, please consider marking it as the accepted answer. And if you found my response helpful, I'd appreciate it if you could give me kudos. 

Thank you!

[Anonymous · Accepted solution]

Hi,

Thanks for the solution Omid_Motamedise , AlienSx  and ZhangKun offered, and i want to offer some more information for user to refer to.

hello @JediMasterWindu , you can refer to the following code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("NU+5EQAhCOzF2OAUEKnFsf82jgUMQNxv4Jwm2nozLzyY9/A2d7v95MxeCoV+3oCsGSwmfAX4fn7SII1gpQqGG0oJbnDCsAlkFsGVOcs0oRJQix/HkimBefvSXayFAxC/TTOVNG/yLUAPTLuOvfcH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Col1A = _t, Col2A = _t, Col3A = _t, Col1B = _t, Col2B = _t, Col3B = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col1A", Int64.Type}, {"Col2A", Int64.Type}, {"Col3A", Int64.Type}, {"Col1B", Int64.Type}, {"Col2B", Int64.Type}, {"Col3B", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each let
    a = Record.FieldValues (_),
    b = List.Count(a),
    c = List.Generate(
        () => [x = 0, y = if a{0} = null then a{3} else a{0}],
        each [x] <= b,
        each [
            y = if a{[x]} = null then a{[x]+3}  else a{[x]},x = [x] + 1
        ],
        each [y]
    ),
d=Table.FromList(List.Skip(c,1), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
e=Table.Transpose(d)
in e),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"})
in
    #"Expanded Custom"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Omid_Motamedise · Accepted solution]

Hi @JediMasterWindu 

an efficent way (if you have lots of data) for solving your problem is using Table.TransformColumns as presented bellow

This is solution for your question, just copy it and past it into the advance editor

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RY3LDcAgDEN3yZkLqOQzC2L/NWoSl0qxFOw8s5ZIk4AUmlCHHtktA4zR19yPr3Q9L4sYPaMoxsmc58hgkuG537Lyvpq4vv2A8Xc07Rc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Col1A = _t, Col2A = _t, Col3A = _t, Col1B = _t, Col2B = _t, Col3B = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col1A", Int64.Type}, {"Col2A", Int64.Type}, {"Col3A", Int64.Type}, {"Col1B", Int64.Type}, {"Col2B", Int64.Type}, {"Col3B", Int64.Type}}),
    Custom1 = Table.FromRecords(Table.TransformRows(#"Changed Type", each _ & [Col1A =_[Col1A]??_[Col1B] , Col2A =_[Col2A]??_[Col2B],Col3A =_[Col3A]??_[Col3B]]))
in
    Custom1

If this answer helped resolve your issue, please consider marking it as the accepted answer. And if you found my response helpful, I'd appreciate it if you could give me kudos. 

Thank you!

[AlienSx · Accepted solution]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], 
    to_list = Table.ToList(Source, (x) => List.Split(x, Table.ColumnCount(Source) / 2)), 
    to_table = Table.FromList(
        to_list, 
        (x) => List.Transform(List.Zip({x{0}, x{1}}), (w) => w{0} ?? w{1}) & x{1}, 
        Value.Type(Source)
    )
in
    to_table

[ZhangKun · Accepted solution]

let
    源 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY3BDcAwCAN34Z1nA2GWiP3XiONapZItocOGvc2GJezwhKHHapBDIeycL3fRxSRD5PnOS4X8ClOF3sfvgU50PDoeemxVBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A1 = _t, A2 = _t, A3 = _t, B1 = _t, B2 = _t, B3 = _t]),
    更改的类型 = Table.TransformColumnTypes(源,{{"A1", Int64.Type}, {"A2", Int64.Type}, {"A3", Int64.Type}, {"B1", Int64.Type}, {"B2", Int64.Type}, {"B3", Int64.Type}}), 
    DataRecords = Table.ToRecords(更改的类型), 
    ReplaceValue = List.Transform(
        DataRecords, 
        each List.Accumulate(
            {"1".."3"}, _, (s, n) => 
            if Record.Field(s, "A" & n) is null then 
                s & Record.AddField([], "A" & n, Record.Field(s, "B" & n)) 
            else s
        )
    ), 
    CombineRecords = Table.FromRecords(ReplaceValue)
in
    CombineRecords