cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

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?!

1 ACCEPTED SOLUTION
Super User III
Super User III

Hello @JR_Cary 

 

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

View solution in original post

14 REPLIES 14
Super User III
Super User III

Hello @JR_Cary 

 

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

View solution in original post

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. 

 

Hello @JR_Cary 

 

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

You are corrrect @Jimmy801 . The fields are manageable in excel but I was just hoping to make that change in BI. The solution I found was a combination of the suggestions listed here. I used the advanced editor formula to get the duration times broken up by "/", then did a combination of reformating, splitting, removing a suffix, adding a prefix, then reformatting. Probably not the most elegant solution but it works! I appreciate the help of everyone that chimed in. 

Hello @JR_Cary 

 

allright. I think the solution provided works perfectly fine with the provided data. However, I didn't see any prefixes in your data 🙂

 

Good luck

 

Jimmy

There weren't, or at least not true ones. Those were created when I was spiltting out the times in the 12:01:18 in text format. That then became 01:18, then 00:01:18, then converted to Duration. Or something like that 😉 

Impactful Individual
Impactful Individual

try this:

3.png

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

Super User IV
Super User IV

@JR_Cary - 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"

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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. 

@JR_Cary - 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.

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@Greg_Deckler  I think this is getting me somewhere! I had originally thought to try splitting by the "/" delimiter but I wasn't sure if I would run into problems with the fields that list Date and time (example: 12/31/1899 12:07:57 AM). 

 

For the fields with duplicate values, this works great. However, for the ones with the one date/time entry I get columns like this: 

JR_Cary_0-1598500423194.png

Which I think I could make work by replacing the 12.00:00:00 and 31.00:00:00 with 0, and replacing the value causing the error in the 3rd column (I think is the 1899/year)....BUT.... none of the created columns have any times in them. They are all 00:00:00. 

 

It might be worth mentioning that for this specific excersise, the data in the preview is just a small fraction of all of the data, so for this query change, it created 11 new columns but know that I have seen as many as 18 individual timestamps in one cell so there would need to be at least that many columns created. Does BI have the smarts to continue this on for the entire data set, or will it only create the columns it sees in the preview. Perhaps a dumb question !

 

@JR_Cary - I would need to see what that Error says. As for the last question, that's an interesting question actually. You may have to modify the behavior to include additional columns created by rows not within the preview. 

 

I am likely quickly getting out of my depth on this matter as my Power Query Fu is not as strong as someone like @ImkeF or @edhans so I may have to bow out and let one of the true Power Query experts take over. No offense but I can only go so deep on certain subjects!


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thanks @Greg_Deckler  I truly do appreciate your help. The error was just a product of converting the text of "1899 12:07:41 AM" to duration. I can split that using the space as delimiter and just replace all errors with 00:00:00...... and I think I will be good with that. Think being the key word here... 

 

Again thanks for your help! 

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors