Converting the Data Type

[kushal258]

Suppose I have a column in this way and want to convert it to whole number data type to perform certain calculations, then how can it be done?
I did try creating a new column in Power Query by extracting text between delimiter.
I gave start delimiter as $ and end delimiter as B.
However certain values are in millions (M).
Can anyone please help me ?

Thank You

[AlienSx]

let
    Source = funding_table,
    new_funding = 
        Table.AddColumn(
            Source, "new funding",
            (x) => 
                try Number.From(Text.BetweenDelimiters(x[Funding], "$", "B")) 
                otherwise Number.From(Text.BetweenDelimiters(x[Funding], "$", "M")) / 1000
        )
in
    new_funding

[kushal258]

Thanks AlienSx

[kushal258]

From Funding to Funding_New column

[serpiva64 · Accepted solution]

Hi, 

try this 

if [Column1.2] ="M" then Value.Multiply([Column1.1.2],0.001) else [Column1.1.2]

[kushal258]

Thank You Serpivs64. It has worked

[serpiva64]

Hi,

you can split your column by numbers of character two time; the first from the end

and the sceon from the start to get the currency and the create a conditional column based on N or B to transform your number

If this post is useful to help you to solve your issue consider giving the post a thumbs up 

 and accepting it as a solution !

[kushal258]

Thank you for your feedback,

I did try that earlier but there is still one problem. There are certain values in Millions ($USD) and other values in Billions ($USD). So I would have to add "0." to values which are in Millions ($USD). 

[serpiva64]

Please make an example of what you want to achieve