Forum Discussion

mjbernier's avatar
mjbernier
Helper II
7 years ago
Solved

Data format is changing between Power Query and data model

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. Her...
Need Help
  • MFelix's avatar
    MFelix
    7 years ago

    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

MFelix's avatar
MFelix
Super User
7 years ago

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's avatar
mjbernier
Helper II
7 years ago
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's avatar
    MFelix
    Super User
    7 years ago

    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