Forum Discussion

nlaauw's avatar
nlaauw
Regular Visitor
4 years ago
Solved

Combining specific data from 2 spreadsheets

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 sprea...
Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    4 years ago

    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's avatar
    nlaauw
    4 years ago

    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's avatar
nlaauw
Regular Visitor
4 years ago

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's avatar
PhilipTreacy
Super User
4 years ago

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