How to create an if statement with multiple conditions

[IMDM]

Dear forum, 

I need some help with putting together the right if statement in Powerquery. 

I need to put together an if statement that also checks on the period. Kind of like the sums if in excel. So for example: 

When account is 63110 -> I want the total amount after corrections to show the following: 

Total corrections multiplied by -1 + the amount but only for the specific period of 1-1-2025, since other months are also in the file. 

The output should combine the amount of € 142.350,- and € 1.468.310,03 and multiply these by -1 and then adds the amount of € 1.086.681,92.  I also need to put together 2 other calculations but if someone can help me with this one, I should be able to figure out the other one myself. 

Really appreciate the help. 

[V-yubandi-msft]

Hi @IMDM ,

We wanted to check in and see if your issue has been resolved. If so, we’d appreciate it if you could share your solution or workaround and mark the appropriate response as the accepted answer. This helps other community members facing similar challenges find solutions more quickly.

If we don’t hear back, we’ll go ahead and close this thread. Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread. We’ll be happy to help.

Yugandhar – Community Support Team.

[V-yubandi-msft]

Hi @IMDM ,

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-yubandi-msft]

Hi @IMDM ,

Thank you for reaching out to the Fabric community. I have tested your scenario and attached a snapshot of the results based on your requirements. Please review the attachment and confirm whether it meets your expectations or if any adjustments are needed?
FYI:

Regards,

Yugandhar.

[IMDM]

Dear Yugandhar, 

The snapshot only shows the total adjusted correction and it does show the correct amount. Could you please share with me how you got to that result? And has it been done in Powerquery? Since the snapshot is showing a powerbi snapshot? I'm asking this, because I'm currently using Powerquery within Excel, not within Powerbi. I look forward to hearing from you. Thank you for the help!

With kind regards, 

IMDM

[V-yubandi-msft]

Hi @IMDM ,

Thank you for the clarification, sorry for the earlier misunderstanding.  Yes, I had originally done the calculation in Power BI, but I’ve now recreated it in Power Query within Excel following your setup.

I filtered the data for

1. Account = 63110

2. Period = 1-1-2025

Then, I added a custom column using this logic:

Adjusted Total = [Total corrections] * -1 + [Amount]

FYI:

This results in the expected value of -523,978.11, as you described. 

Regards,

Yugandhar.

[IMDM]

Dear Yugandhar, 

Thank you for your reply and my apologies for the late response but this solution won't work. I don't just want to see that data for the month of January, but in case the month is for February I want the custom column to only show the amount for February. So the filter option won't work, I need that filter to be included in the M formula. I have now created it in excel using the sumsif formula, which looks like this: =(IF([@[Nr.]]=63110;(-SUMSIF(H:H;C:C;[@Date];A:A;[@Company])+[@Amount]);[@Amount]))

My apologies if I didn't clarify this enough. I hope you can help me solve this problem. 

With kind regards, 

IMDM

[V-yubandi-msft]

Hi @IMDM ,

Could you share sample data along with the expected output? Having these details will help us address your issue more effectively. Please provide the necessary information here so we can assist you better.

Thank You.

[IMDM]

Dear Yubandi, 

I've put together a sample sheet, which shows what the expected output should be. I think the solution might be to use groups but I haven't worked it out fully yet. 

I can't seem to attach the excel sheet, so I've copied it in as a table: 

GL	Date	Amount	Correction  valuation	Correction revenu	Correctie completion	Total corrections	Amount after correction
61110	1-1-2025	346.509,15	-346.509,15	-	-	-346.509,15	346.509,15
61120	1-1-2025	45.686,11	-45.686,11	-	-	-45.686,11	45.686,11
61130	1-1-2025	145.402,67	-145.402,67	-	-	-145.402,67	145.402,67
61140	1-1-2025	233.314,24	-233.314,24	-	-	-233.314,24	233.314,24
61150	1-1-2025	360.114,15	-360.114,15	-	-	-360.114,15	360.114,15
61160	1-1-2025	-433.099,04	433.099,04	-	-	433.099,04	-433.099,04
61170	1-1-2025	220.502,31	-220.502,31	-	-	-220.502,31	220.502,31
62110	1-1-2025	1.200.973,74	-	-524.734,42	-	-524.734,42	1.200.973,74
63110	1-1-2025	-2.119.403,33	-	-	-	-	-676.239,32
80110	1-1-2025	-	-	-	-	-	-918.429,59
85070	1-1-2025	-	-	-	-	-	-524.734,42
61110	1-2-2025	344.098,80	-344.098,80	-	-	-344.098,80	344.098,80
61120	1-2-2025	56.244,96	-56.244,96	-	-	-56.244,96	56.244,96
61130	1-2-2025	140.001,66	-140.001,66	-	-	-140.001,66	140.001,66
61140	1-2-2025	199.865,83	-199.865,83	-	-	-199.865,83	199.865,83
61150	1-2-2025	378.891,39	-378.891,39	-	-	-378.891,39	378.891,39
61160	1-2-2025	-100.016,19	100.016,19	-	-	100.016,19	-100.016,19
61170	1-2-2025	219.677,39	-219.677,39	-	-	-219.677,39	219.677,39
62110	1-2-2025	-549.101,18	-	-171.202,68	-	-171.202,68	-549.101,18
63110	1-2-2025	-689.662,66	-	-	-	-	2.305.053,86
80110	1-2-2025	1.584.750,00	-	-	-1.584.750,00	-1.584.750,00	-1.238.763,84
85070	1-2-2025	-	-	-	-	-	-171.202,68

thank you!

[V-yubandi-msft · Accepted solution]

Hi @IMDM ,

I've set up the Power Query logic to automatically adjust the Amount after correction based on each period and GL, so there's no need for manual filtering. The query first groups data by GL + Date, calculates the total corrections for that specific period, flips the corrections by multiplying them by -1, and then applies the adjustment to each row. This works similarly to the SUMIFS function in Excel, but now it's fully dynamic within Power Query, ensuring the corrections are applied correctly across multiple periods.

FYI:

Sample Data:

I hope this helps. Feel free to reach out if you need any additional information.

[IMDM]

Thank you very much Yubandi for your continious effort to solve this problem!