Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
Hello, I have searched for a topic closely resembling the problem I'm encountering but haven't had much luck.
Background: I'm connecting to a folder of Excel workbooks and combining them. A new file is added each month. I can't change the source formatting.
The problem: Below is a screenshot of cell display, formula bar display, and custom format selection for a column of call center wait times. (Column J, "Longest Wait Time", is also a custom format, in h:mm:ss). I suspect that this is being chosen simply because it outputs in a format that closely resembles MM:SS, as it displays in the cell.
When I connect to this source in Power Query and combine the files, it auto-converts these columns into Date/Times, so that they look like this:
I figure the cleanest way to track this call wait data is to convert it to seconds. That's where I'm stuck.
What I've tried:
Yet to try:
Any nudges or workarounds would be helpful here. Thank you kindly!
Solved! Go to Solution.
Hi @messyjesse_,
The Query Editor just detects the type of the columns. You can change the display format in the Data pane.
Best Regards,
Dale
I had that issue and I fixed it by adding a new columns like this :
duration = HOUR([Workflow_time])*3600 + MINUTE([Workflow_time])*60 + SECOND([Workflow_time])
This is an old topic but I thought it worthwhile responding, especially given the number of views.
1. If you're able, change the Excel format to [h]:mm rather than h:mm - this will format it to a duration, with the [h] showing the total number of hours.
It is also useful when the duration goes over 24 hours - the value in Excel will still be correct whereas normally (formatted as time with h:mm or hh:mm) for instance you'd see 1:00 (or 01:00) for 25 hours.
2. There is also a (slightly clunky) way to resolve this properly from a time (rather than duration) in Excel:
// Add the hours from the time as an integer
#"Inserted Hour" = Table.AddColumn(Source, "Hour", each Time.Hour([Average Wait Time]), Int64.Type),
// Add the minutes as an integer
#"Inserted Minute" = Table.AddColumn(#"Inserted Hour", "Minute", each Time.Minute([Average Wait Time]), Int64.Type),
// Combine [Hour], ':' and [Minute], as text to form a duration string
#"Convert to Duration" = Table.AddColumn(#"Inserted Minute", "Duration", each Duration.FromText(Text.Combine({Text.From([Hour]),":",Text.From([Minute])})))
Hi @messyjesse_,
The Query Editor just detects the type of the columns. You can change the display format in the Data pane.
Best Regards,
Dale
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.