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