How to Convert column having Text values as Million and Billion to number in Power BI

[Madhan_]

Hi

I have text values in one column (Funding col), like $2.5M and $4.5B. I want to convert this column into a numerical one like 2,500,000 and 4,500,000,000 for the whole column. so I can calculate further

 

Please provide solution

[Madhan_]

Hello Pal, I'm sorry still its not working.

[mangaus1111 · Accepted solution]

you can use the following macro

[Madhan_]

Yes, I have tried that. Now I am able to calculate. Whereas I am having doubt that will it works fine if more data will be added in the same format like 10M or 2B to the dataset in future.

[mangaus1111]

Hi @Madhan_ ,

try this code in Power Query

#"Replaced Value" = Table.ReplaceValue(Funding_Table,"M","000000",Replacer.ReplaceText,{"Funding"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","B","000000000",Replacer.ReplaceText,{"Funding"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value1",{{"Funding", type number}})