Forum Discussion
Helper Iduplicate lines based on a list value
Hi
today i got a new issue 😉
i got a source table with information that are presented like this
| ID MEASURE | ID_PARAMETER | PARAMETER_NAME | EQT_CONCERNED | Group_Value | Group_SPEC |
| 1 | 1 | PARAM1 | EQT1,EQT2,EQT3 | 50.45 | 100 |
| 2 | 1 | PARAM1 | EQT1,EQT2,EQT3 | 50.69 | 100 |
| 3 | 1 | PARAM1 | EQT1,EQT3 | 60.36 | 100 |
| 4 | 2 | PARAM2 | EQT1,EQT4 | 7.3 | 50 |
| 5 | 2 | PARAM2 | EQT1,EQT5 | 10.84 | 50 |
| 6 | 2 | PARAM2 | EQT5,EQT6,EQT9 | 10.4 | 50 |
| 7 | 2 | PARAM2 | EQT5,EQT6,EQT9 | 5.6 | 60 |
| 8 | 3 | PARAM3 | EQT1,EQT10,EQT9 | 7.5 | 10 |
| 9 | 3 | PARAM3 | EQT2,EQT5 | 4.6 | 10 |
| 10 | 3 | PARAM3 | EQT2,EQT3 | 2.3 | 10 |
| 11 | 3 | PARAM3 | EQT4,EQT10 | 2.5 | 50 |
| 12 | 3 | PARAM3 | EQT6,EQT7 | 54.6 | 50 |
In order to merge information by EQT and do some more calculation i would need to split the list of "EQT_CONCERNED" to get 1 line for each EQT (not split in column, but in rows ...) ... so for exemple line 1 should be transformed into 3 lines
| 1 | 1 | PARAM1 | EQT1 | 50.45 | 100 |
| 1 | 1 | PARAM1 | EQT2 | 50.45 | 100 |
| 1 | 1 | PARAM1 | EQT3 | 50.45 | 100 |
do you have some advise to do it in power bi query ?
thanks in advance for your help
regards
Go into Power Query and:
1) Select your EQT_CONCERNED column, select "Split Column" from the Home tab in the ribbon:
Now select the three new columns created and in the "Transform" tab choose the unpivot option/Unpivot Columns:
Two new columns are created: Rename the "Values Column and delete the Attributes column:
You should be set.
1 Reply
PaulDBrownCommunity Champion
Go into Power Query and:
1) Select your EQT_CONCERNED column, select "Split Column" from the Home tab in the ribbon:
Now select the three new columns created and in the "Transform" tab choose the unpivot option/Unpivot Columns:
Two new columns are created: Rename the "Values Column and delete the Attributes column:
You should be set.