Forum Discussion
Solved
Split Values in Power BI Query
Hello,
How to split below values in Power BI Query Editor?
Sample Values:
col 1
1h 40m 56s
23m 45s
34s
Expected Results:
col 1, col 2, col 3
1, 40, 56
blank, 23, 45
blank, blank, 34
Thank you.
Like this:
Result
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMk5RMDTJUDAyzVUwNCxWitWJVjLMUDAxyFUwNYNwjYxzFUxMIWxjEyAdCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), Ad_TotalSeconds = Table.AddColumn(Source, "TotalSeconds", each [ a = {{"d", "*86400"}, {"h", "*3600"}, {"m", "*60"}, {"s", "*1"}}, b = List.Accumulate( a, {}, (s,c)=> s & { if not Text.Contains([Column1], c{0}) then null else Text.Replace(List.Select(Text.Split([Column1], " "), (x)=> Text.Contains(x, c{0})){0}?, c{0}, c{1}) } ), c = List.Transform(b, (x)=> try Expression.Evaluate(x) otherwise null), d = List.Sum(c) ][d], Int64.Type), Ad_Splitted = Table.AddColumn(Ad_TotalSeconds, "Splitted", each [ a = Text.SplitAny([Column1], "dhms"), b = List.Transform(List.Select(a, (x)=> Text.Trim(x) <> ""), Number.From), c = List.Repeat({null}, 4 - List.Count(b)) & b, d = Table.FromList({c}, (x)=> x, type table[Days=Int16.Type, Hours=Int64.Type, Minutes=Int64.Type, Seconds=Int64.Type]) ][d], type table), ExpandedSplitted = Table.ExpandTableColumn(Ad_Splitted, "Splitted", {"Days", "Hours", "Minutes", "Seconds"}) in ExpandedSplitted
11 Replies
let Source = sample_table, tm = Table.TransformColumns( Source, {"col 1", (x) => Time.ToRecord( Time.From( Expression.Evaluate( Text.Combine( List.ReplaceMatchingItems( Text.ToList(x), {{" ", "+"}, {"h", "/24"}, {"m", "/1440"}, {"s", "/86400"}} ) ) ) ) ) } ), exp_time = Table.ExpandRecordColumn(tm, "col 1", {"Hour", "Minute", "Second"}) in exp_timethanks AlienSx - I am using attachment from an email as a data source so I am not sure how/where I can add the formulas you have shared, can you please help?
let Source = Exchange.Contents("[email protected]"), Mail1 = Source{[Name="Mail"]}[Data], #"Expanded Sender" = Table.ExpandRecordColumn(Mail1, "Sender", {"Name"}, {"Sender.Name"}), #"Filtered Rows" = Table.SelectRows(#"Expanded Sender", each ([Sender.Name] = "Madhav D")), #"Expanded Attachments" = Table.ExpandTableColumn(#"Filtered Rows", "Attachments", {"AttachmentContent"}, {"Attachments.AttachmentContent"}), #"Attachments AttachmentContent" = #"Expanded Attachments"{0}[Attachments.AttachmentContent], #"Imported CSV" = Csv.Document(#"Attachments AttachmentContent",5553,"",ExtraValues.Ignore,65001), #"Changed Type" = Table.TransformColumnTypes(#"Imported CSV",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}}), #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Column1"}), #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Other Columns", "Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}, {"Column1.3", type text}, {"Column1.4", type text}, {"Column1.5", type text}, {"Column1.6", type text}, {"Column1.7", type text}, {"Column1.8", type text}}), #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type1", [PromoteAllScalars=true]), #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", type text}, {"Agent Group Name", type text}, {"Created Date", type datetime}, {"Subject", type text}, {"Status", type text}, {"Agent Name", type text}, {"Priority", type text}, {"Overall time spent in business hours", type text}}), #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type2", "Overall time spent in business hours", "Overall time spent in business hours - Copy") in #"Changed Type2"Sample Attachment:
Thank you.
Hi MadhavDholakia, another solution. You can decide whether you need TotalSeconds or separately Hours, Minutes and Seconds.
If you don't know how to use my query - read note below my post.
Result:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMsxQMDHIVTA1K1aK1YlWMjLOVTAxhbCNTYB0LAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), Ad_TotalSeconds = Table.AddColumn(Source, "TotalSeconds", each [ a = {{"h", "*3600"}, {"m", "*60"}, {"s", "*1"}}, b = List.Accumulate( a, {}, (s,c)=> s & { if not Text.Contains([Column1], c{0}) then null else Text.Replace(List.Select(Text.Split([Column1], " "), (x)=> Text.Contains(x, c{0})){0}?, c{0}, c{1}) } ), c = List.Transform(b, (x)=> try Expression.Evaluate(x) otherwise null), d = List.Sum(c) ][d], Int64.Type), Ad_Splitted = Table.AddColumn(Ad_TotalSeconds, "Splitted", each [ a = Text.SplitAny([Column1], "hms"), b = List.Transform(List.Select(a, (x)=> Text.Trim(x) <> ""), Number.From), c = List.Repeat({null}, 3 - List.Count(b)) & b, d = Table.FromList({c}, (x)=> x, type table[Hours=Int64.Type, Minutes=Int64.Type, Seconds=Int64.Type]) ][d], type table), ExpandedSplitted = Table.ExpandTableColumn(Ad_Splitted, "Splitted", {"Hours", "Minutes", "Seconds"}, {"Hours", "Minutes", "Seconds"}) in ExpandedSplittedthanks dufoq3 - this has worked as expected. There is only one scenario, apologies that was not involved in my initial question, which is not being handled here.
so in case if the value is as given below, i.e., including Day Value - it shows error. Can you please suggest how I can modify your solution to add this as well?
Thank you.
2d 7h 34m 58s 1d 5h 4m 10s Like this:
Result
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMk5RMDTJUDAyzVUwNCxWitWJVjLMUDAxyFUwNYNwjYxzFUxMIWxjEyAdCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), Ad_TotalSeconds = Table.AddColumn(Source, "TotalSeconds", each [ a = {{"d", "*86400"}, {"h", "*3600"}, {"m", "*60"}, {"s", "*1"}}, b = List.Accumulate( a, {}, (s,c)=> s & { if not Text.Contains([Column1], c{0}) then null else Text.Replace(List.Select(Text.Split([Column1], " "), (x)=> Text.Contains(x, c{0})){0}?, c{0}, c{1}) } ), c = List.Transform(b, (x)=> try Expression.Evaluate(x) otherwise null), d = List.Sum(c) ][d], Int64.Type), Ad_Splitted = Table.AddColumn(Ad_TotalSeconds, "Splitted", each [ a = Text.SplitAny([Column1], "dhms"), b = List.Transform(List.Select(a, (x)=> Text.Trim(x) <> ""), Number.From), c = List.Repeat({null}, 4 - List.Count(b)) & b, d = Table.FromList({c}, (x)=> x, type table[Days=Int16.Type, Hours=Int64.Type, Minutes=Int64.Type, Seconds=Int64.Type]) ][d], type table), ExpandedSplitted = Table.ExpandTableColumn(Ad_Splitted, "Splitted", {"Days", "Hours", "Minutes", "Seconds"}) in ExpandedSplitted
