Forum Discussion

aaron797's avatar
aaron797
Advocate I
5 years ago
Solved

Power Query - Trim after character

I have a text field (Pace) that looks like MM:SS.xxxxx (Where 'x' is of varying length)

 

For example, current output

Pace

01:31.240596

01:16.2000

01:30

 

My desired output is

Pace

01:31.240

01:16.200

01:30

 

How do i trim all after '3' digits past a character ('.')?

Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    5 years ago

    Hi,  aaron797 

    Try code as below:

    M query:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjC0MjbUMzIxMLU0U4rVAQsYmukZGRgYwLjGQFYsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Pace = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Pace", type text}}),
    #"Inserted Text Before Delimiter" = Table.AddColumn(#"Changed Type", "Number of Text Before Delimiter", each Text.PositionOf([Pace], ".",1), Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Inserted Text Before Delimiter", "Length", each Text.Length([Pace])),
    #"Added Conditional Column" = Table.AddColumn(#"Added Custom", "Position of last character", each if [Number of Text Before Delimiter] = -1 then [Length] else if [Number of Text Before Delimiter] <> -1 then [Number of Text Before Delimiter]+4 else null),
    #"Added Custom1" = Table.AddColumn(#"Added Conditional Column", "Output", each Text.Middle([Pace],0,[Position of last character])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Number of Text Before Delimiter", "Length", "Position of last character"})
in
    #"Removed Columns"

    Please check my sample file for more details.

     

    Best Regards,
    Community Support Team _ Eason

2 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    5 years ago

    If the first 2 pairs of numbers are always ##:## then you should be able to just take the LEFT ( [Pace], 9 ) or something like that.  If it is <= 9 you will get what is there and if it > 9 it will trim it off.

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    5 years ago

    Hi,  aaron797 

    Try code as below:

    M query:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjC0MjbUMzIxMLU0U4rVAQsYmukZGRgYwLjGQFYsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Pace = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Pace", type text}}),
    #"Inserted Text Before Delimiter" = Table.AddColumn(#"Changed Type", "Number of Text Before Delimiter", each Text.PositionOf([Pace], ".",1), Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Inserted Text Before Delimiter", "Length", each Text.Length([Pace])),
    #"Added Conditional Column" = Table.AddColumn(#"Added Custom", "Position of last character", each if [Number of Text Before Delimiter] = -1 then [Length] else if [Number of Text Before Delimiter] <> -1 then [Number of Text Before Delimiter]+4 else null),
    #"Added Custom1" = Table.AddColumn(#"Added Conditional Column", "Output", each Text.Middle([Pace],0,[Position of last character])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Number of Text Before Delimiter", "Length", "Position of last character"})
in
    #"Removed Columns"

    Please check my sample file for more details.

     

    Best Regards,
    Community Support Team _ Eason