Forum Discussion
Reading different data types in the same table from Excel
Hi there, I am a new PBI user and have, so far, been able to find answers to any query I had but have not yet cracked this one.
I have an Excel table which outputs different types of data on each row. Below is some mock data I have quickly produced to give an idea of the data set I start with;
| Category | Data |
| Total attendances | 87% |
| Number of cupcakes bought | 58 |
| Number of leaflets dropped | 97 |
| Number of new sign-ups | 11 |
I need to display this table in Power BI but do not need/want any of the contents to change, unfortunately, as Power BI is always thinking ahead I end up with;
| Category | Data |
| Total attendances | 0.87 |
| Number of cupcakes bought | 58 |
| Number of leaflets dropped | 97 |
| Number of new sign-ups | 11 |
which is not ideal.
I have looked through available topics but any possible solutions usually revolve around changing the data type of everything in the 'Data' column so was hoping there might be a fix/workaround in order for me to achieve my desired results.
Many thanks,
Léon
4 Replies
Hi v-chuncz-msft ,
Many thanks for your suggestion, I have tried to use your methodology to achieve the desired output but am met with an error;
"DAX comparison operations do not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values."
Any further assistance would be greatly appreciated.
Many thanks,
Léon
Hi leonmholt ,
The Error that you get says that your column is of the format text. This gives error when you use a comparator operator with it.
In other words, your data column is a text. Hence data > 1 gives you error as you cannot compare a text column to a number.
Check if your data column is of text data type. If so make it a Number.
That should fix this
Regards.
Thejeswar
Hi Thejeswar ,
Thanks for the assistance, I went into 'Query Editor' and, after a few 'trial and errors', changed the data type to "Fixed Decimal Number" then ran the data through v-chuncz-msft 's formula again and it worked!
Many thanks! :smileyhappy:
Léon
