Pivot multiple value types and KPIs

[JK07]

Dear PBI Community,

Im trying to pivot some column in POwer BI query to transform the data and have extra column created with the combination of Value type and column with sotred values.
The initial data looks following:

ID	Attribute 1	Attribute 2	Attribute 3	Attribute 4	Attribute 5	Attribute 6	Value type	Company Part	Vendor Part	System
A	attribute 1	bttribute 1	cttribute 1	dttribute 1	ettribute 1	fttribute 1	Actual	100	200	300
A	attribute 1	bttribute 1	cttribute 1	dttribute 1	ettribute 1	fttribute 1	Budget	23	93	116
A	attribute 1	bttribute 1	cttribute 1	dttribute 1	ettribute 1	fttribute 1	Commitment	83	988	1071
A	attribute 1	bttribute 1	cttribute 1	dttribute 1	ettribute 1	fttribute 1	On hold	1223	456	1679
B	attribute 2	bttribute 2	cttribute 2	dttribute 2	ettribute 2	fttribute 2	Actual	12333	874	13207
B	attribute 2	bttribute 2	cttribute 2	dttribute 2	ettribute 2	fttribute 2	Budget	1344	94857	96201
B	attribute 2	bttribute 2	cttribute 2	dttribute 2	ettribute 2	fttribute 2	Commitment	2134	94874	97008
B	attribute 2	bttribute 2	cttribute 2	dttribute 2	ettribute 2	fttribute 2	On hold	4988	9484	14472
C	attribute 3	bttribute 3	cttribute 3	dttribute 3	ettribute 3	fttribute 3	Actual	39485	95	39580
C	attribute 3	bttribute 3	cttribute 3	dttribute 3	ettribute 3	fttribute 3	Budget	49338	4994	54332
C	attribute 3	bttribute 3	cttribute 3	dttribute 3	ettribute 3	fttribute 3	Commitment	39488	4993	44481
C	attribute 3	bttribute 3	cttribute 3	dttribute 3	ettribute 3	fttribute 3	On hold	48822	32937	81759
D	attribute 4	bttribute 4	cttribute 4	dttribute 4	ettribute 4	fttribute 4	Actual	822	9482	10304
D	attribute 4	bttribute 4	cttribute 4	dttribute 4	ettribute 4	fttribute 4	Budget	28842	399	29241
D	attribute 4	bttribute 4	cttribute 4	dttribute 4	ettribute 4	fttribute 4	Commitment	838230	8763	846993
D	attribute 4	bttribute 4	cttribute 4	dttribute 4	ettribute 4	fttribute 4	On hold	5995	87662	93657

Expected outcome as below:

ID

Attribute 1

Attribute 2

Attribute 3

Attribute 4

Attribute 5

Attribute 6

Actual Company

Budget Company

Commitment Company

On Hold Company

Actual Vendor

Budget Vendor

Commitment Vendor

On Hold Vendor

Actual System

Budget System

Commitment System

On Hold System

A

attribute 1

bttribute 1

cttribute 1

dttribute 1

ettribute 1

fttribute 1

100

23

83

1223

200

93

988

456

300

116

1071

1679

B

…

…

…

…

…

…

…

…

…

…

…

…

…

…

…

…

…

…

C

…

…

…

…

…

…

…

…

…

…

…

…

…

…

…

…

…

…

D

…

…

…

…

…

…

…

…

…

…

…

…

…

…

…

…

…

…

E

…

…

…

…

…

…

…

…

…

…

…

…

…

…

…

…

…

…

Appreciate support.

[DataNinja777]

Hi @JK07 ,

You can rearrange your data in your desired format using Power Query by following these steps:

1. Pivot the [Value type] column: This will create separate columns for each value type ("Actual," "Budget," "Commitment," and "On hold") with 1s and 0s indicating their presence.

= Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[#"Value type"]), "Value type", "ID", List.Count)

1. Add columns: Multiply the original value columns by the newly created columns of 1s and 0s for each value type to place the values in the appropriate columns. This process requires repetitive work, and there might be more efficient methods than manually adding a custom column for each combination of value type and value one by one.
2. Replace 0s with nulls: In the new columns containing values, replace 0s with nulls, and use the fill-up feature to propagate the values.
3. Filter for First Occurrence: In the value type field, filter for the first occurrence of 1 in the top row to remove duplicates.
4. The final output: Your data will be rearranged as shown below.

These steps can be performed by clicking the Power Query ribbons.

Best regards,

[JK07]

Hi @DataNinja777 

Thanks, I stuck on #3 and filtering. Can you please explain more how to do it and keep all populated values in each column?

Best,

[DataNinja777]

Hi @JK07 ,

1. Select the Column: Click on the column header that contains the values you want to fill up.

2. Go to the Transform tab: In the Power Query Editor, navigate to the Transform tab in the ribbon.

3. Use the Fill option:

• In the Transform tab, look for the Fill button. It is usually located in the Any Column group.
• Click on the drop-down arrow next to the Fill button.
• You will see two options: Fill Down and Fill Up.
• Select Fill Up.

4. Apply Changes: Once you select Fill Up, Power Query will fill the empty cells in the selected column by propagating the nearest non-empty value upwards. 

5. To retain only the non-duplicate rows, select the 'Budget' column and filter for the value '1'. This works because the 'Budget' column has a '1' in the top row, and we are using the Fill Up feature to propagate values. By filtering for '1' in the 'Budget' column, we ensure that only non-duplicate rows are kept.

Best regards,

[JK07]

@DataNinja777 hi,

Thanks, It seems it doesnt work for last ID (D). After filtering I get null in Actuals, same in On Hold, etc.

Also ID C should have 39585 in Actual Company while it gets 822 (so ID D value)

Best,

[DataNinja777 · Accepted solution]

Hi @JK07 ,

You are right.  Apologies for that, and instead of using the fill up, you can use Groupby function in Power Query to restructure the table like below:

The above was prepared by clicking the ribbon for each new column one at a time, but there might be a more efficient way to do this, even for the initial setup.

I have attached the PBIX file for your reference.

[JK07]

@DataNinja777 
Perfect!
Thank you very much!