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

0
votes

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
1
powerbidax
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. – mkRabbani
change the datatype from the advanced editor....it should work!! – user11738502
@sanjeev_gautam yes i tried from there it was not working. – coder_bg
@R_R Yes i have thoroughly checked, there are no such values. The data is exactly as i have mentioned above. :/ – coder_bg

1 Answers

1
votes

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