Split Values in Power BI Query

[MadhavDholakia]

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.

[dufoq3]

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
    ExpandedSplitted

[MadhavDholakia]

thanks @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

[dufoq3 · Accepted solution]

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

[AlienSx]

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_time

[MadhavDholakia]

thanks @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("script@xyz.com"),
    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.

[AlienSx]

watch this

[MadhavDholakia]

Thanks @AlienSx - this fabric community link is broken it seems but I could find the same video on Youtube. It was really helpful.

I have now 3 columns added as Hour, Minute and Second - but all 3 of them are showing error:

Error Details:

Expression.Error: [1,23-1,23] Token Literal expected.
Details:
[List]

Expression.Error: [1,18-1,18] Token Literal expected.
Details:
[List]

Expression.Error: [1,10-1,10] Token Literal expected.
Details:
[List]

This is the current query being used:

let
    Source = Mail,
    tm = Table.TransformColumns(
        Source, 
        {"Overall time spent in business hours", (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, "Overall time spent in business hours", {"Hour", "Minute", "Second"})
in
    exp_time

Can you please suggest how I can resolve this?

Thank you.

[AlienSx]

@MadhavDholakia step on Source. Is everything okay with the table? What is inside "overall time..." column? Must be text values over there. 

[MadhavDholakia]

if I change this to TIME data type, it gives error.

[AlienSx]

Need to look at your data. Remove any sensitive / confidential information and upload your data (or just a part of data generating this error) somewhere so we could grab it and investigate a problem. 

[MadhavDholakia]

Hi @AlienSx - this is how my data looks like in Source step.

Thank you.

ID	Agent Group Name	Created Date	Subject	Status	Agent Name	Priority	Overall time spent in business hours
INC-123456	Level 1	01-06-2024 09:34	Loading Error	Open		Medium	3h 8m 53s
INC-456789	Level 1	01-06-2024 10:12	DB Error	Pending		Medium	1h 40m 42s
INC-456789	Level 2	01-06-2024 10:12	DB Error	Open		Medium	32m 35s