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

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

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

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!

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.