Same/Duplicate Column Names in Excel Pivot Tables

Question

I have a data table in Excel as follows:

| Country |   City   | Jan 1, 2020 | Jan 2, 2020 | Jan 3, 2020 | .... | Jan 8, 2020 | Jan 9, 2020 | .... |
----------------------------------------------------------------------------------------------------------
|   USA   | New York |    88647    |    83247    |    92621    | ...  |     32874   |    31940    | .... |
|   USA   |  Boston  |    31647    |    73242    |    42620    | ...  |     22870   |    11944    | .... |
|   USA   |   Miami  |    58641    |    23241    |    92627    | ...  |     22872   |    61943    | .... |
| England |  London  |    88621    |    23324    |    32620    | ...  |     12874   |    21940    | .... |
| England |  Bristol |    73612    |    13320    |    62626    | ...  |     32876   |    81903    | .... |

The columns go on till December 31, 2020... so it's entire year's data.

I want to see the country wise numbers for all Mondays, Tuesdays, ... Sundays in the whole year summed up together. For this, I extracted the WEEKDAY from the date columns, but that would give me column names with repeated names as the day name gets repeated for every 7 days. Below is what the table looks like once I extract the weekday for the given date.

| Country |   City   | Wednesday | Thursday | Friday | .... | Wednesday | Thursday | .... |
--------------------------------------------------------------------------------------------
|   USA   | New York |   88647   |   83247  |  92621 | ...  |   32874   |  31940   | .... |
|   USA   |  Boston  |   31647   |   73242  |  42620 | ...  |   22870   |  11944   | .... |
|   USA   |   Miami  |   58641   |   23241  |  92627 | ...  |   22872   |  61943   | .... |
| England |  London  |   88621   |   23324  |  32620 | ...  |   12874   |  21940   | .... |
| England |  Bristol |   73612   |   13320  |  62626 | ...  |   32876   |  81903   | .... |

Pivoting this is a dead end because of the repeated column names. Second instance of Wednesday column would be read as Wednesday2 in the pivot, third instance as Wednesday3 and so on. Is there any way to make the pivot table consider all the instances of Wednesday as one single Wednesday and so on?

Below is what my final output should look like:

| Country | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday |
-------------------------------------------------------------------------
|   USA   |        |         |           |          |        |          |
| England |        |         |           |          |        |          |

Like I mentioned in my post, I want to aggregate the data by day of the week. For example, I would like to see the trend of revenue (numbers in the table indicate revenue) for USA grouped by all the Mondays in the year together, all the Tuesdays in the year together, so on. This helps me identify on what day of the week is the revenue highest and lowest in the whole year. Aggregation here happens both day of the week wise and also country wise. — f722axzo5d
I guess I wasn't clear. If you have ten Wednesday entries for USA, how do you want them represented? Count? Average? Max? Min? comma separated string? etc. — Ron Rosenfeld

Ron Rosenfeld Ron Rosenfeld · Accepted Answer · 2020-12-25T19:46:52

Depending on how you want to aggregate the results, you can do this fairly easily by doing the pivot in Power Query, available in Excel 2010+ and O365

algorithm

open the PQ editor by
- select a cell in the table
- Data / Get & Transform Data / From Table/Range
Remove the City column since we won't be using it
Select the Country column and "unpivot other colums"
- This transforms your data into a three column table of Country, Attribute, Value
Create a custom column with the Weekday number for sorting
Transform the Attribute column (the dates) to just show the day of the week
Sort by the weekday number column, then delete it.
Pivot the Attribute column, and aggregate the results however you wish
- In the example, I used the Average function for aggregation, and presented it as a whole number, but other options from the UI include, Sum, Max, Min, Median,Count, and you can even program your own aggregation function

M Code

If you copy/paste this into the Advanced Editor, be sure to change the Table name in Line 2 to whatever your data table is actually named

Examine the Applied Steps window to see what happens at each step of the Query

note: code has been edited since originally posted to clean it up and make it more flexible. Ensure you are working with latest version

let
    Source = Excel.CurrentWorkbook(){[Name="Table11"]}[Content],

    //Remove unneeded city column
    #"Removed Columns" = Table.RemoveColumns(Source,{"City"}),

    //Unpivot to generate a three column table
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Country"}, "Attribute", "Value"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Country", type text}, {"Attribute", type date}, {"Value", Int64.Type}}),

    //generate weekday number for sorting
    #"Added Custom" = Table.AddColumn(#"Changed Type", "weekdayNum", each Date.DayOfWeek([Attribute])),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"weekdayNum", Order.Ascending}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"weekdayNum"}),

    //change date to day name
    #"Extracted Day Name" = Table.TransformColumns(#"Removed Columns1", {{"Attribute", each Date.DayOfWeekName(_), type text}}),   
    
    //Pivot on weekday name
    #"Pivoted Column" = Table.Pivot(#"Extracted Day Name", List.Distinct(#"Extracted Day Name"[Attribute]), "Attribute", "Value", List.Average),

    //added code since this example doesn't have all the days of the week
        colNames = List.RemoveFirstN(Table.ColumnNames(#"Pivoted Column"),1),
        colTypes = List.Repeat({Int64.Type},List.Count(colNames)),

    //Round to Integer
    #"Changed Type1" = Table.TransformColumnTypes(#"Pivoted Column",
        List.Zip({colNames,colTypes})
        )
in
    #"Changed Type1"

Same/Duplicate Column Names in Excel Pivot Tables

1 Answers