Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I would need to merge these two tables based on the "Order number" column. The problem, however, is that there are empty cells, so he can't match them. However, this data in the second (empty) table may change and it may happen that the required number will be there. Now, however, I would like to combine these two tables and write something like "Order number is missing" in a new column. Is there a solution in Power Query how can this be done?
Thank you very much for your help.
Solved! Go to Solution.
Hi @Simon_29 ,
I'm assuming it's only your second table that has order number gaps. If so, then the best thing I think would be to merge the two table on order number, but do a FULL OUTER merge type. This will match up records where they can be matched, but will also keep all the rows that couldn't be matched upon which you can apply some logic to label them as unmatched or similar.
If you have blank order numbers in both tables, then you'll probably want to replace blank values in table1 with "noOrderNumber1", and the same in table2 with "noOrderNumber2" or similar, then perform the FULL OUTER merge as before. This will prevent nulls matching with nulls and creating a mess.
Pete
Proud to be a Datanaut!
Hi @Simon_29 ,
I'm assuming it's only your second table that has order number gaps. If so, then the best thing I think would be to merge the two table on order number, but do a FULL OUTER merge type. This will match up records where they can be matched, but will also keep all the rows that couldn't be matched upon which you can apply some logic to label them as unmatched or similar.
If you have blank order numbers in both tables, then you'll probably want to replace blank values in table1 with "noOrderNumber1", and the same in table2 with "noOrderNumber2" or similar, then perform the FULL OUTER merge as before. This will prevent nulls matching with nulls and creating a mess.
Pete
Proud to be a Datanaut!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.