cancel
Showing results for 
Search instead for 
Did you mean: 
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
Responsive Resident
Responsive Resident

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.

 

"The commonality between science and art is in trying to see profoundly - to develop strategies of seeing and showing." - Edward Tufte

View solution in original post

2 REPLIES 2
dearwatson
Responsive Resident
Responsive Resident

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.

 

"The commonality between science and art is in trying to see profoundly - to develop strategies of seeing and showing." - Edward Tufte

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
Power BI Show Ep 4 Post Show Carousel.jpg

The Power BI Community Show

Shabnam Watson demos Incremental refresh & Hybrid Tables and Leila Etaati demos Charticulator.

PBI April Release 2022 768x460.png

Check it out!

Click here to read more about the April 2022 updates!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!