Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Splitting, Summing and Reformatting all in the same cell

Hi All. I'm a beginner user trying to do what I feel is advanced level user type work but I'm stuck with it so..... 

 

My problem. I am building a table using an xlxs file. The "Date" field is a custom formatted cell (hh:mm:ss) in excel which gives me values that look like "00:07:47"  and that is what I expect. This is a field for duration (Test times). When the excel file is imported into BI and I am working on it in the Power Query editor, it changes the format to "12/31/1899 12:07:47". I have been unsuccessful in every attempt to reformat that into a type that doesn't give me errors, and allows me to use a calculable field (average test times, etc). I need to be able to show average test duration in hh:mm:ss. 

 

Also, to add to my problem, the original file has multiple duration entries.

So, I have an original excel file that looks like this: 

0:00:10
0:08:11
00:04:42/00:04:12/00:05:21/00:04:47/00:11:26
0:01:51
0:06:39
0:00:10
0:09:21
00:01:22/00:17:07
0:10:51

  

But when imported into BI/Transform data, it ends up looking like this: (no this isn;t the same data just an example)

12/31/1899 12:07:57 AM
12/31/1899 12:10:32 AM
12/31/1899 12:09:06 AM
00:22:54/00:14:39
00:30:30/00:08:04
12/31/1899 12:08:16 AM
00:04:42/00:04:12/00:05:21/00:04:47/00:11:26

 

So my problems are two fold. I need to somehow sum all of the duplicate values into one value, then split out and convert the date/time values to the hh:mm:ss duration (so a date/time value of 12/31/1899 12:08:16 AM would look like 00:08:16. 8 minutes, 16 seconds duration). 

 

Maybe I'm trying to overcomplicate this. Maybe what I want to do isn't possible..... but I'm going insane trying to figure it out. 

 

