Value not showing when consolidating tables but appear when selecting only one

[newgirl2]

Hello!

I'm experiencing this weird situation.

So I have a sharepoint folder in which all have the same layout and I need to consolidate them into one query.

Screenshot of files

I'd like to focus on these columns. So when I filter ARGL Date, there is amount under Balance as of Date, as seen in the actual Excel file when I open it.

However, I noticed that when I ran my query table, it says there are only null values!

let
    Source = SharePoint.Files("https://XXX.sharepoint.com/sites/XXXX", [ApiVersion = 15]),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Folder Path] = "https://XXX.sharepoint.com/sites/XXXX/Shared Documents/Master Data File/Report Process Manual/Automation Project/AR Management Report/Raw Data/")),
    #"Invoked Custom Function" = Table.AddColumn(#"Filtered Rows", "Query1", each Query1([Content])),
    #"Removed Other Columns" = Table.SelectColumns(#"Invoked Custom Function",{"Query1"}),
    #"Expanded Query1" = Table.ExpandTableColumn(#"Removed Other Columns", "Query1", {"Account", "Branch account", "Company Code", "Business Area", "Division", "PRODUCT ", "STATUS", "VP", "CM", "ASM", "Debit/Credit ind", "G/L Account", "Assignment", "Reference", "Document type", "Document Date", "Net due date", "Arrears after net due date", "Special G/L ind.", "Amount in local currency", "Difference", "Clearing", "Committed", "Credit limit", "Local Currency", "Clearing Document", "Text", "unicode", "Balance as of Date", "CLASSIFICATION", "REMARKS", "ARGL Dated", "Collateral tagging", "Column34", "Column35", "Column36", "Column37"}, {"Account", "Branch account", "Company Code", "Business Area", "Division", "PRODUCT ", "STATUS", "VP", "CM", "ASM", "Debit/Credit ind", "G/L Account", "Assignment", "Reference", "Document type", "Document Date", "Net due date", "Arrears after net due date", "Special G/L ind.", "Amount in local currency", "Difference", "Clearing", "Committed", "Credit limit", "Local Currency", "Clearing Document", "Text", "unicode", "Balance as of Date", "CLASSIFICATION", "REMARKS", "ARGL Dated", "Collateral tagging", "Column34", "Column35", "Column36", "Column37"}),
    #"Filtered Rows1" = Table.SelectRows(#"Expanded Query1", each ([ARGL Dated] = #date(2025, 4, 30)))
in
    #"Filtered Rows1"

But when I tried to filter only the AR 2025 file, and then filter it to 4/30/2025, it can detect the values!

let
    Source = SharePoint.Files("https://XXX.sharepoint.com/sites/XXXX", [ApiVersion = 15]),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Folder Path] = "https://XXX.sharepoint.com/sites/XXXX/Shared Documents/Master Data File/Report Process Manual/Automation Project/AR Management Report/Raw Data/")),
    #"Invoked Custom Function" = Table.AddColumn(#"Filtered Rows", "Query1", each Query1([Content])),
    #"Filtered Rows1" = Table.SelectRows(#"Invoked Custom Function", each ([Name] = "AR 2025.xlsx")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows1",{"Query1"}),
    #"Expanded Query1" = Table.ExpandTableColumn(#"Removed Other Columns", "Query1", {"Account", "Branch account", "Company Code", "Business Area", "Division", "PRODUCT ", "STATUS", "VP", "CM", "ASM", "Debit/Credit ind", "G/L Account", "Assignment", "Reference", "Document type", "Document Date", "Net due date", "Arrears after net due date", "Special G/L ind.", "Amount in local currency", "Difference", "Clearing", "Committed", "Credit limit", "Local Currency", "Clearing Document", "Text", "unicode", " Balance as of Date ", "CLASSIFICATION", "REMARKS", "ARGL Dated", "Collateral tagging"}, {"Account", "Branch account", "Company Code", "Business Area", "Division", "PRODUCT ", "STATUS", "VP", "CM", "ASM", "Debit/Credit ind", "G/L Account", "Assignment", "Reference", "Document type", "Document Date", "Net due date", "Arrears after net due date", "Special G/L ind.", "Amount in local currency", "Difference", "Clearing", "Committed", "Credit limit", "Local Currency", "Clearing Document", "Text", "unicode", " Balance as of Date ", "CLASSIFICATION", "REMARKS", "ARGL Dated", "Collateral tagging"}),
    #"Filtered Rows2" = Table.SelectRows(#"Expanded Query1", each ([ARGL Dated] = #date(2025, 4, 30)))
in
    #"Filtered Rows2"

Here's the custom function that's invoked in both queries.

(ExcelWB) =>
let
    #"Imported Excel Workbook" = Excel.Workbook(ExcelWB),
    Sheet1_Sheet = #"Imported Excel Workbook"{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true])
in
    #"Promoted Headers"

 Hope somebody can help me because this is driving me crazy!

[johnbasha33 · Accepted solution]

Hi @newgirl2 
You're definitely not alone — this is a super common and frustrating issue in Power Query when working with SharePoint folders + Excel workbooks. The key detail that’s likely tripping things up is inconsistent column names due to leading/trailing spaces or subtle data structure differences across files.

Solution

Step 1: Sanitize column names inside your function

Update your custom function to remove leading/trailing spaces from column headers before promotion:

(ExcelWB) =>
let
Source = Excel.Workbook(ExcelWB),
Sheet1 = Source{[Item="Sheet1", Kind="Sheet"]}[Data],
CleanedHeaders = Table.TransformColumns(
Sheet1,
List.Transform(
Table.ColumnNames(Sheet1),
each {_, Text.Trim}
)
),
Promoted = Table.PromoteHeaders(CleanedHeaders, [PromoteAllScalars=true])
in
Promoted

Step 2: Update your main query’s ExpandTableColumn

After sanitizing, update this step so the column name is trimmed:
Table.ExpandTableColumn(..., {"Balance as of Date", ...})
(No more " Balance as of Date " with spaces.)

Extra tip: Check for structural consistency

Even with trimming, some files may have:

• Merged cells

• Blank top rows

• Differently structured sheets

To guard against that:

1. Add a filter to load only files with expected row counts or headers.

Add a step in the function to skip top X rows if needed, or apply a try...otherwise pattern for error handling.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

[newgirl]

Thank you so much @johnbasha33 !! I missed a few column headers when I was comparing the three files. They have some spacing in some 🙃 Thank you so much for taking the time to reply and provide suggestions!!

[v-veshwara-msft]

Hi @newgirl , @newgirl2 ,

Thank you for confirming that the issue was due to extra spaces in some column headers, it’s a subtle but common issue, and we're glad to hear @johnbasha33's response was helpful.

Would you mind marking your reply or @johnbasha33 ’s as Accepted Solution? This will help others in the community who might face a similar challenge find the right guidance more easily.

Thanks for using the Microsoft Fabric Community, and feel free to reach out if you need any further assistance.

Best Regards,
Vinay.

[newgirl]

Hi @v-veshwara-msft! I can't seem to find the button to mark it as solution or maybe I overlooked? 

[v-veshwara-msft]

Hi,

Thanks for following up.

Since the original post was made using @newgirl2 , the option to "Accept as Solution" is only available when you're logged in as @newgirl2 . Please use that account to mark the helpful response as the accepted solution.

Thank you.