Forum Discussion
Power Pivot Relationship Questions
I have a file that is coming from SharePoint with a Multiple Selection field, this translates in excel to:
| Macro;#Process for Web;#Auto-fail |
I'm adding this table to Power Query and then I Split by Delimiter creating multiple rows for some entries, some entries only have 1, some other can have 5 rows. At this point I Made a calculation to create my key, I concatenated the Entry ID with the Name:
| 10=Agent 60 |
This gives me the result I want:
Now, using the Power Pivot relationship, I've made the relationship between the concatenated cells:
And now when trying to add the agent name from Table 2:
I thought the connection should be working like in Power Bi where I can filter from the 1 to the many result, I was looking to have something like:
PD: No idea how to upload a sample file here
And when I try to drag and drop it says
1 Reply
So the relationship between Table2 and Reason Breakdown would technically be 1-to-1, if that type of relationship were supported in Power Pivot?
If so, you simply need to amend the relationship such that the Reason Breakdown table is on the 'one' side. When presented with a 1-to-1 relationship, which is not supported in Power Pivot, it will designate it as a 1-to-many relationship. However, the side which is assigned as '1' is dependent upon the order in which you choose the tables when creating the relationship, and can easily be reversed.
