Sample file not loading the entire data

Hey Anonymous,

• 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

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.

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.