Forum Discussion
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.
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
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))
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))
16 Replies
That would be an Anti-Join.
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.Looks I need to follow Fullouter for all the tables to join with Table1, and create series of if statements.
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:This is how a full outer would look. What are you expecting?
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.Sir, you are right that nulls come with mismatch values, instead of nulls I need other columns from the second table for mismatched values
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))
