Fact table column splitting

[Yannick_013]

I have a self made dataset containing Spotify playlist data. In the fact table, I have the song title with the related artist. I also have a dimension table with each artist that is included. The issue lies in this: in the fact table, the artists belonging to a song are in one column, delimited by a comma (,). This is not the case in the dimension table, as there each artist is their own row. For example, in the fact table, an artist for one song could look like this: "Artist A, Artist B", and in the dimension table it would look like this: "Artist A" "Artist B". I did this part in Excel, so that's why they are seperated.

I am trying to use the dimension table artists in a slicer, but this does not filter properly, as in the fact table the ones that are listed as "Artist A, Artist B" are not included. I tried splitting the column into multiple columns in the fact table, but then it only checks the first of those columns with the dimension table, despite having made relationships between all columns.

My question is this: is there any way for Power BI to look into the fact table and see "Artist A, Artist B", and if I select Artist A from the dimension table in a slicer, it will also include "Artist A, Artist B" in the result?

I am quite new to Power BI so my knowledge is limited. If any additional clarification / screenshots are needed, please let me know!

[bhanu_gautam · Accepted solution]

@Yannick_013 Go to the Power Query Editor by clicking on Transform Data.
Select your fact table.
Select the column that contains the comma-separated artist names.
Go to the Transform tab, and click on Split Column > By Delimiter.
Choose the comma (,) as the delimiter and select Each occurrence of the delimiter.
This will split the column into multiple columns. Now, you need to unpivot these columns to create individual rows for each artist.
Select the columns that were created from the split.
Go to the Transform tab, and click on Unpivot Columns.
This will create a new table where each artist is in its own row.

After splitting and unpivoting, ensure that the new table has a column for the song title and a column for the artist.
Close and apply the changes to load the transformed data back into Power BI.
In the Model view, create a relationship between the artist column in the new fact table and the artist column in the dimension table.

[v-bmanikante]

Hi @Yannick_013 ,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,

B Manikanteswara Reddy

[v-bmanikante]

Hi @Yannick_013 ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

B Manikanteswara Reddy

[v-bmanikante]

Hi @Yannick_013 ,

We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

B Manikanteswara Reddy

[v-bmanikante · Accepted solution]

Hello @Yannick_013 ,

Thank you for reaching out to Microsoft Fabric Community Forum.

@bhanu_gautam Thank you for your quick response.

I've attached a PBIX file with sample data based on your scenario. Please check the steps applied in Power Query, where the Artists column in the fact table is split into rows to enable proper filtering through a slicer using the dimension table. 

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

Regards,
B Manikanteswara Reddy

[bhanu_gautam · Accepted solution]

@Yannick_013 Go to the Power Query Editor by clicking on Transform Data.
Select your fact table.
Select the column that contains the comma-separated artist names.
Go to the Transform tab, and click on Split Column > By Delimiter.
Choose the comma (,) as the delimiter and select Each occurrence of the delimiter.
This will split the column into multiple columns. Now, you need to unpivot these columns to create individual rows for each artist.
Select the columns that were created from the split.
Go to the Transform tab, and click on Unpivot Columns.
This will create a new table where each artist is in its own row.

After splitting and unpivoting, ensure that the new table has a column for the song title and a column for the artist.
Close and apply the changes to load the transformed data back into Power BI.
In the Model view, create a relationship between the artist column in the new fact table and the artist column in the dimension table.