The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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.
Solved! Go to Solution.
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:
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: