Forum Discussion
Pivot to multiple columns and row number
Thanks Stachu, that really helped.
I also found this page very helpful to work out the ID columns.
https://www.powerbi-pro.com/en/grouped-running-total-in-power-query/
I created a conditional index column of 1s and 0s then created a function to create a running total of that column.
what's the reason for having the data in that format? is it how you want to present it in the dashboard? it seems quite inconvenient to use with DAX later, what's the output you want to show based on that data? Do the numbers in Value columns not add up because it's a sample set, or is it part of some internal logic?
in order to populate the index you will need helper columns (basically the codes columns but filled down), but most likely you will face very similar challenges when you try to create dashboard based on the data formatted that way
have you considered using something without the blanks in the table, e.g. like this:
| EmployeeID | ID | Code | Level | Value |
| 1 | 1 | Tax Code 1 | L1 | 10 |
| 1 | 2 | Tax Code 2 | L1 | 20 |
| 1 | 3 | Tax Code 3 | L1 | 25 |
| 1 | 4 | Tax Code 4_SubCode1 | L2 | 2000 |
| 1 | 5 | Tax Code 4_SubCode2 | L2 | 1351 |
| 1 | 5 | Tax Code 4_SubCode2_SubCode1 | L3 | 1321 |
| 1 | 5 | Tax Code 4_SubCode2_SubCode2 | L3 | 84 |
Thanks for the reply Stachu
The data is being used for an import into another system and this is the specification required. It won't be used for reporting in Power BI although I will need to export the resulting dataset in that format.
I am trying to model it using Power Query as it is good at pivoting columns easily.
The numbers are just sample numbers but they don't need to add up.
The blank cells need to be there as this is the format required unfortunately.