Forum Discussion
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...
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
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.
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