Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I am working with the power query editor in power bi (M).
I want to transform durations based of a column of texts in the format:
0 Seconds |
1 Minute |
2 hours 35 Minutes |
3 Days 12 Hours 10 Minutes |
57 Minutes |
in terms of days. (decimals).
As an example assuming that this column had 2 rows:
what I tried so far but it gives only zeros:
let
durationText = [Full Total Downtime],
splitText = Text.Split(durationText, " "),
totalDays =
List.Sum(
List.Transform(
splitText,
each
let
numericValue = try Number.FromText(Text.BeforeDelimiter(_, " ")) otherwise null, // Extract numeric value
unit = Text.AfterDelimiter(_, " "), // Extract unit (e.g., "Day", "Hour", "Minute", "Second")
multiplier =
if unit = "Day" then 1
else if unit = "Hour" then 1/24
else if unit = "Minute" then 1/(24*60)
else if unit = "Second" then 1/(24*60*60)
else 0
in
if numericValue <> null then numericValue * multiplier else 0
)
)
in
totalDays
Solved! Go to Solution.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlAITk3Oz0spVorViVYyVPDNzCstSQVzjBQy8kuLihWMTaGiEDXGCi6JlcUKhkYKHmBpQwMUaVNzBDcWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Duration = _t]),
#"Added Custom1" = Table.AddColumn(Source, "Text", each [Duration]),
#"Added Index" = Table.AddIndexColumn(#"Added Custom1", "Index", 0, 1, Int64.Type),
#"Capitalized Each Word" = Table.TransformColumns(#"Added Index",{{"Duration", Text.Proper, type text}}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Capitalized Each Word", {{"Duration", Splitter.SplitTextByDelimiter("s", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Duration"),
#"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"Duration", Text.Trim, type text}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Trimmed Text", "Duration", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Duration.1", "Duration.2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Duration.1", Int64.Type}, {"Duration.2", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Duration", each if [Duration.2]="Day" then [Duration.1] else
if [Duration.2]="Hour" then [Duration.1]/24 else
if [Duration.2]="Minute" then [Duration.1]/1440 else
if [Duration.2]="Second" then [Duration.1]/86400 else 0),
#"Grouped Rows" = Table.Group(#"Added Custom", {"Index", "Text"}, {{"Duration", each List.Sum([Duration]), type number}})
in
#"Grouped Rows"
How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlAITk3Oz0spVorViVYyVPDNzCstSQVzjBQy8kuLihWMTaGiEDXGCi6JlcUKhkYKHmBpQwMUaVNzFK6hEcQQQ4VisD1KsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Text = _t]),
#"Replaced Units" = Table.AddColumn(Source, "Duration", each Expression.Evaluate(Text.Combine(List.Select(Splitter.SplitTextByAnyDelimiter({"A".."Z"})(List.Accumulate({{"DA","*86400"},{"HOU","*3600"},{"MINUT", "*60"},{"SECON","*1"}},Text.Upper([Text]),(s,c) => Text.Replace(s,c{0},c{1}))), each _<>"")," + "))/86400)
in
#"Replaced Units"
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LeanAndPractise(Everyday) ) |
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlAITk3Oz0spVorViVYyVPDNzCstSQVzjBQy8kuLihWMTaGiEDXGCi6JlcUKhkYKHmBpQwMUaVNzFK6hEcQQQ4VisD1KsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Text = _t]),
#"Replaced Units" = Table.AddColumn(Source, "Duration", each Expression.Evaluate(Text.Combine(List.Select(Splitter.SplitTextByAnyDelimiter({"A".."Z"})(List.Accumulate({{"DA","*86400"},{"HOU","*3600"},{"MINUT", "*60"},{"SECON","*1"}},Text.Upper([Text]),(s,c) => Text.Replace(s,c{0},c{1}))), each _<>"")," + "))/86400)
in
#"Replaced Units"
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LeanAndPractise(Everyday) ) |
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlAITk3Oz0spVorViVYyVPDNzCstSQVzjBQy8kuLihWMTaGiEDXGCi6JlcUKhkYKHmBpQwMUaVNzBDcWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Duration = _t]),
#"Added Custom1" = Table.AddColumn(Source, "Text", each [Duration]),
#"Added Index" = Table.AddIndexColumn(#"Added Custom1", "Index", 0, 1, Int64.Type),
#"Capitalized Each Word" = Table.TransformColumns(#"Added Index",{{"Duration", Text.Proper, type text}}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Capitalized Each Word", {{"Duration", Splitter.SplitTextByDelimiter("s", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Duration"),
#"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"Duration", Text.Trim, type text}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Trimmed Text", "Duration", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Duration.1", "Duration.2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Duration.1", Int64.Type}, {"Duration.2", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Duration", each if [Duration.2]="Day" then [Duration.1] else
if [Duration.2]="Hour" then [Duration.1]/24 else
if [Duration.2]="Minute" then [Duration.1]/1440 else
if [Duration.2]="Second" then [Duration.1]/86400 else 0),
#"Grouped Rows" = Table.Group(#"Added Custom", {"Index", "Text"}, {{"Duration", each List.Sum([Duration]), type number}})
in
#"Grouped Rows"
How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
102 | |
84 | |
79 | |
70 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |