Forum Discussion
sumifs doesnt work because of power query
Power Query outputs data as Excel Tables, and these behave differently from standard ranges:
• Columns may appear formatted as dates, but Excel formulas might not recognize them properly.
• Wildcards like "DTC*" can misbehave if the column isn't truly text.
• Dynamic references like SCRAP!$B:$B may not work as expected on structured tables.
Below are some workarounds that would be suggested:
1. Convert Dates Explicitly in Power Query
Even if the column looks like a date, force it:
• In Power Query Editor, select the column → right-click → Change Type → Date.
• Then add a new column that extracts the date value:
= Date.From([YourDateColumn])
2. Use Structured References Instead of Full Column Ranges
Instead of SCRAP!$K:$K, use structured table references:
=SUMIFS(Table1[Value], Table1[Items], "DTC*", Table1[Source], "Job or Schedule", Table1[Date], "<="&P5)
Make sure Table1[Date] is truly a date column.
3. Use a Helper Column for Date Comparison
In Power Query, add a column like:
= if [Date] <= Date.From(P5) then [Value] else null
Then sum that column in Excel:
=SUM(Table1[FilteredValue])
4. Consider Doing the Aggregation in Power Query
If performance is an issue or formulas keep breaking, you can replicate SUMIFS logic directly in Power Query using Group By and filters:
• Filter rows where Items starts with "DTC" and Source is "Job or Schedule".
• Filter by date using Date.IsInPreviousNMonths or custom logic.
• Group and sum the Value column.