Combining specific data from 2 spreadsheets

[nlaauw]

Apologies as total newbie - in an ideal world I need the data in spreadsheet 1 (pic 1) to look for a match in spreadsheet 2 (pic 2) and if there is a match, to bring the 5 digit ID code back to spreadsheet 1 in a new column eg the code 41852 to be inserted into spreadsheet 1 next to Aberdeen. Is this possible?

[PhilipTreacy · Accepted solution]

Hi @nlaauw 

Download this sample PBIX file with code.

My sample file loads data into 2 tables and then does a merge to get the ID from the 2nd table into the first

Select the Places query and from the Home tabe in the PQ editor, click on Merge Queries:

Giving a result like this

I don't have access to your original source files so you will need to load those into PBI and merge them, but following my example it should be pretty straight forward.

If you get stuck, post back.

Regards

Phil

[PhilipTreacy]

@nlaauw 

Glad I solved the issue for you.

Regards

Phil

[nlaauw]

Hi Phil, it worked for 984 results but not for 230 or so. I played around with the fuzzy options but as soon as I did this they tended to be mismatched. Is there any other solution you may be aware of? Thank you so much for your help already!

[PhilipTreacy]

Hi @nlaauw 

If it's not matching that's because the same names don't appear in both columns.  Not really sure how you want to get arounds that.  Ifd you want to pullan ID from the 2nd table then you need to have the place name match from the 1st table.

If you have places in your 1st table that aren't in the 2nd, you need to add them to the 2nd table with the relevant ID.

Phil

[PhilipTreacy · Accepted solution]

Hi @nlaauw 

Download this sample PBIX file with code.

My sample file loads data into 2 tables and then does a merge to get the ID from the 2nd table into the first

Select the Places query and from the Home tabe in the PQ editor, click on Merge Queries:

Giving a result like this

I don't have access to your original source files so you will need to load those into PBI and merge them, but following my example it should be pretty straight forward.

If you get stuck, post back.

Regards

Phil

[nlaauw · Accepted solution]

Thanks so much @PhilipTreacy When I merge them, it says 'table' in every row. I noticed in your example of applied steps that it had the same, but then is followed by "expanded codes" however I've looked and can't seem to find that option anywhere. Could you please tell me where that would be?

[nlaauw]

I found it!! Thank you so much!!

[PhilipTreacy]

Hi @nlaauw 

OK so you have 2 spreadsheets you are loading into PBI and you want to pull the ID from one to the other?

Give me 10 minutes as I have to type in all the data, its always best if you can supply sample data as files or tables.

Regards

Phil

[PhilipTreacy]

Hi @nlaauw 

Are you wanting to do this in Excel or in Power BI?

Regards

Phil

[nlaauw]

In Power Bi desktop @PhilipTreacy