Forum Discussion

MadhavDholakia's avatar
MadhavDholakia
Icon for Helper I rankHelper I
2 years ago
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, bla...
Need Help
Show and Tell
  • dufoq3's avatar
    dufoq3
    2 years ago

    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
MadhavDholakia's avatar
MadhavDholakia
Icon for Helper I rankHelper I
2 years ago

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("[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.

  • MadhavDholakia's avatar
    MadhavDholakia
    Icon for Helper I rankHelper I
    2 years ago

    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's avatar
      AlienSx
      Icon for Super User rankSuper User
      2 years ago

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

      • MadhavDholakia's avatar
        MadhavDholakia
        Icon for Helper I rankHelper I
        2 years ago

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