Data format is changing between Power Query and data model

[mjbernier]

I have a column of data that contains responses to a questionnaire. For some of the questions, the responses are text values (Y, N, N/A), and for others the responses are a numeric scale of 0-10. Here is an example:

Question 1   Y

Question 2   6

Question 3   8

Question 4   N

Question 5   10

Question 6   N/A

I would like to calculate the average value of the numerical scores while ignoring the text responses; in this example, the average would be 8. In Excel, if the column of values is defined at General format I can use the AVERAGE function and it works perfectly. When I try this in Power BI, it fails.

I went into Power Query to ensure the data type for the column is General format; i.e. the type symbol on the left side of the column header is the stacked "ABC 123", the text values are displayed as left-justified, and the numeric values are right-justified. But, when I use "Close & Apply" to close out the editor and then look at the data tables in the model, the column has been changed to Text only; i.e. all values are left-justified. When I create a measure over this column using the AVERAGE function it returns the error "The function AVERAGE cannot work with values of type String".  When I try using AVERAGEA (which handles text values) there isn't an error but the result is always zero, which confirms to me that all of the values have been converted to text, even though they were in General (mixed text and numeric) format in Power Query.

The only solution I have found is to create a calculated column that checks the contents of the original column and writes out the numeric values. It's a messy solution, but it works. Why is the data type changing? And is there a better solution?

[MFelix]

Hi @mjbernier ,

The ABC 123 on the top of the column symbols that you have text and numbers on a column, by default is what is uploaded in some columns. When you go to the aplly this it's read as text and not as numbers.

Go to the query editor click on the ABC123 and select the number format for the column with the numerics, then close and apply and your data will be as number.

Regards,

MFelix

[mjbernier]

This does not work. Changing the column format from General to Numeric when there is a mixture of text and numbers fails with an error message saying it cannot convert a string value (such as "Y") to a number. I want to retain the General format in the Power BI data model just as I can in Excel.

[MFelix · Accepted solution]

Hi @mjbernier ,

Sorry for the confusion, I read it incorrectly your question.

Create the following measure:

Measure =
AVERAGEX (
    FILTER (
        ADDCOLUMNS (
            GROUPBY ( ALLSELECTED ( Table1 ); Table1[Question]; Table1[Answer] );
            "Number_Answer"; IFERROR ( Table1[Answer] + 0; 0 )
        );
        [Number_Answer] <> 0
    );
    [Number_Answer]
)

Should give expected result.

Once again sorry for the incorrect information previouly.

Regards,

MFelix