Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Super User

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).

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.

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?

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors