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.
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?
Solved! Go to 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
Proud to be a Super User!
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?
Glad I solved the issue for you.
Regards
Phil
Proud to be a Super User!
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!
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
Proud to be a Super User!
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
Proud to be a Super User!
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?
I found it!! Thank you so much!!
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
Proud to be a Super User!
Hi @nlaauw
Are you wanting to do this in Excel or in Power BI?
Regards
Phil
Proud to be a Super User!
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.
User | Count |
---|---|
112 | |
100 | |
77 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |