Forum Discussion
Anonymous
3 years agoNot applicable
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
- sevenhillsSuper User
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: