Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I have a column called "CapabilitiesId" in a dataset that is pulled from a Sharepoint list.
The "CapabilitiesId" column is defined in Sharepoint by a tickbox, using a lookup to another list, and allows multiple selections.
Therefore, in the Power BI Power Query Editor, the column is showing as a list, such as below:
UniqueID | Title | CapabilitiesId |
1 | AAA | List |
2 | BBB | List |
I have the ability to "Expand to New Rows", but I then get an error as it will duplicate the UniqueID column and there are relationships that don't allow duplicates.
If I was to click the List, there could be rows with only one value but some rows have multiple values:
My problem is I am trying to create a relationship to the Capabilities table that contains references to "CapabilitiesId", as such:
CapabilitiesId | Capability |
1 | CAP001 |
2 | CAP002 |
3 | CAP003 |
I am looking for suggestions on how I could achieve this?
My thoughts are:
With either of the methods I am not sure how I would implement them. Any help would be much appreciated.
Thank you.
The correct way is to pull the capabilities object from Sharepoint , creating a separate table that you can then join into your data model as appropriate.
Hello,
I already have a capabilities table (as above), I am trying to create a relationship to it using the CapabilitiesId from the "main" table but the IDs are in a list.
This is one of these rare cases where a M:M relationship is required. Try to make it single direction.
Should I extract the values into multiple columns or rows? because they are in a list it won't create the relationship I don't think
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
95 | |
80 | |
77 | |
66 |
User | Count |
---|---|
130 | |
106 | |
105 | |
86 | |
72 |