Forum Discussion

Dunner2020's avatar
Dunner2020
Post Prodigy
5 years ago
Solved

Spliting a row into two rows based on column value

 

 

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

2 Replies

  • 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.