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))

biengineer's avatar
biengineer
Helper I
3 years ago

Sir, may you please explain the code you are talking about?

 

The idea is to have both tables in one table with all values with their respective columns values in second table whether match or mismatch.

 

Best regards 

wdx223_Daniel's avatar
wdx223_Daniel
Community Champion
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))

 

  • biengineer's avatar
    biengineer
    Helper I
    3 years ago

    Sir, here are the results. 

     

    Looks weird.  I do not know what mistake I have made.

     

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

     

     

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

    • wdx223_Daniel's avatar
      wdx223_Daniel
      Community Champion
      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))

      • biengineer's avatar
        biengineer
        Helper I
        3 years ago

        Dear sir,

         

        The FuzzyNestedJoin worked well, I am applying this to fullouter and it misses the values from the First Table in Date and Art columns.