Power Query: Unstack data

[Mathexcel]

Hi Mr Excel Community,

I would like to unstack this data using Power Query in order to have a clean list to pivot.

Desired situation > To have a table with the following headers: Market, Items, Years, Month, Values.
Change vs prior Year will be filtered out of the data sample.

I know that I need to merge, transpose, unpivot, unmerge and pivot but does anyone have an idea how to split the Items colums into Years & Items?

Data Sample:

[TABLE="width: 425"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Market[/TD]
[TD]Items[/TD]
[TD]Column2[/TD]
[TD]Column3[/TD]
[/TR]
[TR]
[TD]England[/TD]
[TD]Occupancy (%)[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[/TR]
[TR]
[TD]England[/TD]
[TD]2016[/TD]
[TD]60[/TD]
[TD]70[/TD]
[/TR]
[TR]
[TD]England[/TD]
[TD]2017[/TD]
[TD]60[/TD]
[TD]70[/TD]
[/TR]
[TR]
[TD]England[/TD]
[TD]2018[/TD]
[TD]61[/TD]
[TD]71[/TD]
[/TR]
[TR]
[TD]England[/TD]
[TD]2019[/TD]
[TD]65[/TD]
[TD]73[/TD]
[/TR]
[TR]
[TD]England[/TD]
[TD]Change vs prior year (%)[/TD]
[TD]0.394330743[/TD]
[TD]0.063125438[/TD]
[/TR]
[TR]
[TD]England[/TD]
[TD]Average Daily Rate[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[/TR]
[TR]
[TD]England[/TD]
[TD]2016[/TD]
[TD]70[/TD]
[TD]90[/TD]
[/TR]
[TR]
[TD]England[/TD]
[TD]2017[/TD]
[TD]80[/TD]
[TD]86[/TD]
[/TR]
[TR]
[TD]England[/TD]
[TD]2018[/TD]
[TD]83[/TD]
[TD]86[/TD]
[/TR]
[TR]
[TD]England[/TD]
[TD]2019[/TD]
[TD]90[/TD]
[TD]87[/TD]
[/TR]
[TR]
[TD]England[/TD]
[TD]Change vs prior year (%)[/TD]
[TD]1.690275579[/TD]
[TD]-0.105899308[/TD]
[/TR]
[TR]
[TD]England[/TD]
[TD]RevPAR[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[/TR]
[TR]
[TD]England[/TD]
[TD]2016[/TD]
[TD]50[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]England[/TD]
[TD]2017[/TD]
[TD]56[/TD]
[TD]62[/TD]
[/TR]
[TR]
[TD]England[/TD]
[TD]2018[/TD]
[TD]56[/TD]
[TD]61[/TD]
[/TR]
[TR]
[TD]England[/TD]
[TD]2019[/TD]
[TD]55[/TD]
[TD]53[/TD]
[/TR]
[TR]
[TD]England[/TD]
[TD]Change vs prior year (%)[/TD]
[TD]2.091271599[/TD]
[TD]-0.04284072[/TD]
[/TR]
</tbody>[/TABLE]

Many thanks in advance for your precious time!

Best ,
Matt

 

[sandy666]

how to split the Items colums into Years & Items?

maybe

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Year", each [Items]),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Year", Int64.Type}}),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Changed Type", {{"Year", null}}),
    #"Added Conditional Column" = Table.AddColumn(#"Replaced Errors", "Custom", each if [Year] = null then [Items] else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Items"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Items"}})
in
    #"Renamed Columns"

 

[bkjohn2016]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([Items] = "Average Daily Rate" or [Items] = "Occupancy (%)" or [Items] = "RevPAR")),
    #"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 0, 1),
    #"Merged Queries" = Table.NestedJoin(Source, {"Items"}, #"Added Index", {"Items"}, "Filtered Rows", JoinKind.LeftOuter),
    #"Expanded Filtered Rows" = Table.ExpandTableColumn(#"Merged Queries", "Filtered Rows", {"Index"}, {"Index"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded Filtered Rows", each ([Items] <> "Change vs prior year (%)")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows1", "Custom", each if [Index] <> null then [Items] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Filled Down",{"Market", "Custom", "Items", "Column2", "Column3", "Index"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Reordered Columns", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"England", type text}, {"Occupancy (%)", type text}, {"Occupancy (%)_1", type any}, {"Jan", type any}, {"Feb", type any}, {"0", Int64.Type}}),
    #"Filtered Rows2" = Table.SelectRows(#"Changed Type", each ([#"Occupancy (%)_1"] <> "Average Daily Rate" and [#"Occupancy (%)_1"] <> "RevPAR")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows2",{{"Occupancy (%)", "Item"}, {"Occupancy (%)_1", "Year"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"0"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"England", "Item", "Year"}, "Attribute", "Value"),
    #"Renamed Columns1" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Month"}})
in
    #"Renamed Columns1"

 

[Mathexcel]

Many thanks for your reply! I have managed to get to the desired solution. ?

I am trying to consolidate about 30 documents into this one master database. Thanks to your help I have managed to clean my data, nevertheless, I am facing a new challenge. When expanding all my tables, it seems like the Item column isn't populated for some of the documents.

The Item column is organised in a sequence of 5 items for 3 KPIs. > I would like this sequence to repeate throughout the entire column.

I was thinking of using a modulo, but I don't know how to create a sequence so that it repeats.

Below is a picture of my data sample in PQ and the desired state:

Many thanks for your time.
Best,
MattExcel