Power Query: Unstack data

Mathexcel

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

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
3,762
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"
 

bkjohn2016

New Member
Joined
Sep 9, 2016
Messages
38
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"
 

Mathexcel

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

Forum statistics

Threads
1,081,719
Messages
5,360,831
Members
400,600
Latest member
Stuckagainandagain

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top