SumIfs function in power query

[kartipan4e]

Need help with a sumifs formula that I am trying to replicate in power query.

Basically in column A i have items, in column B i have a sumifs formula i was able to replicate with power query - =SUMIFS(SCRAP!K:K,SCRAP!E:E,"Account alias",SCRAP!O:O,'Nonpr scrap'!B2), and in column C i need something similar to column B, however i need it to work with a dynamic date - =SUMIFS(SCRAP!K:K,SCRAP!E:E,"Account alias",SCRAP!O:O,'Nonpr scrap'!B2,SCRAP!B:B,"<="&'Nonpr scrap'!$D$1). Basically, D1 is a date and when i change that date the values for the corresponding items below change as well. E:E is the source column in another sheet, O:O are the items, K:K - values and B:B are the dates. So when i put for example 02/25/2025 in D1, the values below show what happened up until then and i can compare with my current values in C:C.

I am having a hard time doing all that in power query and will appreciate any help.

[v-kpoloju-msft · Accepted solution]

Hi @kartipan4e,

Thank you for reaching out to the Microsoft fabric community forum. Thank you @bhanu_gautam for your input on this issue.

It looks like your issue is related to an Excel query, and for the best possible support on this, I would recommend posting your question in the Excel Community. The community there is specifically focused on Excel related issues, and you'll likely get the most accurate solution for your needs.

You can find the Excel Community here: Microsoft Excel Community

Just to clarify, the Microsoft Fabric Community focuses on issues related to Power BI and Microsoft Fabric, so the Excel Community will be the best place for your specific query.

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you for using Microsoft Community Forum.

[v-kpoloju-msft]

Hi @kartipan4e,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-kpoloju-msft]

Hi @kartipan4e,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-kpoloju-msft]

Hi @kartipan4e,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.

Thank you.

[bhanu_gautam]

@kartipan4e 

 Load Data into Power Query
Go to the Data tab in Excel and select Get Data > From Other Sources > From Table/Range.
Load both the SCRAP and Nonpr scrap sheets.
Merge Queries
In Power Query, select the SCRAP query.
Go to the Home tab and select Merge Queries.
Choose the Nonpr scrap query to merge with.
Select the columns to merge on (e.g., Account alias and Item).
Filter by Date
Add a custom column to filter the data based on the date in Nonpr scrap!$D$1.
Go to the Add Column tab and select Custom Column.
Use the following formula to filter by date:
powerquery
if [Date] <= Date.FromText("02/25/2025") then [Value] else null
Replace "02/25/2025" with the reference to the date column from Nonpr scrap.