Unpivot or transpose data table to different layout

[Amstercam]

I have some project admin data in the format of the first table. This is how I receive the data and it will keep on being received like this. Pease note that the number of allocation categories can sometimes change from A,B,C, to A,B,C,D, for example and so the number of categories may change when updating the data.
I want to transform the layout of the data easily to the target format shown at the bottom. Have tried pivots & transpose options but I cannot figure it out to get exactly how I want it.
Any tips welcome!

 

[RoryA]

You could do that with Power Query. See Leila's video here:

 

[Amstercam]

Hi RoryA,
I actually saw that and tried but I still cannot figure it out. When I have multiple groups of columns to unpivot (in my example columns J3:013), using the unpivot function in Power Query ends up putting all of the data underneath each other in 1 column. I want to have the Categories in 1 column but the Initial investment and Acc Depreciation data next to each other in 2 columns like my target example.

 

[RoryA]

You'll have to filter that column for those two values afterwards, then pivot that column to get them back into separate columns.

 

[Amstercam]

You'll have to filter that column for those two values afterwards, then pivot that column to get them back into separate columns.

Hi which column do you mean by "that" column?

 

[RoryA]

using the unpivot function in Power Query ends up putting all of the data underneath each other in 1 column

the one you mentioned in your prior post. You should end up with an attribute column that has the column header(s) in, and a values column. You'd filter the attribute for the values you want, then pivot that to put the values back in separate columns.

 

[RoryA]

You can also do this with formulas - see for example here: How to Unpivot Data with New Excel Functions - but it can get a bit complicated.

 

[Amstercam]

the one you mentioned in your prior post. You should end up with an attribute column that has the column header(s) in, and a values column. You'd filter the attribute for the values you want, then pivot that to put the values back in separate columns.

I still cant figure this. Is it correct that in Power Query I first need to select columns J:0 and unpivot them?

the one you mentioned in your prior post. You should end up with an attribute column that has the column header(s) in, and a values column. You'd filter the attribute for the values you want, then pivot that to put the values back in separate columns.

Ugh I am new to Power Query. Still cant get it to work because if I make my dataset into a Table (as the video mentions), I cannot label the columns J:O as shown in line 3 of my example. It wont accept duplicate names so CAT A becomes CAT A2, CAT B becomes CAT B2 etc if I try to use those headings multiple times.

 

[Amstercam]

I still cant figure this. Is it correct that in Power Query I first need to select columns J:0 and unpivot them?

Ugh I am new to Power Query. Still cant get it to work because if I make my dataset into a Table (as the video mentions), I cannot label the columns J:O as shown in line 3 of my example. It wont accept duplicate names so CAT A becomes CAT A2, CAT B becomes CAT B2 etc if I try to use those headings multiple times.

Oh now I found a way to ALMOST make it work with Power Query, but when I get to the step after merging the Anchored columns, filling the blank values in that column then I need to hit "use first row as headers" it gives me an Expression Error (cannot convert some data to type Text). so stuck now

 

[Amstercam]

I finally figured out how to do it. The video posted earlier uses the same method but I couldnt get it to work because of some of the steps she does that cause me to get errors in the Power Query table. But I found this explanation much clearer and due to a slightly different series of steps involved this one worked great.