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 |        |         |           |          |        |          |

Answer 1

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"