cancel
Showing results for 
Search instead for 
Did you mean: 
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
Super User III
Super User III

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.

View solution in original post

Fowmy
Super User IV
Super User IV

@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!

Proud to be a Super User!

Website   YouTube    LinkedIn

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