Converting B/M to number & % to decimal

[m3z2t]

Hi Folks,

I've constructed a rather complex Power Query to pull stock data from Yahoo Finance for all stock tickers in a list I have provided. There are columns in this list that included values such as the following:

• 4.34b or 3.65m etc...
• .45% etc...

Right now these columns are recognized as text and simply changing to decimal or currency results in error.

What is the best methodology to deal with the B, M or % suffix (I guess I need 2 separate solutions for B/M then for %)?

Thanks!




m

 

[Bill Szysz]

Hi m3z2t,
I think construction like this below should helps

 TransfColumns = Table.TransformColumns(#"YOUR_PREVIOUS_STEP", {{"YOUR_COLUMN_NAME", each Text.Combine(Text.SplitAny(_, "MB%"))}})

Regards

 

[m3z2t]

Thanks Bill!

Your solution worked for getting rid of these values. However, I realized the problem was much more complex than that when it came down to M,B and k! Your solution works perfectly for the %s.

My solution:
Split Column by the single right most character (numbers on the left (756, 13.43, 12.2 etc), characters on the right (M,B,K etc)
Replace values of M (1000,000), B (1000,000,000) and k (1000)
Multiply numbers (756 etc) by the replaced values (1000,000 etc)