Sample file not loading the entire data

[Phani_Mantha]

Hi, I am trying to use a folder as data source and filter the most recently added excel file to load the data. Power BI succesfully creates the sample file, below is the query from advanced editor from the sample file.

Advanced editor: 

let
Source = Folder.Contents("C:\Users\pmanth\OneDrive - PVH Corp\Inbound monitoring\New folder"),
#"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".xlsx")),
#"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Date created", Order.Descending}}),
#"Kept First Rows" = Table.FirstN(#"Sorted Rows",1),
Navigation1 = #"Kept First Rows"{0}[Content]
in
Navigation1

However, when I expand the sample file, I only see a single cell as the entire data, whereas my excel files contains lot of rows and columns.

Data from Sample file

 There are no error in this step but I get the Expression.Error in my 'Transform Sample File' query. I am assuming this error is because only a single cell is being loaded as data source. I really cannot understand why the data is being loaded properly. Any suggestions on how I can resolve this issue would be highly appreciated. Many thanks in advance!Expression.Error

[jaineshp · Accepted solution]

Hey @Phani_Mantha,

• Navigate to the actual worksheet - After getting the file content, you need to specify which sheet to use:
  • Add this step: Navigation2 = Navigation1{[Item="Sheet1",Kind="Sheet"]}[Data]
  • Replace "Sheet1" with your actual sheet name
• Check your Excel file layout - Make sure:
  • Data starts from A1 (no blank rows/columns at the top)
  • No merged cells above your data
  • Consistent structure across all Excel files
• Update your M code like this:

Navigation1 = #"Kept First Rows"{0}[Content],
Navigation2 = Navigation1{[Item="YourSheetName",Kind="Sheet"]}[Data]

The key issue is you're stopping at the workbook level instead of drilling down to the actual worksheet data. Once you add that navigation step, your transform should work properly.

Fixed? ✓ Mark it • Share it • Help others!

Best Regards,
Jainesh Poojara | Power BI Developer

[Shahid12523 · Accepted solution]

Why You're Seeing Only a Single Cell
The issue stems from this line in your query:
Navigation1 = #"Kept First Rows"{0}[Content]

This returns the binary content of the most recent Excel file, but Power BI doesn’t know how to interpret that binary unless you explicitly navigate to a sheet, table, or named range inside the file. Without that, it defaults to a generic binary preview — hence the single cell.

🛠️ Fix: Properly Navigate to the Sheet or Table
You need to add a step that opens the binary and selects the correct sheet. Here's how to do it:

✅ Updated Sample File Query

let
Source = Folder.Contents("C:\Users\pmanth\OneDrive - PVH Corp\Inbound monitoring\New folder"),
#"Filtered Rows" = Table.SelectRows(Source, each [Extension] = ".xlsx"),
#"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Date created", Order.Descending}}),
#"Kept First Rows" = Table.FirstN(#"Sorted Rows",1),
FileContent = #"Kept First Rows"{0}[Content],
ExcelFile = Excel.Workbook(FileContent, null, true),
SheetData = ExcelFile{[Item="Sheet1", Kind="Sheet"]}[Data]
in
SheetData

🔁 Replace "Sheet1" with the actual sheet name in your Excel file. You can inspect ExcelFile to see available sheets and tables.

Why the Transform Sample File Fails
Your "Transform Sample File" query likely uses Table.PromoteHeaders(...) on a table that doesn’t exist yet — because the binary wasn’t properly expanded. Once you fix the sample file query to return a full table, the transform logic will work as expected.

[Phani_Mantha]

Thanks for your quick replies @jaineshp @Shahid12523 . I managed to resolve the issue as you mentioned, thank you!

[Shahid12523 · Accepted solution]

let
Source = Folder.Contents("C:\Users\pmanth\OneDrive - PVH Corp\Inbound monitoring\New folder"),
#"Filtered Rows" = Table.SelectRows(Source, each [Extension] = ".xlsx"),
#"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Date created", Order.Descending}}),
#"Kept First Rows" = Table.FirstN(#"Sorted Rows",1),
FileContent = #"Kept First Rows"{0}[Content],
ExcelFile = Excel.Workbook(FileContent, null, true),
SheetData = ExcelFile{[Item="Sheet1", Kind="Sheet"]}[Data]
in
SheetData

🔁 Replace "Sheet1" with the actual sheet name in your Excel file. You can inspect ExcelFile to see available sheets and tables.

[Shahid12523 · Accepted solution]

Why You're Seeing Only a Single Cell
The issue stems from this line in your query:
Navigation1 = #"Kept First Rows"{0}[Content]

This returns the binary content of the most recent Excel file, but Power BI doesn’t know how to interpret that binary unless you explicitly navigate to a sheet, table, or named range inside the file. Without that, it defaults to a generic binary preview — hence the single cell.

🛠️ Fix: Properly Navigate to the Sheet or Table
You need to add a step that opens the binary and selects the correct sheet. Here's how to do it:

✅ Updated Sample File Query

let
Source = Folder.Contents("C:\Users\pmanth\OneDrive - PVH Corp\Inbound monitoring\New folder"),
#"Filtered Rows" = Table.SelectRows(Source, each [Extension] = ".xlsx"),
#"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Date created", Order.Descending}}),
#"Kept First Rows" = Table.FirstN(#"Sorted Rows",1),
FileContent = #"Kept First Rows"{0}[Content],
ExcelFile = Excel.Workbook(FileContent, null, true),
SheetData = ExcelFile{[Item="Sheet1", Kind="Sheet"]}[Data]
in
SheetData

🔁 Replace "Sheet1" with the actual sheet name in your Excel file. You can inspect ExcelFile to see available sheets and tables.

Why the Transform Sample File Fails
Your "Transform Sample File" query likely uses Table.PromoteHeaders(...) on a table that doesn’t exist yet — because the binary wasn’t properly expanded. Once you fix the sample file query to return a full table, the transform logic will work as expected.

[jaineshp · Accepted solution]

Hey @Phani_Mantha,

• Navigate to the actual worksheet - After getting the file content, you need to specify which sheet to use:
  • Add this step: Navigation2 = Navigation1{[Item="Sheet1",Kind="Sheet"]}[Data]
  • Replace "Sheet1" with your actual sheet name
• Check your Excel file layout - Make sure:
  • Data starts from A1 (no blank rows/columns at the top)
  • No merged cells above your data
  • Consistent structure across all Excel files
• Update your M code like this:

Navigation1 = #"Kept First Rows"{0}[Content],
Navigation2 = Navigation1{[Item="YourSheetName",Kind="Sheet"]}[Data]

The key issue is you're stopping at the workbook level instead of drilling down to the actual worksheet data. Once you add that navigation step, your transform should work properly.

Fixed? ✓ Mark it • Share it • Help others!

Best Regards,
Jainesh Poojara | Power BI Developer