Changing column type

[TAP936]

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?

[Icey]

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]

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

Result

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 

[TAP936]

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 · Accepted solution]

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

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]

@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