Forum Discussion
Pivot multiple value types and KPIs
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.
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.
6 Replies
Hi JK07 ,
You can rearrange your data in your desired format using Power Query by following these steps:
- 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)- 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.
- Replace 0s with nulls: In the new columns containing values, replace 0s with nulls, and use the fill-up feature to propagate the values.
- Filter for First Occurrence: In the value type field, filter for the first occurrence of 1 in the top row to remove duplicates.
- The final output: Your data will be rearranged as shown below.
These steps can be performed by clicking the Power Query ribbons.
Best regards,
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,
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,
