Unpivoting table with categories on multiple headers

Hi @sam.mckay,
I have a table I need to unpivot with categories on multiple headers.
For each row (for testing I’m using just 12) there are 3 categories in the first 3 columns I need to have in columns, as in the picture.
I tried transposing, filling down, then unpivoting, but it worked just with 1 category.
How can I do it?

Thanks for help

Roberto

example.xlsx (18.0 KB)
multiple headers.pbix (17.8 KB)

Hi @Roberto,

See if this is helpful, all solutions leverage mostly the UI but I’ve split it up into two fases
First get the items, this is followed by Table.Repeat( RenameColumn, 3 ) for each category

image
.
And for the Values its a basic Transpose and Unpivot

Next included 3 ways of joining these tables back together:
First method. Adding a merge key followed by a conventional merge
Second. Using List.Zip to bring all columns together
Third. Performing a Record merge

with this result.

Here’s your sample file.
eDNA - multiple headers.pbix (32.4 KB)

I hope this is helpful

1 Like

Hi @Roberto, did the response provided by @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

I hope that you are having a great experience using the Support Forum so far. Kindly take time to answer the Enterprise DNA Forum User Experience Survey, we hope you’ll give your insights on how we can further improve the Support forum. Thanks!

1 Like

Hi @Melissa three solutions! Thanks a lot

Roberto

Hi @Melissa,
I’ve been trying to apply your solutions step by step to my problem, but I could not make it.
Solution #2 seems the one that fits better (merging index in #1 doesn’t seem to work with real data)
I’m doing something wrong using #2, but I cannot find my error.

For istance the Insight Portal asset for ServiceNow-Incident should show

image

While in the transformed data I got this

Can you please help?

Thanks so much

Roberto
Allocation.pbix (226.3 KB)
source.xlsx (31.9 KB)

Hi @Roberto,

Give this a go.
Allocation.pbix (229.2 KB)

I hope this is helpful

1 Like

Hi @Melissa,
works nicely now.

Thanks so much

Roberto