Creating a lookup table from data table

[Caitlin_Knox]

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.

[dearwatson · Accepted solution]

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"

4. merge on the customer key

5. expand the merge columns

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

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]

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.