Converting B/M to number & % to decimal

m3z2t

New member
Joined
Oct 12, 2016
Messages
8
Reaction score
0
Points
0
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
 
Hi m3z2t,
I think construction like this below should helps
Code:
 TransfColumns = Table.TransformColumns(#"YOUR_PREVIOUS_STEP", {{"YOUR_COLUMN_NAME", each Text.Combine(Text.SplitAny(_, "MB%"))}})

Regards
 
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)
 
Back
Top