Power Query: Unstack data

Mathexcel

New Member
Joined
Jun 22, 2017
Messages
36
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:


MarketItemsColumn2Column3
EnglandOccupancy (%)JanFeb
England20166070
England20176070
England20186171
England20196573
EnglandChange vs prior year (%)0.3943307430.063125438
EnglandAverage Daily RateJanFeb
England20167090
England20178086
England20188386
England20199087
EnglandChange vs prior year (%)1.690275579-0.105899308
EnglandRevPARJanFeb
England20165060
England20175662
England20185661
England20195553
EnglandChange vs prior year (%)2.091271599-0.04284072

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

Many thanks in advance for your precious time!

Best ,
Matt
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
how to split the Items colums into Years & Items?
maybe
Code:
// 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"
 
Upvote 0
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")),
    #"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"
 
Upvote 0
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:
1579081251887.png



Many thanks for your time.
Best,
MattExcel
 
Upvote 0

Forum statistics

Threads
1,213,533
Messages
6,114,179
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top