Forum Discussion
Converting the Data Type
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
Hi,
try this
if [Column1.2] ="M" then Value.Multiply([Column1.1.2],0.001) else [Column1.1.2]
8 Replies
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 !
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).
Please make an example of what you want to achieve
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_fundingThanks AlienSx
From Funding to Funding_New column
