Forum Discussion
Import Excel Sheet - "This table is empty"
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
Hello Anonymous
could you please post your code from the advanced editor
BR
Jimmy
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.
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