Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Annemieke
Frequent Visitor

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:

Annemieke_0-1684159057254.png

 

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("xxxxxxx@xxxxxx.com"),
  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.

Annemieke_1-1684160427597.png

 

Hope you could help me!

5 REPLIES 5
ND_Pard
Helper II
Helper II

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.

vcgaomsft_1-1685079746542.png

 

Best Regards,
Gao

Community Support Team

 

If 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 Gao,

Thanks for your response! Yes, the output of the previous steps is as expected. I see all emails, am able to expand the attachment columns and can open the needed binary of an excel file or combine the excel files that are in the attachments.

 

I don't get any errors, but for some reason only the first row is imported for these files, which only contains the headers.

Hi @Annemieke ,

 

I checked the code and there seems to be nothing wrong with it. Please find the file according to the previous filter and check if it is empty.

 

Best Regards,
Gao

Community Support Team

 

If 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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors