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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Caitlin_Knox
Advocate III
Advocate III

Creating a lookup table from data table

I have two files. One is several files merged into one [Merged] and the other is just one big file [Big]. The files that make up Merged were once extracted from the Big file, separated by region. Updates were made to the indivudal files and not the Big file. Now the task is to update the Big file with the contents of the Merged file. The only similar value between the two is the Customer name column. What I've done so far was create a separate Customers table (from both files, removed duplicates), and added an ID field- just starting at 1. I then used VLOOKUP to populate this new field on both the Merged and Big files. Now my data model has 3 connections, Big, Merged, and Customers. I have two relationships- Customers:Big & Customers:Merged, 1:N, filtered both ways. I am still having relationship issues trying to build a table using columns from Big & Merged. What am I missing? I also can't create custom columns using both data points either.

 

Thanks in advance for your help.

1 ACCEPTED SOLUTION
dearwatson
Continued Contributor
Continued Contributor

Instead of using the modelling engine (relationships) I suggest you use Power Query (The Query Editor) for this type of data tranformation... thats what it is built to do 🙂

 

Assuming you have a common customer key bring in both tables [big] [merged] as seperate queries...

1. open the query editor

2. select the [big] query

3. select "Merge Queries"Capture.PNG

 

 

4. merge on the customer key Capture.PNG

 

 

5. expand the merge columnsCapture.PNG

 

 

 

6. create a calculated column for the data e.g. if merged.data = null then big.data else merge.data 

 

Capture.PNG

 

anyway it will be something like that... this is a simple example but you can use power query to do all sorts of cool stuff. worth learning or check out the blogs like www.powerpivotpro.com which have great learning resources for this.

 

View solution in original post

2 REPLIES 2
dearwatson
Continued Contributor
Continued Contributor

Instead of using the modelling engine (relationships) I suggest you use Power Query (The Query Editor) for this type of data tranformation... thats what it is built to do 🙂

 

Assuming you have a common customer key bring in both tables [big] [merged] as seperate queries...

1. open the query editor

2. select the [big] query

3. select "Merge Queries"Capture.PNG

 

 

4. merge on the customer key Capture.PNG

 

 

5. expand the merge columnsCapture.PNG

 

 

 

6. create a calculated column for the data e.g. if merged.data = null then big.data else merge.data 

 

Capture.PNG

 

anyway it will be something like that... this is a simple example but you can use power query to do all sorts of cool stuff. worth learning or check out the blogs like www.powerpivotpro.com which have great learning resources for this.

 

ImkeF
Community Champion
Community Champion

I can also recommend to check out the Join Kinds: LeftAnti or RightAnti (see picture to 4)

 

They just merge those rows of the other table who are NOT contained in the table. No need to write any conditional filter-columns. then.

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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