Power Query Editor retrieves data from excel inconsistently, making it useless

[GabrielFLima]

I encountered a problem while importing data using the Power Query Editor and coultn't find anything even parcially related while searching for solutions:

I receive an Excel spreadsheet daily to make a report which has a couple Date fields and one Time field (representing a sum of hours).

Sometimes the Power Query Editor imports those columns as it usually does (considering them as numbers, which I can easily manipulate to get the desired results), but sometimes it imports the dates already formatted as dates (which would actually be good if the change didn't trigger an error) and the Time format gets imported as only ":01", rendering the whole column useless.

I haven't been able to identify anything that may cause it to import the data in one way or the other, and it seems to just happen randomly.

Here are the 2 ways the Dates get imported:

And here is how the Time column gets imported vs the original format in Excel (which is custom):

Excel:

I'd greatly appreciate if anyone can help me make the query work consistently, but especially to make it not import the Time data in such a way that renders it completely useless.

[danextian]

Hi @GabrielFLima ,

Could you kindly provide a sample Excel file along with the expected data types for each column? This would make it easier for us to provide assistance as it gives us something concrete to work with.

[GabrielFLima]

Hello @danextian, 

Here is a link to the sample Excel file. 

The columns' expected data types are:
"Name" - Text
"Month" - Date

"Date" - Date

"Num Hours" - Time

Sorry for the wait, had a couple of non-work days.

[danextian]

It appears that power query cannot read the custom time format in Excel which [hh]:mm. So I changed it to as below, set the initial data type to number before converting to time.

[GabrielFLima]

I figured it'd have something to do with the custom formatting, but what gave me hope of finding a solution that doesn't involve manually adjusting the original file is the fact that sometimes Power Query actually recognizes the values and show them as numbers (my original query used this before it changed the way it recognized the data).

There wouldn't be, by any chance, a way to adjust this detail via query? Because manually adjusting the file kinda defeats PQ's purpose haha.

EDIT: For some reason my sample file gets imported correctly as I want it to without me having to change the file (see screenshot). Each time I get a little more lost

[danextian]

That is a curious and confusing thing. Is it the same file you shared? Can you share you M code?

[GabrielFLima]

Yeah, it's absolutely the same file, and now it's back to how it was originally...

Here's the (very simple) M code I used to test the sample, which started working as intended and today came back to the problem:

let
Fonte = Excel.Workbook(File.Contents("C:\Users\...\sample.xlsb"), null, true),
SAMPLE1 = Fonte{[Name="SAMPLE"]}[Data],
#"Cabeçalhos Promovidos" = Table.PromoteHeaders(SAMPLE1, [PromoteAllScalars=true]),
#"Tipo Alterado" = Table.TransformColumnTypes(#"Cabeçalhos Promovidos",{{"Name", type text}, {"Month", Int64.Type}, {"Date", Int64.Type}, {"Num Hours", type number}})
in
#"Tipo Alterado"