Power Query Consolidation Question

[jtao7387]

Dear Power Query Experts,

I have a question on 'selective consolidation' of 2 files into 1 file. 

THe Issue: (Refer below screen shot): 

- Excel file 'Book 2' is the original file with 5 columns - Mat No; A; B; C; D

- Now, the business submits another file called 'Book 2 - updated' to make some changes to 'Book 2'. Lets say they only want to amend cell C3 and cell D4 so they only change the value of these 2 cells to a new value (red line highlight in screen shot)

- The other cells in 'Book 2-updated' are left as blank which means NO DATA CHANGE.

Question:

How can I combine "Book 2" and "Book 2 - updated" using Power Query so the consolidated file only updates cell C3 and D4 (where the data has changed) and keep the original data as is based on 'Book 2'? My final consolidated sheet should show all data as per 'Book 2' except the data updated in cell C3 and D4 as per 'Book 2 - updated'.

The reason the business doesn't want to re-enter all the values (showing 'null' in screenshot) that hasn't changed, is because it creates unnecessary work and may cause data entry errors. So they only submit those cells where data has changes (eg. cell C3 and D4).

thank you in advance, appreciate a quick reply.

Jack

[v-stephen-msft · Accepted solution]

Hi @jtao7387 ,

My workaround works but is not easy enough.

Here're the steps.

First separate the two tables based on the source name and merge.

Expand the column needed.

Then add two conditional columns to substitue.

Remove the columns unneeded and rename the conditional columns.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jtao7387]

HI KNP,

thanks for your reply. If you "Filter out any nulls as required" would Power Query keep the nulls values as the original file version and only replace the cells (C3 & D4) in the consolidated version?

I can't find where i can upload the excel files? could you please let me know?

thanks,

[KNP]

Hi @jtao7387,

I'm really sorry, I missed your reply.

Upload files to OneDrive or similar and share.

It's quite a complex question, so rather than try to answer in theory, I'd rather have files to work with. 

[KNP]

(I'm answering from my phone so can't provide an example at the moment)

I would look at keeping either the created or modified date time from the files.

Use this date time to identify the latest version.

Filter out any nulls as required.

If you can provide a couple of sample files or data to work with it would be easier to answer.

Let me know if you have any questions about this method, I'll see if I can provide an example when I'm back at my computer.