Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Advocate II
Advocate II

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



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? 



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:


@ 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

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors