Forum Discussion
Solved
Transformation conundrum!
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 in...
Please follow this sequence and let me know if work for you:
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.
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"