How to convert a column which is in whole number into currency in Power BI DAX

[amansin19]

Actually I was working with Power BI on a dataset, and there it was a column where it shows funding column, and there I tried to convert text into whole number, which I was unable to do. 

Because it shows error : it can't automatically convert "text" into whole number. 

Although I did the same for other column here in "Valuation", but can't do it for "Funding".

Additionally, in column Valuation, in summarization dropdown it has "Sum" property mentioned which is not mentioned in "Funding". 

Screenshot

Please guide me through this issue.

[amansin19]

Thanks for your comment @tackytechtom @v-xiaotang . But, I tried to do the same replacing "type text" with "int64.type", but it showed error in all the rows.

[v-xiaotang · Accepted solution]

Hi @amansin19 

Thanks for your reply. I checked the funding column again, and then I found that there is "unknown" in it. When non numeric text is converted to numeric type, an error will be reported, so we need to clean the data first. It is recommended that you first replace the non numeric text in the funding column with other values.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[tackytechtom]

Hi @amansin19 ,

This is because your column most likely contains values that cannot be converted to "whole number", i.e. strings. What you could do is go to Power Query, click on the column and change the type of the column there to whole number:

The column will then change its type and the values that were not able to be converted display an error:

From there you have different options. You could click on remove errors (right click on the column):

Note: All rows containing that error will be removed:

Alternatively, you could replace the values with null by clicking on replace error. This will not delete rows!

But first off, you would probably want to find out what these errors are before you remove them. Your table might be really long and it might be hard to find them by scrolling through the column. However, if you right click on that red bar underneath your column name, you can find the keep errors options.

With that you filter the tables for all errors occured with the data type change. When clicking on a cell you get a description of the error, too:

Let me know if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[amansin19]

Actually, I appreciate your solution and your comment.

But, the problem is that when I apply converting text to whole number in Power Query Editor, all the numbers shows ERROR and further doing any procedure removing any error as you know will remove the entire table.

That is creating issue for me...

Below screenshot is for the same.

Additionally, I would like to say that your blog is nice, I started following it and it is nice. I would like to have more of your posts on Power BI 🙂🙂....

[tackytechtom]

Hi @amansin19 ,

First off, thanks for your kind words 🙂

This is really awkward... What does the error message exactly say and could you provide a screenshot of that column before you do the data type transformation? Just curious on how the values look like. Also, no problem if you'd want to share the file 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[amansin19]

Great 😊 then @tackytechtom , I have shared the Power BI File link. Please see if something works out, I am working on this issue for quite a few days but nothing has worked yet.

 Power BI File

[tackytechtom]

Hi @amansin19 ,

I am sorry for my late reply. I actually thought I had replied, but I cannot see the message...

So, I cannot see the data in Power Query since it references a local file but it seems like there was some replacing / substituting happening which transformed the column to a "special" currency column:

Obviously, PBI has problems to convert this to to a number and the only way it can deal with it is as a text data type. I think this is because of both the comma and the dollar sign. So I'd suggest to change the column type to Int64.Type in the third step (changed type) as @v-xiaotang suggested. But I pressume that the query will fail or still not do what you want because of the last steps Replaced Value1 and Replaced Value2. So, I would probably remove them by clicking on the x on the left.

Also, you should have the same issue with valuation. To fix that you can also change the type to "int64.Type" plus remove the first Replaced Value step.

Hope this helps!

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[v-xiaotang]

Hi  @amansin19 

If you go to Advanced Editor, then replace "type text" with "Int64.Type", will it work?

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.