Forum Discussion

biengineer's avatar
biengineer
Helper I
3 years ago
Solved

Merge Queries: Rightouter - Mismatch Columns Null

Dear All,   Need your help.   Thanks in advance.     I am merging two tables based on Date, Line, Article using Right Outer,. Those not match with the first comes as null, I want the date, lin...
Need Help
Show and Tell
Tutorial Requests
  • biengineer's avatar
    biengineer
    3 years ago

    Suppose there are two columns in both tables, the second is Serial#. If I do right outer, vthe serial# should come into table 1 under Serial# along the value instead of nulls except expanded.

     

    If there is any other way, please share.

     

    Thanks in advance.

     

    Regards 

     

     

  • wdx223_Daniel's avatar
    wdx223_Daniel
    3 years ago

    after nestedjoin, do like this, say the new merged column is "Merged"

    =Table.FromRecords(List.TransformMany(Table.ToRecords(NestedJoinStep),each Table.ToRecords([Merged]),(x,y)=>Record.RemoveFields(x,{"Merged"})&y))

     

  • wdx223_Daniel's avatar
    wdx223_Daniel
    3 years ago

    no need addcolumn, just put the code in the new step

    =Table.FromRecords(List.TransformMany(Table.ToRecords(#"Merged Packing"),each Table.ToRecords([Packing]),(x,y)=>Record.RemoveFields(x,{"Packing"})&y))

wdx223_Daniel's avatar
wdx223_Daniel
Community Champion
3 years ago

say, the two tables are tb1 and tb2,and tb1's values are prior to tb2, and changing the join kind to FullOuter, then try this

 

=let cols=List.Distinct(Table.ColumnNames(tb1)&Table.ColumnNames(tb2)) in #table(cols,List.TransformMany(Table.ToRecords(#"Merged Packing"),each Table.ToRecords([Packing]),(x,y)=>List.Transform(cols,each List.RemoveNulls(List.Transform({x,y},(x)=>Record.FieldOrDefault(x,_,null))){0}?)))

biengineer's avatar
biengineer
Helper I
3 years ago

Dear Sir,

 

Looks like it is skipping Fuzzynestedjoin function, repeats the match, and creating duplicate values.