Transformation conundrum!

[LouStagner]

I have a Power Query transformation I am trying to do that I am stuck on.  I have 2 pictures below showing what the data currently looks like and an after picture that shows what I need to turn it into.

I need to take the values in rows 1-4 and turn them into columns that repeat down all rows.  

Row 5 will eventually be my column headers.

Any thoughts on this one?

BEFOREAFTER

[MarkS]

I think that you should Transpose the Table,

then select the first 5 columns and choose the Unpivot Other Columns function,

then select Column 5 and Select Pivot Column (the values will be the Values column and choose Advanced Options "Don't Aggregate" from the drop down for the aggregate values function).

. This will get you to this

Then clean up the column names and data types.

[Vvelarde · Accepted solution]

@LouStagner

Please follow this sequence and let me know if work for you:

https://youtu.be/8e9C1IqG5TU

[LouStagner]

This worked perfectly victor! Thank you.

Thanks to everyone else for their input.

The conundrum however continues as there is a folder full of files in this format that I need to mash up together. 

[Anonymous]

@LouStagner,

The no-code steps from @Vvelarde have a smarter merge than mine.  Here's my query again (inc. comments) to include his approach which uses only query/table:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
//Split out the first four rows as the FIRST table
    #"Kept First Rows" = Table.FirstN(Source,4),
//Just keep the first (identical) column
    #"Removed Other Columns" = Table.SelectColumns(#"Kept First Rows",{"Column1"}),
//Turn the column into a row
    #"Transposed Table" = Table.Transpose(#"Removed Other Columns"),
//Add the key column for later join
    #"Added Custom" = Table.AddColumn(#"Transposed Table", "Column5", each 1),
//Rename columns
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Column1", "Period"}, {"Column2", "Year"}, {"Column3", "Division #"}, {"Column4", "Division Name"}}),
// Now create the SECOND table by removing the first four rows from Source
    #"Removed Top Rows" = Table.Skip(Source,4),
//Promote the column headers
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows"),
//Add the key column
    #"Added Custom1" = Table.AddColumn(#"Promoted Headers", "Column5", each 1),
//Join the two tables on the newly added key column
    #"Merged Queries" = Table.NestedJoin(#"Added Custom1",{"Column5"},#"Renamed Columns",{"Column5"},"NewColumn",JoinKind.FullOuter),
//Expand out the contents of the joined columns
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Period", "Year", "Division #", "Division Name"}, {"Period", "Year", "Division #", "Division Name"}),
//Tidy by removing the join column
    #"Removed Columns" = Table.RemoveColumns(#"Expanded NewColumn",{"Column5"})
in
    #"Removed Columns"

[GilbertQ]

I would suggest to Transpose your Columns first.

Then once that is done you possibly then can Unpivot some other columns and then that should get it into the format you need.

I have personally done it in the past where I have Transposed, Unpivoted and Transposed again.

[LouStagner]

Thanks for your feedback,

I am still having no luch trying to make this work through a combination of trasnforms and pivot/unpivot.

There has got to be a way, but whatever that way is, I can't figure it out!

[Anonymous]

@LouStagner,

If the Tranpose/Unpivot/Transpose approach from @GilbertQ doesn't pan out, you can try a mashup like below - split out the first four rows and transpose, then join back to the rest of the table and sort and fill down and filter out the nulls.  But this will be sensitive to data changes...

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Kept First Rows" = Table.FirstN(Source,4),
    #"Removed Other Columns" = Table.SelectColumns(#"Kept First Rows",{"Column1"}),
    #"Transposed Table" = Table.Transpose(#"Removed Other Columns"),
    #"Added Custom" = Table.AddColumn(#"Transposed Table", "Column5", each ""),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom",{{"Column1", "Period"}, {"Column2", "Year"}, {"Column3", "Division #"}, {"Column4", "Division Name"}}),
    #"Removed Top Rows" = Table.Skip(Source,4),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows"),
    #"Merged Queries" = Table.NestedJoin(#"Promoted Headers",{"AccountNumber", "Period Budget", "Period Actual", "YTD Actual", "YTD Budget"},#"Renamed Columns",{"Period", "Year", "Division #", "Division Name", "Column5"},"NewColumn",JoinKind.FullOuter),
    #"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Period", "Year", "Division #", "Division Name", "Column5"}, {"Period", "Year", "Division #", "Division Name", "Column5"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded NewColumn",{"Column5"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Period", Order.Descending}}),
    #"Filled Down" = Table.FillDown(#"Sorted Rows",{"Period", "Year", "Division #", "Division Name"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each [AccountNumber] <> null)
    
in
    #"Filtered Rows"