Create relationship to a list column

[Toll]

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:

• Extract values and split into multiple columns
  • The issue with this is, currently, the maximum selected is 2, but this could be 3 or more in future so would need to accomodate
• Extract values and have values separated, but in one column
  • Unsure if this method would actually allow relationships though?

With either of the methods I am not sure how I would implement them. Any help would be much appreciated.

Thank you.

[lbendlin]

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.

[Toll]

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.

[lbendlin]

This is one of these rare cases where a M:M relationship is required. Try to make it single direction.

[Toll]

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