Forum Discussion

GabrielFLima's avatar
GabrielFLima
Frequent Visitor
2 years ago

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 m...
Need Help
GabrielFLima's avatar
GabrielFLima
Frequent Visitor
2 years ago

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's avatar
danextian
Super User
2 years ago

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

  • GabrielFLima's avatar
    GabrielFLima
    Frequent Visitor
    2 years ago

    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"