Forum Discussion
Power Query Editor retrieves data from excel inconsistently, making it useless
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.
6 Replies
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.
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.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.
