Reading different data types in the same table from Excel

[leonmholt]

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

[Thejeswar]

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

[leonmholt]

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!

Léon

[v-chuncz-msft · Accepted solution]

@leonmholt 

Excel handles a different data type for each cell, whereas DAX handles a single data type for each column. You may try using FORMAT.

Column =
FORMAT ( Table1[Data], IF ( Table1[Data] > 1, "General Number", "Percent" ) )

[leonmholt]

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