Trying to calculate average with a Pivot table and/or Power query.

How can I get an average for a row in a pivot table and/or power query? I have many columns of data. 6 of these have percentages. So I thought I would be smart and make a custom column in power query to get the average of each row of these specific 6 columns and then hide the original columns making one master average column.

I know I can make a custom column add columns by going:

=[Name]+[Name2]

But working out the average formula seems to baffle google. I know It would be easy to just throw this in a table and use the AVERAGE formula, but I want to use this data model for a specific purpose and that would negate that.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Hopefully the attached will clear things up.

First tab is how the data is send to us (annoyingly in PDF format first).

Then there are 4 groups of data which we just want to blend/average as one.
The second tab shows what I want a pivot table or in power query to do. I have no need for all the colour group columns, only the final averaged data.
Doing this in excel is easy, but in a pivot table or power query, baffled.

I think I get you, in that case, once I have done all that, how do I get the averages on the rows using those specific columns?

Maybe like this (PowerQuery)

grp.jpg

btw. table in post #4 is not averaged table

Thanks that looks great!
I forgot to ask before (table didn't load), is there an easy way of getting that colour column to transpose like that?
Or is it a case of manually having to make the first "group" column and assigning the values myself?

Sorry, I know. What I meant by colour is the first column in your table example, the group column.
You have moved that from a row to a column. Was that an automated process or did you have to manually do that?
I ask because the end game of this is to dump data in a sheet (with minor tweaks) and then magic happens. I don't want to have to jiggle all that data around for these weekly reports.

Refresh thread and see attached excel file

Thanks.
Well that is, frustrating.

Is there any way to rectify this? Ideally I want the grouping to average to be do as the pivot table does.
Or is this a case of, it's the way it is and if I want it how I want it I would have to use a pivot table?
I only fear on using a pivot table because I will be referring to cells in a pivot and I worry updating the table would break the link or link to the wrong cell.

it depends on what you need. How should I know?
maybe use DataModel and PowerPivot and calculate the average there

best to attach the Excel file with before / after, means to show what you want to achieve. prepare it manually if necessary


In attached Excel file try not to use:

• merged cells
• password protection
• unnecessary formatting like: colours, borders, aligning another than default, etc...
• unnecessary zooming/grouping/freezeing

You have words to logically describe the problem

To attach an Excel file to your post,

• desensitize data
• remeber that your example should reflect structure and type of data
• click Go Advanced,
• scroll down until you see Manage Attachments,
• click that and select Browse,
• select your file and click Open,
• click Upload and you will see your attachment below Upload Files from a website
• click Close this window,
• click Submit reply

After that you should see attachment in your post

What I need is for the query editor to stop averaging null values as a zero, which it seems to do after I have multiple groups.
Looks like I will have to go the easy route and just export the table and use a pivot table on it. The data I have right now is too complex and time consuming to remove sensitive information and produce a dummy sample.

you can do that easiest way,

• insert pivot table
• use an external data source
• choose connection
• select that query what you want
• OK