cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Squirrel15
Frequent Visitor

Power Query change column with numbers and text into decimal

Hi everyone, 

I am currently working with the App "Scroller" in Power BI. And in order to add data/tables to Scroller they have to be decimals (it doesnt get texts). 

I am working with data like this (+0.23ct), (-0.75bp),(+1.75%) etc. which is classified as text in Query but I want it to be classified as "decimals". 

However, when I change it in Query, I either get an Error or ct and bp get changed into %. 

 

Does anyone know how to solve this? Thank you all in advance! 🙂 

5 REPLIES 5
HotChilli
Super User II
Super User II

You have to get rid of the "ct", "bp" parts of the data.  This is just general data cleaning.

You can do this by 'Replace Value' feature. (right-click the column heading to find that )

or 'Split column by delimiter' feature (right-click column heading and choose the appropriate option)

There are lots of ways to do it.

Then change the data type to decimal

Thanks for your quick reply. However, I want to show the ct and bp together with the numbers. 

But unfortunately, Scroller is only able the read decimals tables here (I'm adding the table to "Measure Deviation" in Scroller).

Icey
Community Support
Community Support

Hi @Squirrel15 ,

 

How about this:

 

1. Create two custom column.

custom.JPG

custom.1.JPG

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W0jbQMzJOLlGK1YlW0jXQMzdNKgCztQ2BbFWl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([Column1],"%") then Text.Start([Column1],Text.Length([Column1])-1) else Text.Start([Column1],Text.Length([Column1])-2)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if Text.Contains([Column1],"%") then Text.End([Column1],1) else Text.End([Column1],2)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Custom", type number}, {"Custom.1", type text}})
in
    #"Changed Type1"

 

 

2. Put "Custom.1" column into "Category" field and "Custom" column into "Measure Deviation".

Scroller.gif

 

 

Best Regards,

Icey

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Squirrel15
Frequent Visitor

Hi Icey, 

first of all thank you for helping! 

Unfortunately, this doesnt solve my problem 😕 

 

I want the output of the Scroller to look sth. like this: Dax 12.357 (here this green thing going up) +2.4% - Bund Future 175,34 (red thing going down) -2bp etc. 

 

I still havent found a way to put this -2bp etc into the Measure deviation, so it shows both the number (2) and the text (bp). 

 

Any ideas here?

Icey
Community Support
Community Support

Hi @Squirrel15 ,

 

To my knowledge, "bp" doesn't support in Power BI currently.

You can post your idea on Power BI Ideas forum. It is a place for customers to provide feedback about Power BI.

 

 

Best regards

Icey

 

If this post helps, then consider Accepting it as the solution to help other members find it faster.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors