Forum Discussion
Pivot multiple value types and KPIs
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.
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,
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,
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.
DataNinja777
Perfect!
Thank you very much!