Data Upload

Hi Lorenza,

Power Query is trying to read one or more files as structured Excel workbooks (.xlsx) while their actual format is different, typically .csv, legacy .xls, corrupted files, or files whose extension doesn't match their real content.

Probably, the root cause is due to :

• Mixed file types: some are .xlsx, others are .csv or .xls, but the combine function applies the same treatment to all of them
• Corrupt or empty files within the batch
• Locked/open files on SharePoint at the time of loading
• "Transform Sample File" was built against one .xlsx but other files in the folder have a different format

So to resolve this issue, you can try this guideline step by step:

Step 1: Filter by extension before combining

In Power Query, before the combine step, add a filter on the Extension column:

= Table.SelectRows(Source, each [Extension] = ".xlsx")

This excludes .csv, .xls, and any unrelated file types from the folder.

Step 2: Exclude Office temporary files

SharePoint frequently generates ghost files prefixed with ~$. Add this filter as well:

= Table.SelectRows(Source, each not Text.StartsWith([Name], "~$"))

Step 3: Check the combine function

In the left queries panel, open "Transform Sample File" and confirm the source step uses:

Excel.Workbook([Content], true)

and not Csv.Document or anything else.

Step 4: Identify the problematic file

If the error still occurs, add a custom column to test each file individually and spot which one is breaking:

= try Excel.Workbook([Content], true) otherwise "ERROR"

This lets you pinpoint exactly which file(s) are causing the issue without blocking the entire query.

To sum up, the problem almost certainly comes from one or more files in your SharePoint folder that are not genuine .xlsx, which breaks the combine function. Filtering by extension and excluding temporary files resolves this in the vast majority of cases.

Step 1: Connect to the SharePoint Folder

In Power BI Desktop:

Get Data → SharePoint Folder (not SharePoint List)

Enter your root SharePoint URL only:

https://aroundtown.sharepoint.com/sites/GBL-M365-TQM

This loads a table listing all files in that site, including their binary content.

Step 2: Filter to Keep Only Your .xlsm Files

Once the table loads in Power Query, apply these filters immediately:

Filter by extension:

= Table.SelectRows(Source, each [Extension] = ".xlsm")

Exclude Office temp files:

= Table.SelectRows(#"Filtered Rows", each not Text.StartsWith([Name], "~$"))

Filter to your specific folder (if needed):

= Table.SelectRows(#"Filtered Rows1", each Text.Contains([Folder Path], "YOUR_FOLDER_NAME"))

Step 3: Do NOT Use the Default Combine Button

The "Combine Files" button Power Query suggests automatically uses Excel.Workbook() which doesn't work properly with .xlsm files in some configurations. Instead, add a custom column manually.

Step 4: Add a Custom Column to Extract the Sheet

Click Add Column → Custom Column and use:

= try Excel.Workbook([Content], true){[Item="YOUR_SHEET_NAME", Kind="Sheet"]}[Data] otherwise null

Replace YOUR_SHEET_NAME with the exact tab name that is identical across all 200 files.

If the sheet name varies, use index instead:

= try Excel.Workbook([Content], true){0}[Data] otherwise null

This takes the first sheet of each file regardless of its name.

Step 5: Expand the Tables

Once the custom column is created, click the expand icon (double arrows) at the top of that column → select the columns you need → uncheck "Use original column name as prefix" if you want clean headers.

Step 6: Promote Headers (if needed)

If the first row of each file contains the column headers, add:

= Table.TransformColumns(#"Expanded Column", {"YOUR_CUSTOM_COL", each Table.PromoteHeaders(_)})

Or use the "Use First Row as Headers" button after expanding.

Step 7: Remove Null Rows

After expanding, filter out rows where the custom column returned null (files that failed to parse):

= Table.SelectRows(#"Expanded Column", each [YOUR_CUSTOM_COL] <> null)

Step 8: Close & Apply

Once the preview looks correct → Close & Apply → your combined dataset is ready for visuals.

Remember that since your files are .xlsm (macro-enabled), make sure the sheet you're targeting contains data as a proper table or range, not just VBA output that only renders when macros run. Power Query reads the static data only, macros do not execute during import.