Import Excel Sheet - "This table is empty"

[Anonymous]

Hello Power Bi community.

I have a really strange behaviour while trying to import a sheet from an Excel file.

The sheet cleary has data in it, as you can see on the below screenshot.

Now when I'm importing this in Excel, it reads the headers but otherwise tells me that the sheet is empty:

I have no explanation for this. Can anyone help?

[Jimmy801]

Hello @Anonymous 

now I can think of what is the reason. Go to your Excel-file, go to the first unused row, select all rows till the end, change height of this rows, delete them and make a save as with the same name. Retry to refresh the query

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Jimmy801]

Hello @Anonymous 

could you please post your code from the advanced editor

BR

Jimmy

[Anonymous]

Hello @Jimmy801 ,

thanks for caring.

This is the M Code, its really simple:

let
    Source = Excel.Workbook(File.Contents("\\CXXX\D7_OutputFile_2020-09-25.xlsx"), null, true),
    Consolidated_Sheet = Source{[Item="Consolidated",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Consolidated_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Country Code", type text}, {"Invoice Number", Int64.Type}, {"Date ", type datetime}, {"Amount", Int64.Type}, {"Currency", type text}, {"Vendor Name", type text}, {"Vendor Email ID", type text}, {"Due - Non Due for Payment", type text}, {"Invoice Status", type text}, {"Comments", type any}, {"Voucher Number", type text}, {"Amount Diff", type any}, {"Posted to Vendor Code", Int64.Type}, {"Email sent to", type any}, {"Email sent on", type any}, {"Ageing", Int64.Type}, {"Ageing Bucket", type text}})
in
    #"Changed Type"

The thing is: the file I try to read is created automatically every day and having to manually format it is exactly what we are trying to avoid by using Power BI 😉

I tried a few things and what makes the file "readable" is simply copying the contents of the sheed and pasting them again in the same position as before. The file is no different then but suddenly Power BI can sees all rows.

[Jimmy801 · Accepted solution]

Hello @Anonymous 

then, there is no way to fix this... or at least no easy way 🙂 . The thing is that power bi reads the UsedRange of Excel and in some cases this object could be wrong. Especially when the file is created automatically.

As i suggested you can fix the file manually before or you could try to change the interface... maybe there is a way to get a csv.file that would be by the way faster.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy