Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JClishe
Helper II
Helper II

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

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 an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

8 REPLIES 8

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 an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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

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 an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Thank you! Unfortunately that link isn't working.

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 an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Hi Matt,

 

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

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



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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.