cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JClishe Regular Visitor
Regular Visitor

Joining and filtering data from multiple sources

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Joining and filtering data from multiple sources

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.



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
8 REPLIES 8
Super User
Super User

Re: Joining and filtering data from multiple sources

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?

 

 

 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
JClishe Regular Visitor
Regular Visitor

Re: Joining and filtering data from multiple sources

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

Super User
Super User

Re: Joining and filtering data from multiple sources

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

 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
JClishe Regular Visitor
Regular Visitor

Re: Joining and filtering data from multiple sources

Thank you! Unfortunately that link isn't working.

Super User
Super User

Re: Joining and filtering data from multiple sources

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.



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
JClishe Regular Visitor
Regular Visitor

Re: Joining and filtering data from multiple sources

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

Highlighted
mhodges720 Frequent Visitor
Frequent Visitor

Re: Joining and filtering data from multiple sources

Hi Matt,

 

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

Super User
Super User

Re: Joining and filtering data from multiple sources

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



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.