Create multiple columns from rows

[Anonymous]

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

[Anonymous · Accepted solution]

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

[amitchandak]

@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

[Anonymous]

Thanks ! What about diplaying the correct Value chain, as sumx does not handle string values?