Pivot to multiple columns and row number

[andy_scott42]

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. 

[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]

yes, have a look here:
https://community.powerbi.com/t5/Desktop/Custom-column-Index-or-Ranking-by-other-column/td-p/33864

[andy_scott42 · Accepted solution]

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.

[Stachu]

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_scott42]

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.