Merging tables with empty cells

[Simon_29]

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.

[BA_Pete · Accepted 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

[Simon_29]

Hi,
I've already solved it, @BA_Pete.

Thank you very much for your help 🙂