AVERAGE of LEFT(column,1) ignoring blanks / IsNaN

[BenGWeeks]

Hello,

I have a Microsoft Forms survey as per below:

This is being stored into a SharePoint list that I amn retrieving via OData. The data looks like as follows:

I would like to create a average of these scores (ignoring the "I don't know" and "N/A" answers that are not numbers). I was thinking along the lines of create a new column with the following formula:

However, the columns that do not start with a number are presumably breaking it.

Any help much appreciated.

Regards,

Ben

[PhilipTreacy · Accepted solution]

Hi @BenGWeeks 

Please download this sample PBIX file containing the following code and visual.

You can tidy this data up in Power Query to give you just numbers, so starting this with this sample data

You end up with this

This is the code that performs these transformations

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlHQVQguKcrPS8+pVEhML0pNVdJRMgYJ5uemlmcklsAFjZAFUzKLYeJYTIjVwWEwRYIgU7G6jHhBbKbGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Column2", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Column2.1", "Column2.2"}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Split Column by Delimiter1", "Column3", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Column3.1", "Column3.2"}),
    #"Split Column by Delimiter3" = Table.SplitColumn(#"Split Column by Delimiter2", "Column4", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Column4.1", "Column4.2", "Column4.3", "Column4.4"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Split Column by Delimiter3",{"Column1.1", "Column2.1", "Column3.1", "Column4.1"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Column1.1", Int64.Type}, {"Column2.1", Int64.Type}, {"Column3.1", Int64.Type}, {"Column4.1", Int64.Type}})
in
    #"Changed Type"

You can now create a Table Visual with these columns and choose Average as the aggregation

[v-yangliu-msft · Accepted solution]

Hi  @BenGWeeks,

Here are the steps you can follow：

1. Create calculated column.

abc_column =
IF(ISERROR(LEFT('Table'[abc],1)+1),BLANK(),LEFT('Table'[abc],1))

asd_column =
IF(ISERROR(LEFT('Table'[asd],1)+1),BLANK(),LEFT('Table'[asd],1))

dfc_column = IF(ISERROR(LEFT('Table'[dfc],1)+1),BLANK(),LEFT('Table'[dfc],1))

2. Result：

3. Place the three calculated columns in the visual table and select AVG

4. Result：

You can downloaded PBIX file from here.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[BenGWeeks]

This was perfect thanks Liu 🙂

[PhilipTreacy · Accepted solution]

Hi @BenGWeeks 

Please download this sample PBIX file containing the following code and visual.

You can tidy this data up in Power Query to give you just numbers, so starting this with this sample data

You end up with this

This is the code that performs these transformations

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlHQVQguKcrPS8+pVEhML0pNVdJRMgYJ5uemlmcklsAFjZAFUzKLYeJYTIjVwWEwRYIgU7G6jHhBbKbGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Column2", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Column2.1", "Column2.2"}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Split Column by Delimiter1", "Column3", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Column3.1", "Column3.2"}),
    #"Split Column by Delimiter3" = Table.SplitColumn(#"Split Column by Delimiter2", "Column4", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Column4.1", "Column4.2", "Column4.3", "Column4.4"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Split Column by Delimiter3",{"Column1.1", "Column2.1", "Column3.1", "Column4.1"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Column1.1", Int64.Type}, {"Column2.1", Int64.Type}, {"Column3.1", Int64.Type}, {"Column4.1", Int64.Type}})
in
    #"Changed Type"

You can now create a Table Visual with these columns and choose Average as the aggregation

[iXpert_info]

Hey @BenGWeeks 

One Interesting solution is you can replace NA and I don't Know as "0-N/A" and "0-I don't Know"

That will consider both of your those answers as 0 and it wont disturb your above measure.

Did I answer your question? Please mark this as solution

Best Regards
Jay Patel
iXpert Analytics