Joining and filtering data from multiple sources

[JClishe]

I'm relatively new to Power BI and trying to wrap my head around how to combine, join, and filter data from 3 different datasets into a single report. Here's a quick breakdown of what I'm trying to accomplish:

Dataset 1:

This is a spreadsheet provided by an external vendor that contains customer names and addresses.

Dataset 2:

This is a spreadsheet provided by an external vendor that contains customer names and sales unit details. This data does NOT contain customer address; It has customer names only.

Dataset 3:

This is a spreadsheet from our internal operations that contains a mapping of sales reps to zip codes (hundreds of zip codes per sales rep).

So....ultimately what I'd like to end up with is the sales data from dataset 2 that's filterable by sales rep. I know I need to take zip code and sales rep data from dataset 3, match it to the zip codes and customer names in dataset 1, and then match the customer names from dataset 1 to the customer names in dataset 2 which would ultimately give me the sales data. I hope that all makes sense. Can anyone point me in the right direction to help me figure out how I can build this out?

[MattAllington]

Here is the general process. 

Import table 3 and load it to power bi. This will be your lookup table

load table 1 and set it so it doesn't actually load (connection only). 

Load table 2 the same way as 1

merge table 1 & 2 together then load to power bi. This is your data table

join the data table to the lookup table using post code.  You can then get total sales by rep name

is this enough to help you?  Do you know how to do these things?

[JClishe]

Thanks for the reply! Yes that definitely helps although no, I don't yet know how to do those things (although I'm learning).

[MattAllington]

IT would take a long time to type an explanation.  Much easier just to show you - here is a video.

https://dl.dropboxusercontent.com/u/30711565/how%20to%20join%20tables.wmv

[JClishe]

Thank you! Unfortunately that link isn't working.

[MattAllington · Accepted solution]

That's strange.  I just copied it again and it was exactly the same as last time.  I think it is a forum page issue (this forum does strange things sometimes).  Anyway, I have updated the link and it now works.

[Anonymous]

Hi Matt,

Can you repost the video in the link?  I get an error from Dropbox that the file is no longer available.

[MattAllington]

Here it is https://www.dropbox.com/s/x4c7g7nfl4ogke3/how%20to%20join%20tables.wmv?dl=1

[JClishe]

Awesome, thank you so much! I watched the video, it seems to be spot-on for what I need. I'll give it a shot with my data tomorrow.

Thanks again.

Jason