Column1 Not Found" Error in Power Query Folder Combine After Promoting Headers

[Waterfront]

Hi Power BI Community,

I’m combining multiple Excel files from a folder in Power Query to create a dataset for Power BI dashboards. Each file has the same structure (~50–60 columns, headers in the 4th row, named tabs like "File_1", "File_2"). My goal is to clean the data (remove columns, change data types), extract specific columns into queries, append them, and load to Power BI.Setup:

• Source: Folder with multiple Excel files.
• Helper Queries: Transform Sample File and Parameter1.
• Combined File: Combines files, includes "Source.Name" (tab names) and data columns.
• Transform Sample File Steps:
  • Remove top 3 rows.
  • Promote 4th row as headers (e.g., "ID", "Value").
• Combined File Steps: Source > Invoke Custom Function (uses Content column, Parameter1) > Removed Other Columns1 (error occurs here).

Issue: "Column1 Not Found" Error

• After promoting headers in the Transform Sample File, the Combined File shows an error in the "Removed Other Columns1" step: "Expression.Error: The column 'Column1' of the table wasn't found."
• What I’ve Tried:
  • Deleted the "Removed Other Columns1" step, but the error persists in other steps.
  • Reconfigured the Invoke Custom Function step to use the Content column and Transform Sample File.
  • Verified all files have identical structure.
• Question: How do I fix the "Column1 not found" error? Should I address it in the Combined File or Transform Sample File?

• Additional Info: I need "Source.Name" to track file identifiers (e.g., extract "1" from "File_1"). Using Power BI Desktop, recent version.Thanks for any help resolving the error and guiding my workflow!Best regards,
  WaterFront

[Waterfront]

Thank you m_dekorte. my issue fixed. much appreciated!

[m_dekorte]

Hi @Waterfront,

It's important you verified all files have an identical structure with matching column names in row 4 of the sheet. And you should move the Removed Other Columns - step to this query (if applicable) and address it in the Transform Sample File as it's logic is applied to all files in the folder. 

If you can't move that step, examine the Combined File query. The process is the same, step through the code by selecting each step one by one from the Aplied Steps, to determine where 'Column1' makes an appearance - once you've identified where and how it comes into being, you can resolve it. 

As for how to extract "1" from "File_1" just use the User Interface, go to: Transform > Extract
There you'll find options to extract the last characters or text after a delimiter - use what suits your data best.

I hope this is helpful