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
TAP936
Helper I
Helper I

Changing column type

Hi,

 

I have a results column that has a data format and type of text. Most values in the column are decimal numbers but there are a few that include text/characters i.e. <0.3 or .83T so I cannot simply change the format to decimal.

 

I need the column to be data format and type of decimal so I can create measures but I cannot lose the results that have characters in them. 

Do i have to create a new column and filter it some how?

 

This is a direct query report & i'm new to power query and dax so a bit lost here, can anyone please advise?

1 ACCEPTED SOLUTION
candicehan
Frequent Visitor

You can use the Repace Value function to replace all characters that you don't want.

candicehan_0-1605903849075.png

Value to Find - put the character you want to remove

Replace With - leave it as blank

Then, you will remove all the characters you don't want and be able to change the data type to decimals.

View solution in original post

5 REPLIES 5
Icey
Community Support
Community Support

Hi @TAP936 ,

 

Please let us know if the replies above are helpful.

 

If they are, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

 

If not, please let us know.

 

 

Best Regards,

Icey

Jimmy801
Community Champion
Community Champion

Hello @TAP936 

 

this is exactly the main field of Power Query

in this case use Table.TransformColumns to change your value and change the column type afterwards. Use the characters in Text.SplitAny to define witch characters have to be removed. Text.SplitAny(val,"T><")

Here an example

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtAzVorViVYy1TMF04YGYCqm1MDAOBkmqWdhHAJmGBmAlMUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [YourColumn = _t]),
    TransformYouColumn = Table.TransformColumns
    (
        Source,
        {
            {
                "YourColumn", 
                (val)=> Number.From(Text.Combine(Text.SplitAny(val,"T><")),"en-US"),
                type number
            }
        }
    )
in
    TransformYouColumn

Before

Jimmy801_0-1605947194018.png

 

Result

Jimmy801_1-1605947208979.png

 

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy 

Hi Jimmy, thanks for your reply, I think this would work for me but im afraid I do not know how to recreate it in my own data, limits of my knowledge not your response. Thank you though.

candicehan
Frequent Visitor

You can use the Repace Value function to replace all characters that you don't want.

candicehan_0-1605903849075.png

Value to Find - put the character you want to remove

Replace With - leave it as blank

Then, you will remove all the characters you don't want and be able to change the data type to decimals.

Fowmy
Super User
Super User

@TAP936 

A column should always contain a single data type. The best option here is to request the data source owner to segregate the values on to two columns.

However, If you need to add a column, you will have to create an additional column but in Direct Query, only simple functions are allowed.

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

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