Hello all,
I have tried some extensive searching on this topic but can't find a solution anywhere. What I'm trying to do is marry up two different data types, find the commonality between them and append this to one of the datasets. Essentially have a second "reference list" from which a value is looked up and appended to an extra column in the master list. Both data sources are excel that have been connected within the PBI Desktop file. By way of explanation:
Dataset 1 (the "master" dataset)
| Category | Completed | LevelReference |
| Plastering | Yes | TJ01 |
| Plastering | Yes | TJ01 |
| Plastering | Yes | TJ02 |
| Decorating | Yes | TJ01 |
| Decorating | Yes | TJ03 |
Dataset 2 (the "reference" dataset)
| LevelReference | Expected Records |
| TJ01 | 4 |
| TJ02 | 4 |
| TJ03 | 4 |
| TJ04 | 6 |
Then grab data from "LevelReference" from the Dataset 2 and apply into the "Expected Completions" column in Dataset 1:
| Category | Completed | LevelReference | Expected Records |
| Plastering | Yes | TJ01 | 4 |
| Plastering | Yes | TJ01 | 4 |
| Plastering | Yes | TJ02 | 4 |
| Decorating | Yes | TJ01 | 4 |
| Decorating | Yes | TJ03 | 6 |
What I can then do is is calculate the number of records against the expected number for a given level, e.g. on TJ01 I am expecting 4, I can use some basic PBI to calculate there are 3 records on TJ01, so 75% completion on that level. I think this part is fairly easy to manipulate, it's effectively doing a "VLOOKUP" type activity from the master list to the reference list to assign an expected number of records to each record so the data is available. I have tried to connect the datasets separately and analyse from there, but can't get it to work so I think I need the data in a single table.
Thank you and hopefully this makes sense!
