PowerQuery: taking the average of each of many columns

Question

I'm new to PowerQuery and I have a table that is essentially a matrix of dates and hours within those days: the first column holds each date and the rest of the columns are labeled 1 through 24. An example is:

Date   H1   H2   H3   H4 ...
----   --   --   --   --
Jan 1
Jan 2
Jan 3
...

This is stored in an Excel file that is quite large, so I want to be able to simply query that file and pull subsets of the data. One example is the average hourly number by year. In SQL this would be represented by "SELECT YEAR(Date), AVG(H1), AVG(H2), ... FROM Source Table GROUPBY YEAR(Date)". However, in PowerQuery it seems like you can only use GROUPBY to generate a new column with the grouped result and thus have to repeat the operation x24 in this case, or more if I had data by seconds for example (to be fair, in the SQL query you also have to type out each column if you don't consider scripting solutions). Is there a simpler approach to generate my desired table (essentially collapsing each column to its average), or do I need to manually add each column?

Wedge Wedge · Accepted Answer · 2018-11-08T16:32:00

You can unpivot your hour columns and then you only need to group by year and the unpivoted attribute column.

I made a sample table of your data like this and loaded it into power query. I converted the Date column to Year only, Unpivoted Other Columns on the Date column, then Grouped by the Date and Hour column after unpivoting. The result looks like this.

You can of course repivot the data after if you want inside or outside of power query. This is what the code in power query looks like, but this was all created with normal menu options, not written by hand.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Extracted Year" = Table.TransformColumns(Source,{{"Date", Date.Year, Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Extracted Year", {"Date"}, "Hour", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Date", "Hour"}, {{"Average", each List.Average([Value]), type number}})
in
    #"Grouped Rows"

PowerQuery: taking the average of each of many columns

1 Answers