CODEWITHSUNDEEP
powerbidax
coder_bg
coder_bg

Reputation: 370

How do I convert a number from data type TEXT to whole number to further calculate it using DAX?

I am taking data from the excel where there is a column "Global" with values as whole numbers and decimal numbers. When I am importing it to Power BI, the column data type is coming as "TEXT" and hence I am unable to use it to calculate my new column which is "Local + Global / Total" since it cannot convert calculate on string which makes complete sense. I looked it up and tried the following :

  1. To change the column type to Whole Number / Decimal Number and it showed me an error that It cannot be converted.
  2. I made a measure using the functions CONVERT and VALUE but in vain. It could not be converted saying a single value was expected but multiple values were provided in the latter.

How do I solve this?

EDIT The column looks like this

Global
12345.67
43566
123.765

Upvotes: 0

Views: 6199

Answers (1)

mkRabbani
mkRabbani

Reputation: 16918

If it is showing error that It cannot be converted, obviously there are some garbage value in the column like - space, string or other values not a number. Please check your data first, hope you will get the issue as well.

In Power Query Editor You can select the column and change data type to Whole Number. In that case, some errors will be shown where the conversion failed to convert some value as shown below-

enter image description here

Here I have provided a string in the last row. Now you can check your data by filtering the column with error to check what are the actual values in the source. You can than perform some transformation to get the correct value value out of them.

You can also remove all records with error as shown below if you find those rows are with garbage value is not important for your.

enter image description here

Upvotes: 1

Related Questions