Can anybody please help me?!

  • Jimmy801's avatar
    Jimmy801
    6 years ago

    Hello Anonymous 

     

    add a custom column and add this function. Column name has to be "Duration" and formated as text

     

    List.Accumulate(List.Transform(Text.Split(_[Duration],"/"), each Duration.From(_)),#duration(0,0,0,0), (s,c)=> s + c)

     

    Here the complete example for reproducing it

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc3RDcAgCATQXfxuUs6q1FvFuP8atSBN/x6EO8ZIQhFC0jzMNwH3GgpLPh1wVGbsTdEXAHOLMFgRbrx6+P+gr4bvwQpbL5Si+wJiLfMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Duration = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Duration", type text}}),
    SumAllDuration = Table.AddColumn
    (
        #"Changed Type",
        "SumDuration",
        each List.Accumulate(List.Transform(Text.Split(_[Duration],"/"), each Duration.From(_)),#duration(0,0,0,0), (s,c)=> s + c),
        type duration
    )
in
    SumAllDuration

     

     

     

    As alternative you can also apply a Table.TransfromColumns. (so you don't need a additional column

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc3RDcAgCATQXfxuUs6q1FvFuP8atSBN/x6EO8ZIQhFC0jzMNwH3GgpLPh1wVGbsTdEXAHOLMFgRbrx6+P+gr4bvwQpbL5Si+wJiLfMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Duration = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Duration", type text}}),
    SumAllDuration = Table.TransformColumns
    (
        #"Changed Type",
        {
            "Duration",
            each List.Accumulate(List.Transform(Text.Split(_,"/"), each Duration.From(_)),#duration(0,0,0,0), (s,c)=> s + c),
            type duration
        }
    )
in
    SumAllDuration

    Copy paste this code to the advanced editor in a new blank query to see how the solution works.

    If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
    Kudoes are nice too

    Have fun

    Jimmy

14 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    Anonymous - I did it like the following. PBIX is attached below sig, you want Table 14

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc3RDcAgCATQXfxuUs6q1FvFuP8atSBN/x6EO8ZIQhFC0jzMNwH3GgpLPh1wVGbsTdEXAHOLMFgRbrx6+P+gr4bvwQpbL5Si+wJiLfMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Duration = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Duration", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Duration", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Duration.1", "Duration.2", "Duration.3", "Duration.4", "Duration.5"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Duration.1", type duration}, {"Duration.2", type duration}, {"Duration.3", type duration}, {"Duration.4", type duration}, {"Duration.5", type duration}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1",null,#duration(0, 0, 0, 0),Replacer.ReplaceValue,{"Duration.2", "Duration.3", "Duration.4", "Duration.5"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each [Duration.1]+[Duration.2]+[Duration.3]+[Duration.4]+[Duration.5]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Duration.1", "Duration.2", "Duration.3", "Duration.4", "Duration.5"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Duration"}})
in
    #"Renamed Columns"

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you Greg_Deckler . I appreciate the response. I tried copying your query changes and pasting them into the advanced editor... starting with 

      #"Changed Type" = Table.TransformColumnTypes(Source,{{"Duration", type text}}),

      I then changed "Duration" to elapsed time (my fields name) and a few other tweaks to make sure i wasn;t writing over or duplicating anything. Unfortunately I kept getting an error that it couldn't find that field name. I've created a sample pibx with some of my timestamp data but I'm not sure how to upload that. 

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        6 years ago

        Anonymous - Let's try it like this:

        1. Start with your column of values as Text
        2. Select your column in Power Query Editor 
        3. From the Transform tab, choose Split Column, choose Custom and enter /
        4. Select all of the columns created, use the Ctrl or Shift keys
        5. Right-click a column header and choose Change Type and then Duration
        6. Select all of these columns again
        7. Right-click a column header and choose Replace Values, enter null and 0
        8. From Add column tab, choose Custom Column. Select each of the columns created and put a + between them, [Duration.1]+[Duration.2]+[Duration.3], etc.
        9. Select all of the intermediary columns created (not our Custom column), right-click a column header and choose Delete
        10. Double-click our Custom column header and rename to the original column name

        Those are the steps that I did to create the query that I posted.

         

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      ziying35 That I can do. It's the splitting and summing that I have to do first that is defeating me. Thank you

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    6 years ago

    Hello Anonymous 

     

    add a custom column and add this function. Column name has to be "Duration" and formated as text

     

    List.Accumulate(List.Transform(Text.Split(_[Duration],"/"), each Duration.From(_)),#duration(0,0,0,0), (s,c)=> s + c)

     

    Here the complete example for reproducing it

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc3RDcAgCATQXfxuUs6q1FvFuP8atSBN/x6EO8ZIQhFC0jzMNwH3GgpLPh1wVGbsTdEXAHOLMFgRbrx6+P+gr4bvwQpbL5Si+wJiLfMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Duration = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Duration", type text}}),
    SumAllDuration = Table.AddColumn
    (
        #"Changed Type",
        "SumDuration",
        each List.Accumulate(List.Transform(Text.Split(_[Duration],"/"), each Duration.From(_)),#duration(0,0,0,0), (s,c)=> s + c),
        type duration
    )
in
    SumAllDuration

     

     

     

    As alternative you can also apply a Table.TransfromColumns. (so you don't need a additional column

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc3RDcAgCATQXfxuUs6q1FvFuP8atSBN/x6EO8ZIQhFC0jzMNwH3GgpLPh1wVGbsTdEXAHOLMFgRbrx6+P+gr4bvwQpbL5Si+wJiLfMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Duration = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Duration", type text}}),
    SumAllDuration = Table.TransformColumns
    (
        #"Changed Type",
        {
            "Duration",
            each List.Accumulate(List.Transform(Text.Split(_,"/"), each Duration.From(_)),#duration(0,0,0,0), (s,c)=> s + c),
            type duration
        }
    )
in
    SumAllDuration

    Copy paste this code to the advanced editor in a new blank query to see how the solution works.

    If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
    Kudoes are nice too

    Have fun

    Jimmy

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks for helping out Jimmy801  

       

      I have tried both of your suggestions and here is what I run into. 

       

      Creating custom field: I get no syntax errors, hit ok then it creates a new Duration column. The values that are already in hh:mm:ss format all seem to be summing properly into this one field, which is AWESOME! The only issue I still have is that all of the Date/Time entries show "error" in this new field. Here is the error it states: 

       

      Expression.Error: We couldn't parse the Duration literal.
      Details:
      1899 12:05:19 AM

       

      The fact that the repeat values are summing now is amazingly helpful even with the other errors. I feel like I can probably work around those and end up combining two clean columns at the end. Hopefully.

       

      I did also try the 2nd suggestion but it went straight to error: 

      Expression.Error: The column 'Duration' of the table wasn't found.
      Details:
      Duration

       

       

      Your first suggestion seems to be getting me in the right direction though. Am I correct in my assumption that I can use the newly column, then work on splittging/extracting out the 12/31/1899 12:00:00 times in the original column, then combine the two columns? Of course, I'm all ears if there is a better way. 

       

      Thanks guys for all your help. I am soooo close I can feel it. I was supposed to have these reports turned in last Friday. Our data is just disgustingly messy. Have already spent two weeks just trying to clean it up. 

       

      • Jimmy801's avatar
        Jimmy801
        Community Champion
        6 years ago

        Hello Anonymous 

         

        I think your errors coming coming from the autoformatting of Power BI. Please import the data and don't apply any type changes, because as you were stating in your first post, that your Excel-column is consisting of only duration, sometimes even more than one duration in one cell, combined with "/". 

        The other error you get is because you are referencing a column called "Duration", that probably in your database is not existing. Just adapt the column name in my formula, thats it. 

         

        all the best

         

        Jimmy