Pivot table and date groupings

Adnandos

New member
Joined
Jun 12, 2018
Messages
25
Reaction score
0
Points
0
Excel Version(s)
365
Hi all

I have a non-standard table that we've drafted. The purpose of the table is to track sale numbers by store, category, and date.

In an ordinary table, creating a pivot and grouping is easy. However, because I have my dates running across as columns in my source table, the Pivot table is acting up a bit, and is showing each date as a separate field to choose from.

Ultimately we're looking for a pivot table that will show as rows (store, then metric), and as columns, months, where the values presented in the table is the sum of the relevant metric.

How can we go about doing this?View attachment Daily Store Sales - Metrics - for excel guru - Copy.xlsx
 
Load your data to Power Query and using the attached MCode you can unpivot your data to put it in a normalized manner to allow you to Pivot the information.

Code:
// Load your range to PQ as Table1


let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([Store] <> null)),
    #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows","Total New",null,Replacer.ReplaceValue,{"Metric"}),
    #"Filtered Rows1" = Table.SelectRows(#"Replaced Value", each ([Metric] <> null)),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows1", {"Metric"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"
 

Attachments

  • Daily Store Sales - Metrics - for excel guru - Copy.xlsx
    55.3 KB · Views: 12
I don't mean to stand on other's toes - I'm getting to grips with Power Query etc. and wouldn't mind some feedback.
I experimented and ended up with
Code:
// Load your range to PQ as Table1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([Metric] <> null and [Metric] <> "Total New" and [Metric] <> "Total Renewals")),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows", {"Store", "Metric"}, "Attribute", "Value"),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Unpivoted Other Columns", {{"Attribute", type date}}, "en-GB"),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type with Locale",{{"Attribute", "Date"}})
in
    #"Renamed Columns"
which gives real dates and a Store column. I added a pivot on the Pivot sheet.
 

Attachments

  • ExcelGuru9986Daily Store Sales - Metrics.xlsx
    51 KB · Views: 15
No toe stepping here. I am at the early stages also. And as we already know, there are many ways to accomplish the same result in Excel. Glad to see you on board with PQ. I am always learning from the others. A really good resource is Ken Puls and Miguel Escobars book "M is for (Data) Monkey.
 
Back
Top