Hi Everyone!
I am relatively new to PowerBI, and while I have found these forums very useful I can't seem to find a solution for what I am trying to do. This could be because I don't know the right terms to look for, so if there is something already existing happy to be referred to that.
I am trying to create a report based on a DirectQuery from Dynamics. I am pulling three separate direct query datasets, I can't give the actual data due to privacy but i've created fake data to illustrate:
| Account | | | Contact | | | | Connection | | |
| Entity Name | Entity ID | | Entity Name | Full Name | Email | | Connection From | Connection To | Role |
| KFC | 1 | | KFC | Kylie | kylie@kfc | | KFC | Kylie | CEO |
| Macca's | 2 | | KFC | Kane | kane@kfc | | KFC | Kane | EA |
| Macca's | 2 | | Macca's | Jennifer | jennifer@maccas | | Macca's | Jennifer | CEO |
| Macca's | 2 | | Macca's | James | james@maccas | | Macca's | James | EA |
| Macca's | 2 | | Macca's | Julie | julie@maccas | | Macca's | Julie | VP |
| Hungry Jacks | 3 | | Hungry Jacks | Sarah | sarah@hj | | Hungry Jacks | Sarah | CEO |
| Hungry Jacks | 3 | | Hungry Jacks | Sally | sally@hj | | Hungry Jacks | Sally | VP |
There are two relationships active:
1. Account[Entity Name] to Connection[Connection From] - 1 (Account) to Many Relationship (Connection)
2. Contact [Full Name] to Connection[Connection To] - 1 (Contact) to Many Relationship (Connection)
Currently when i put together a table in PowerBI I get a column like the above, where the Entity Name is duplicated for every person and role type. However I am trying to prepare a table that will show a unique list of Entities, with data from both other datasets as follows:
| Entity Name | Entity ID | CEO Full Name | CEO Email | VP Full Name | VP Email | EA Full Name | EA Email |
| KFC | 1 | Kylie | kylie@kfc | | | Kane | kane@kfc |
| Macca's | 2 | Jennifer | jennifer@maccas | Julie | julie@maccas | James | james@maccas |
| Hungry Jacks | 3 | Sarah | sarah@hj | Sarah | sarah@hj | | |
Any help would be greatly
