Forum Discussion
Import Excel - No data shown, only headers
Hi,
I am receiving an Excel file a couple of times a day with a status overview of processed orders. I want to combine these files in Power BI to create a summary, but I am not able to get the data in. Only the headers are shown.
The data is always structured the same way:
In below code I opened a random binary of one of the Excel files, but in the end I will combine them all. (No help needed there)
let
Source = Exchange.Contents("[email protected]"),
Navigation = Source{[Name = "Mail"]}[Data],
#"Filtered rows 1" = Table.SelectRows(Navigation, each ([Folder Path] = "\Inbox\xxxxx\xxxxx\xxxxx\")),
#"Filtered rows" = Table.SelectRows(#"Filtered rows 1", each Text.StartsWith([Subject], "ROB0059_MassOrderCreationBeNeLux - Status Report")),
#"Expanded Attachments" = Table.ExpandTableColumn(#"Filtered rows", "Attachments", {"Name", "Extension", "AttachmentContent"}, {"Name", "Extension", "AttachmentContent"}),
#"Filtered rows 2" = Table.SelectRows(#"Expanded Attachments", each Text.StartsWith([Name], "Status_Report")),
#"Navigation 1" = #"Filtered rows 2"{2}[AttachmentContent],
#"Imported Excel workbook" = Excel.Workbook(#"Navigation 1", null, true),
#"Navigation 2" = #"Imported Excel workbook"{[Item = "Status_Report", Kind = "Sheet"]}[Data]
in
#"Navigation 2"
This is the result. As you can see, only the headers are included and non of the data. When I combined the files and promoted the headers, I was left with 1 row per file with only null in each cell.
Hope you could help me!
If it is xlsx file, then try the following code:
// #"Imported Excel workbook" = Excel.Workbook(#"Navigation 1", null, true), #"Imported Excel workbook" = Excel.Workbook(#"Navigation 1", [UseHeaders = null, DelayTypes = true, InferSheetDimensions = true]),
10 Replies
If it is xlsx file, then try the following code:
// #"Imported Excel workbook" = Excel.Workbook(#"Navigation 1", null, true), #"Imported Excel workbook" = Excel.Workbook(#"Navigation 1", [UseHeaders = null, DelayTypes = true, InferSheetDimensions = true]),At first glance, the result shown did NOT promote the headers; i.e., the header is shown as a record instead of as a header.
Not sure if my comment is helpful, but I hope it is.
Good Luck 🙂True, in the print screen the headers were not yet promoted 🙂 But the issue is that there is nothing below that row, although there is data in the excel file. Promoting the headers only leads to having no rows at all anymore.
Hi Annemieke ,
First check that the output of these previous steps is as expected.
Best Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
Hi, I sometimes get this error when I copy a file into a OneDrive folder. What usually works for me is going into the file and editing something (row width, etc), saving and closing it. Then Power Query retrieves the data as expected.
Thanks for your reply! Unfortunately, the files are in emails and not in a folder and I receive multiple of those emails a day for 2 years already. I connect with the Microsoft Exchange connector, with the goal to combine all the attachments. So unfortunately changing a small thing in a file is not an option 🙈
