Averaging Every Column

[Craigc3814]

I am using Powerquery in Excel 2016 (Office 365)

I have a bunch of Excel Spreadsheets (daily spreadsheets of run time) saved in a network folder and I have PQ connected to that folder and combining all the spreadsheets into one large table.

The columns of data are the run date, item name, sub item 1 start, sub item 1 run time, sub item 2 start, sub item 2 run time........ all the way out to sub item 6.

I know I can group by item name, then add an aggregation for each column for average.

My question is, is this the most efficient way? I am about 2 years in on PQ and it is time for me to start learning to actually code in it. The UI is so easy to understand that I think I lean on it a bit too much.

 

[VBA Geek]

Hello Craig, the best way to do this aggregation is within DAX and not Power Query, unless the aggregation needs to be used by another Query

 

[Craigc3814]

Unfortunately the Company I am doing this for does not have Power BI, it would make life a lot more simple if they did.

 

[VBA Geek]

Power Pivot is also in Excel, starting from Excel 2010...

 

[Craigc3814]

I did not realize Power Pivot was available for Excel now. I had it in an old version then lost it when I first got office 365. Looks like Microsoft added it back?