cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
biengineer
Helper I
Helper I

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, line, and style also from the second table for those do not match with the first.

 

 

 

biengineer_0-1664382556551.png

 

 

biengineer_2-1664382580361.png

 

3 ACCEPTED SOLUTIONS
biengineer
Helper I
Helper I

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 

 

 

View solution in original post

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_0-1664423794745.png

 

View solution in original post

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

View solution in original post

16 REPLIES 16
wdx223_Daniel
Super User
Super User

can use other code to resolve this before you expand the table column

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 

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_0-1664423794745.png

 

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

 

biengineer_0-1664425571365.png

 

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

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

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. 

 

biengineer_0-1664464198303.png

 

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

Dear Sir,

 

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

 

biengineer_0-1664647646542.png

 

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

biengineer
Helper I
Helper I

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 

 

 

It does. Look at the tables above. Bananas and Dates exist in both tables, so they came through. Any additional fields (expiration date, quantity on hand, etc.) would also come through. The only time you get null is when there is no match.

Please post examples of what you are doing using the directions below. thanks!

 

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Sir, you are right that nulls come with mismatch values, instead of nulls I need other columns from the second table for mismatched values

biengineer
Helper I
Helper I

With full outer, still gives all nulls except the values come from expansion. Very confused how to do it.

If there is no match, the only available result is null. 
Consider these tables:

edhans_0-1664385114807.png

This is how a full outer would look. What are you expecting?

edhans_1-1664385200388.png

 






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
edhans
Super User
Super User

That would be an Anti-Join. 

edhans_0-1664382996661.png


You cannot do two types of joins at once. Perhaps you should do a FULL OUTER where everything comes in, then create another column with a series of if/then/else statements to return true/false to know what to keep, then filter on that column.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Looks I need to follow Fullouter for all the tables to join with Table1, and create series of if statements.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors