Spliting a row into two rows based on column value

[Dunner2020]

Hi there,

I have a dimension table that has a 1:M relationship with the fact table as shown in the following picture:

In the fact table, ContributingFactor may contain multiple values as shown in the following picture (red lines show multiple values in a single cell, values are separated by a tab).

The problem is that when I use the dimension table (contributingfactor) as a slicer, and select value from it. It only shows the record of fact table where contributingfactor value appears itself. For example, as shown in the picture below:

In the above picture, it only shows those records of work method when ‘work method’ appears alone in the fact table. It does not show those values when ‘work method’ appears with other values. For example, it did not show a record when contributing factor value in the fact table is ‘work method’ and ‘Service provider -availability’.

I was wondering that is there any way in the power query that we split the one row into two rows if contributingfactor column has multiple values. Each row represents the single value of contributingfactor

Sample file here

[v-jingzhang · Accepted solution]

Hi @Dunner2020 

Please refer to this article: Split by Delimiter into Rows (and Columns) with Power Query

Under Home tab, select Split Column > By Delimiter.

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

[lbendlin]

You can deconstruct the rows in Power Query.  Use Character.FromNumber(9) to find the tabs (or let the Power Query editor do it for you).

Please provide sample data in usable format (not as a picture) if you want sample Power Query Code.