Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?!
Solved! Go to Solution.
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
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
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 😉
try this:
@ziying35 That I can do. It's the splitting and summing that I have to do first that is defeating me. Thank you
@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"
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:
Those are the steps that I did to create the query that I posted.
@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:
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!
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!