# Power Query: Unstack data

#### Mathexcel

##### New Member
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:

 Market Items Column2 Column3 England Occupancy (%) Jan Feb England 2016 60 70 England 2017 60 70 England 2018 61 71 England 2019 65 73 England Change vs prior year (%) 0.394330743 0.063125438 England Average Daily Rate Jan Feb England 2016 70 90 England 2017 80 86 England 2018 83 86 England 2019 90 87 England Change vs prior year (%) 1.690275579 -0.105899308 England RevPAR Jan Feb England 2016 50 60 England 2017 56 62 England 2018 56 61 England 2019 55 53 England Change vs prior year (%) 2.091271599 -0.04284072

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

Best ,
Matt

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### sandy666

##### Well-known Member
how to split the Items colums into Years & Items?
maybe
Code:
``````// Table1
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"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

##### New Member
Rich (BB code):
``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Filtered Rows" = Table.SelectRows(Source, each ([Items] = "Average Daily Rate" or [Items] = "Occupancy (%)" or [Items] = "RevPAR")),
#"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),
#"Reordered Columns" = Table.ReorderColumns(#"Filled Down",{"Market", "Custom", "Items", "Column2", "Column3", "Index"}),
#"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

##### New Member
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:

Best,
MattExcel