Forum Discussion
Creating a lookup table from data table
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.
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.
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.