Results 1 to 4 of 4

Thread: Pivot table and date groupings

  1. #1
    Acolyte Adnandos's Avatar
    Join Date
    Jun 2018
    Posts
    25
    Articles
    0
    Excel Version
    365

    Pivot table and date groupings



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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?Daily Store Sales - Metrics - for excel guru - Copy.xlsx

  2. #2
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    193
    Articles
    0
    Excel Version
    2019
    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"

  3. #3
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,604
    Articles
    0
    Excel Version
    365
    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.
    Attached Files Attached Files

  4. #4
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    193
    Articles
    0
    Excel Version
    2019
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •