I cannot select "Average" for some data columns

[tedjant33]

Hello,

I have my report linked to an excel with a list of student names followed by columns for scores on 5 different assignments administered 6 times throughout the year (30 columns of data). I want to be able to show the average across students for each individual column. The issue I am running into is that only the first data column of each assignment allows me to select Average. The following 5 data columns only give me options for "first", "last", "count (distinct)", and "count." I have noticed that the columns that allow me to use the average function have the sum all symbol next to them. Am I able to fix it and average each column?

I have included a picture of my options when working with the "Mechanics 1" data column which gives me more options, as well as the "Mechanics 2" data column which does not give me the option to average. 

Thanks in advance!

[Pragati11]

Hi @tedjant33 ,

The screesnhot that you have shared clearly shows that Mechanics 1 is a numeric type of column therefore you see summarisation as SUM, AVERAGE, etc.

On the other hand, Mechanics 2 is a text type of column therefore you see summarisation as FIRST, LAST, etc.

[tedjant33]

Thanks for the quick response! is there an easy way to change the type of column?

[Pragati11]

Hi @tedjant33 ,

Yes click on your column --> on the top ribbon --> modify datatype from Text to say whole number.

[tedjant33]

Unfortunately, the only option appearing under format is text. I will mention that all of the columns are identical in data, the only difference being the name of the column.

Any thoughts?

[Pragati11]

Hi @tedjant33 ,

Did you click on the small drop-down icon as shown below to check other datatype options?

Once you click on the drop-down, it will show you other data-types.

[tedjant33]

Yes, and Text is the only option that appears.

[Pragati11 · Accepted solution]

Hi @tedjant33 ,

In that case go to Power Query Editor and try to modify the datatype of this column.

What datasource are you connecting to?

[tedjant33]

I went into Power Query Editor and was able to change the data types there. Thanks so much for your help!

[tedjant33]

For reference, here is a screenshot of some of the excel.

[amitchandak]

@tedjant33 , You can do that only for numeric columns - Whole number and decimal