Forum Discussion
Power Query Editor retrieves data from excel inconsistently, making it useless
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.
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 lostThat is a curious and confusing thing. Is it the same file you shared? Can you share you M code?