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, 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. 

     

     

    • 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.

         

         

  • biengineer's avatar
    biengineer
    Helper I
    3 years ago

    Sir, this is perfect for Rightouter, I do not want to lose rows from either of the tables. How to do it with Fullouter?