Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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))
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))
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"}))))
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.
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.
Sir, this is perfect for Rightouter, I do not want to lose rows from either of the tables. How to do it with Fullouter?
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingSir, you are right that nulls come with mismatch values, instead of nulls I need other columns from the second table for mismatched values
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?
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThat 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingLooks I need to follow Fullouter for all the tables to join with Table1, and create series of if statements.