sharepoint folder - missing column names

[Anonymous]

Hi all,

Ive run into interesting behaviour of Power query.

Problem:

I have 2 excel files stored on sharepoint. Some columns in both excel files have header (Serial number), some dont (Empty header).

Both files have same structure of columns, just different data in rows. 

In power query Im using Sharepoint Folder to link these 2 files and combine them into a single query.

The strange behaviour is:

In power query - file1 will display data from both columns (column with empty header and also Serial number header column)

In power query - file2 will display data from Serial number column, but data from Empty column will be displayed as null.

Once I will give a name to an empty header in both files, everything works well and null will become a number as it should. 

Is this normal behaviour? and is there any way how to display data correctly without renaming empty header?

Scenario with empty header

Scenario after giving a name (Price) to an empty header 

[Anonymous]

Hi @Anonymous,

Thanks for reaching out to the Microsoft fabric community forum.

It looks like you have two Excel files stored in SharePoint that contain data with the same structure (same columns) but different rows. Some columns in both files have headers (Serial Number), while others have empty headers (unnamed columns). You are facing unexpected behaviour where in file1 PQ correctly displays data from both the "Serial Number" column and the empty header column and in file2 PQ displays data correctly for the "Serial Number" column, but the empty header column shows null values instead of actual data. However, when you rename the empty headers (e.g., name them "Price"), then everything works as expected, and the null values are replaced by actual numbers.

As @BeaBF already responded to your query, please go through his response and accept it as solution if it answers your query.

I would also take a moment to thank @BeaBF, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

If this post helps then please mark it as a solution, so that other members find it more quickly.

Thank you.

[BeaBF · Accepted solution]

@Anonymous Hi! Yes, this is expected behavior in Power Query when combining files with SharePoint Folder or other folder-based connectors. Here’s why it happens and what you can do about it.

When using SharePoint Folder to combine multiple files, Power Query tries to align columns based on headers. If one file has an unnamed (empty) column header and another file has a named header (e.g., "Serial number"), Power Query treats them as different columns.

If a file has an unnamed column, Power Query may not recognize it properly during automatic column mapping, leading to null values.
If another file has a named column in the same position, Power Query keeps that name and only combines matching headers.
Since File1 has an unnamed header, Power Query still picks up the data. But for File2, it ignores the unnamed column because there's no corresponding match.

Solution:

• Load the data without automatically promoting headers (disable "Use First Row as Headers" in the Source step).
• Manually promote the first row as headers later in the transformation process.
• Replace empty column names dynamically using a custom transformation step.
  
  If it's ok, please accept my answer as solution!
  BBF