Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Splitting Column in time format as 1h 20m ect

Hi all,

 

I have a column that is formatted like this

Elapsed Time
1h

8h 30m

15m
2h 22m
 

 

I would like to split the column by hours and minutes. 

  • Using Power Query: (assuming h and m are sequential in positions)

     

    let
        Source = Table,
        #"Added Conditional Column" = Table.AddColumn(Source, "Hours", each 
            if Text.Contains([Elapsed Time], "h") then Text.BeforeDelimiter([Elapsed Time], "h")
            else if not Text.Contains([Elapsed Time], "h") then null
            else null
            ),
        #"Added Conditional Column 2" = Table.AddColumn(#"Added Conditional Column", "Minutes", each 
            if Text.Contains([Elapsed Time], "h") and Text.Contains([Elapsed Time], "m") then Text.BetweenDelimiters([Elapsed Time], "h", "m")
            else if Text.Contains([Elapsed Time], "m") then Text.BeforeDelimiter([Elapsed Time], "m")
            else if not Text.Contains([Elapsed Time], "m") then null
            else null) 
    in
        #"Added Conditional Column 2"

     

     

    Output:

     

1 Reply

  • Using Power Query: (assuming h and m are sequential in positions)

     

    let
        Source = Table,
        #"Added Conditional Column" = Table.AddColumn(Source, "Hours", each 
            if Text.Contains([Elapsed Time], "h") then Text.BeforeDelimiter([Elapsed Time], "h")
            else if not Text.Contains([Elapsed Time], "h") then null
            else null
            ),
        #"Added Conditional Column 2" = Table.AddColumn(#"Added Conditional Column", "Minutes", each 
            if Text.Contains([Elapsed Time], "h") and Text.Contains([Elapsed Time], "m") then Text.BetweenDelimiters([Elapsed Time], "h", "m")
            else if Text.Contains([Elapsed Time], "m") then Text.BeforeDelimiter([Elapsed Time], "m")
            else if not Text.Contains([Elapsed Time], "m") then null
            else null) 
    in
        #"Added Conditional Column 2"

     

     

    Output: