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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
SHDJason
Advocate II
Advocate II

How to create a look up table of distinct values from values in two different tables?

Hi,

 

I am having a hard time coming up with a good way to handle this.  

 

I have two source tables--one for web data and one for call data.  

 

They share a common column called "tactic". I want to be able to aggregate by tactic across both tables.

 

For instance:

 

placement #1 search produced 5 leads from web visits  

placement #2 banner ads produced 7 leads from phone calls 

placement #3 social ads produced 8 leads from web vistis and phone calls combined

 

I can't simply append the data because the other columns in the tables are very different. 

 

What I thought would be the right approach is to create a lookup table of distinct values for tactic that they could share (same as the date look up table).  

 

Is that the right approach? And if so, how do i do it? I can create a unique table from one source, but can't figure out how to create a unique table from two sources. Is there a better way to do this? 

 

Thanks.

1 REPLY 1
Greg_Deckler
Super User
Super User

Your approach is sound. Basically what you want is a query that pulls only the "tactic" column from your first data source. Then you create an Append query that refers to your first query and then pulls the "tactic" column from your second data source and then you remove duplicates. Probably best to use the technique I posted here to put it all into one query. While the blog post is for Merge queries, it also works for Append queries. Same basic technique:

 

http://social.technet.microsoft.com/wiki/contents/articles/32915.power-bi-merge-query-with-m.aspx

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors