Excel hh:mm:ss Duration Column Imports as Null in Power BI (SharePoint Source)

[CliveM]

Hi, 

I'm trying to import a dataset from an Excel file stored in a SharePoint folder into Power BI. The file includes a duration field formatted in Excel as a custom hh:mm:ss. When I open the file directly in Excel and load it into Power Query (within Excel), the duration data is read correctly. However, when I use either the Web connector or the SharePoint connector in Power BI to import the same file, the duration field defaults to null values. This issue persists regardless of which connector I use in Power BI, but the same data is read without issues when using Power Query in Excel.

This was working perfectly fine until this week.

[Nasif_Azam · Accepted solution]

Hey @CliveM ,

Thanks for sharing the screenshots and the context. You're encountering a common issue where Excel's custom time formats (like hh:mm:ss) are not correctly interpreted by Power BI when importing from SharePoint, leading to null values.

Workaround 1: Use Excel.File instead of Excel.Workbook

• In Power BI, open Advanced Editor of the query.
• Change the function from:

Excel.Workbook(...)
to:

Excel.Workbook(..., true)
This forces the connector to preserve cell types, which often helps with duration fields.

Workaround 2: Convert Duration to Text in Excel Before Import

• In Excel, add a helper column:     =TEXT(A2, "hh:mm:ss")
• Save and reupload to SharePoint.
• In Power BI, load this helper column.
• Convert it back to duration using Power Query:     = Time.FromText([HelperColumn])

Workaround 3: Use Power Automate to Export as CSV First
Create a flow that:

• Triggers when a file is added/updated in SharePoint.
• Exports the Excel table to CSV (where durations are converted to text).
• Load the CSV into Power BI.

Workaround 4: Store Time as Decimal in Excel
Since Excel stores time as a decimal (e.g., 0.5 = 12 hours), you could:

• Add a column in Excel with:  =A2*24*60*60  (converts time to total seconds)
• Load this numeric value into Power BI and convert back using:    = #duration(0, 0, 0, [SecondsColumn])

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

[CliveM]

Hi Nasif, 
Thanks for the suggestions. I was already using Excel.Workbook(..., true).

Luckily the original data is in .csv format which has resolved the problem. It is still a bit confusing that it had been working until this week.

Anyway, thanks for the detailed response.

[nathancwatkins]

What is the underlying value in the formula bar in the source Excel file? Also, what is the value in Power BI before any transformations? Keep in mind, Excel uses the decimal date format for datetimes, and Power Query in Excel recognizes it. Power BI 

uses the proper datetime format, and doesn't automatically convert decimals to datetimes or times.

--Nate

[CliveM]

Hi Nate, thanks for the reply.

To clarify:

In the source Excel file, the Duration column is formatted as hh:mm:ss. When I click on the cell, the formula bar shows a time, like 12:45:43 AM, which suggests it's being stored as a decimal datetime under the hood.

In Power BI, when I preview the data before any transformations, the column appears as null — even though I can see the time in Excel.

That’s why I initially thought it might be a formatting or type mismatch. I also tested the same file in Power Query inside Excel, and it does read the value correctly as a time — but Power BI doesn’t.

This exact setup worked before (same export method, same format), so I’m not sure if something changed with how Power BI handles Excel connector data types — but it seems like it now fails to interpret the time unless it’s preformatted differently (e.g., as text or decimal).

[v-vpabbu]

Hi @CliveM,

The issue is not a bug but a data type and format handling difference between the tools. To fix this, you’ll need to adjust your data preparation or import steps.

Regards,

Vinay Pabbu

[CliveM]

Hi Vinay, 

Thanks for the reply. It was working just fine until this week. Switching to .csv has resolved the problem.

[grognard]

Hello,

Excel seems to automatically convert hh:mm:ss to YY:MM:DD when saved: 

I’d recommend verifying that excel isn't treating hh:mm:ss as a date first if you haven’t already. 

When Power query encounters an error, it won’t usually convert it into an error without additional M language. If you can provide some of the M language used (with any URLs and important names redacted of course) we might be able to solve the problem faster.

[CliveM]

Hi grognard,

Thanks for the suggestion! I did suspect Excel might be treating the values as times, so when building the original query I took that into account. 

Here’s a quick outline of my Power Query steps:

Step 1 – Import files from a SharePoint folder
Step 2 – Expand the Processed Data column dynamically
Step 3 – Clean and reshape the data, including extracting Term and Program
Step 4 – Add a column that calculates total duration in minutes from the original Duration column

This setup was working fine before — I was pulling in Excel exports that had the Duration column formatted as hh:mm:ss, and Power Query was interpreting it correctly.

Now, however, I’m suddenly getting null values in the Duration column after import — even though I can open the file in Excel and see the durations are still there.

Here’s the part of the function that handles the column typing and conversion:

// Convert Duration column
WithDuration = Table.TransformColumnTypes(PromotedHeaders, {{"Duration", type time}}),

// Fix Duration format
WithFixedDuration = Table.AddColumn(WithDuration, "Formatted Duration", each let
rawTime = Time.ToText([Duration], "HH:mm:ss"),
parts = Text.Split(rawTime, ":"),
correctedHour = if parts{0} = "12" then "00" else parts{0}
in correctedHour & ":" & parts{1} & ":" & parts{2]),

// Convert Duration to Minutes
WithTotalMinutes = Table.AddColumn(WithFixedDuration, "Total Duration (Minutes)", each let
convertedDuration = Duration.FromText([Formatted Duration]),
totalMinutes = (Duration.Hours(convertedDuration) * 60) + Duration.Minutes(convertedDuration)
in totalMinutes)

Prior to this step is just 

Overall query steps