Forum Discussion
Helper IIPivot to multiple columns and row number
Hi
I'm trying to pivot a table into a specified format but I'm having trouble with a couple of things.
My raw table looks the below:
And I need to get it into the following format:
I've managed to pivot the columns into rows and then use custom columns using some IF statements to get the columns into the required order as below but I still need help:
The IDs need to be incremental indexes but I can't get the grouping to work. For the first ID, the number needs to remain the same as the other ID column increments. I've tried grouping, then adding a custom index column but I can't get the grouping correct.
The Second column of sub code_subcodes needs to start on the same line as the middle SubCode but I can currently only get it to appear on another line.
Any help would be greatly appreciated.
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.
6 Replies
- Stachu
Community Champion
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 
andy_scott42Thanks 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.
- Stachu
so we're talking Excel + Power Query + VBA, correct? or do you plan to export from Power BI?
as for the index - the logic is not very consistent, so I think hardcoding the ifs is the option to consider.
Other one that comes to mind is a helper table with the codes, e.g. like this:
1 Tax Code 1 2 Tax Code 2 3 Tax Code 3 4 Tax Code 4_SubCode1 5 Tax Code 4_SubCode2 5 Tax Code 4_SubCode2_SubCode1 5 Tax Code 4_SubCode2_SubCode2 5 Tax Code 4_SubCode2_SubCode3 Other option is to do something like this (as long as the indexes are single digit)
if Number.FromText(Text.Middle([Code2], 9, 1)) >= 4
then Number.FromText(Text.Middle([Code2], 9, 1)) + Number.FromText(Text.Middle([Code2], 18, 1)) - 1
else Text.Middle([Code1], 9, 1)where [Code1] is the name of column with tax codes 1-3, and [Code2] is the column with tax code 4 and sub codes
it will return Errors for null rows, so you can then replace Errors with nulls and use fill down
- andy_scott42
Thanks Stachu
Yes it is mainly Excel/Power Query. Unfortunately it has been decided VBA is not allowed.
The numbers need to be dynamic as different employees can have a different number of tax codes.
I've created a helper table to get the codes into the correct columns.
Is it possible to group rows similar to the ROW_NUMBER() PARTITION BY in SQL?
- Stachu
