Forum Discussion
Create multiple columns from rows
Hello All,
I have the following table. My current key is a combination of ID & Type. For each type, I have to get 7 columns with the following schema (type + each following column) :
- Coal Value Chain
- Coal threshold type
- Coal threshold value
- Coal absolute
- Coal relative
- Coal new
.....
In this example, I want to end up with 8*6 columns (coal, oil, tobacco, other_fossil_fuel,cannabis,gas,unconventionnal_weapon,PORNOGRAPHY_ADULT_ENTERTAINMENT_SERVICES)*(value chain, threshold type, threshold value, absolute, relative, new) and two line, one for each ID (1000 and 1002).
| ID | Type | Value chain | threshold type | threshold value | absolute | relative | new |
| 1000 | COAL | U;P;D | R | 0.2 | 10 | 0.2 | Y |
| 1000 | OIL | U;P | C | null | null | null | Y |
| 1000 | TOBACCO | U;P;D | R | 0.3 | null | null | null |
| 1000 | OTHER_FOSSIL_FUEL | U;P | C | null | null | null | Y |
| 1000 | CANNABIS | P | R | 0.3 | null | null | null |
| 1000 | GAS | U;P | C | null | null | null | Y |
| 1000 | UNCONVENTIONAL_WEAPONS | U;P;D | R | 0 | null | null | null |
| 1002 | UNCONVENTIONAL_WEAPONS | U;P;D | R | 0 | null | null | null |
| 1002 | PORNOGRAPHY_ADULT_ENTERTAINMENT_SERVICES | U;P;D | R | 0.05 | null | null | null |
| 1002 | TOBACCO | U;P;D | R | 0.3 | null | null | null |
| 1002 | CANNABIS | P | R | 0.3 | null | null | null |
| 1002 | COAL | U;P;D | R | 0.2 | 10 | 0.2 | Y |
| 1002 | GAS | U;P | C | null | null | null | Y |
| 1002 | OTHER_FOSSIL_FUEL | U;P | C | null | null | null | Y |
| 1002 | OIL | U;P | C | null | null | null | Y |
I have tried to pivot columns... but I can't figure out how to deal with this. I am able to create the required columns but I can't find a way to group by ID so that I only get one row with the correct data.
Any idea on this?
Thanks,
Aryagon
Hi Anonymous ,
Here are the steps you can follow：
1. In power query – select [schema] – Spliy Column – By Delimiter.
Split at : Left – most delimiter.
2. Select [schema.2] – Transform – Pivot Column.
Values Column – [Flag]
Aggreate Value Function – Don’t Aggregate
3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
3 Replies
Anonymous , You can create a new column with group values
example
Sumx(filter(table, Table[ID] = earlier(Table[ID])) , Table[threshold value] )
Power BI DAX- Earlier, I should have known Earlier: https://youtu.be/CVW6YwvHHi8
Hi Anonymous ,
Here are the steps you can follow：
1. In power query – select [schema] – Spliy Column – By Delimiter.
Split at : Left – most delimiter.
2. Select [schema.2] – Transform – Pivot Column.
Values Column – [Flag]
Aggreate Value Function – Don’t Aggregate
3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
