Q: Sumif using multiple columns and specific text

[jns1087]

Hi. im new to power query and playing it in excel, i have crossed this idea on how to achieve this in power query. lets say i have 10 columns and i want to sum the amount in column A based on specific text in Column B - F, also B-F have many different unique data.

Col A are amounts 
Col B = apple and kiwi
Col C = dog
Col D = cage
Col E = ordinary
Col F = 123-456

Thanks,

[V-yubandi-msft]

Hi @jns1087 ,
Has your issue been resolved, or are you still experiencing any problems? Your feedback is valuable to the community and can help others facing similar situations. Please let us know if you have any updates.

Thank You.

[V-yubandi-msft]

Hi @jns1087 ,

If you've had a chance to review the attached PBIX file.. If not, please take a look and let me know if you need any additional information or clarification to proceed.

Best regards,

Yugandhar.

[V-yubandi-msft]

Hi @jns1087 ,

Thank you for reaching out to the Fabric Community.
And thanks for sharing the M code, @sanalytics . I tested it on a sample dataset and it worked well the rows filtered for Apple + Kiwi with Dog, Cage, Ordinary, and 123-456 summed up to 345 as expected.

I’ve attached a PBIX file for your reference if you’d like to review it further. Please let me know if you notice any changes that are needed.

I appreciate your support on this, @sanalytics .

Regards,
Yugandhar.

[sanalytics]

@jns1087 
Use the below M code for your result

List.Sum( 
Table.SelectRows( 
Source, each 
(_[Column B] = "APPLE" or _[Column B] ="KIWI") and
_[Column C] = "DOG" and _[Column D] = "CAGE" and
_[Column E] = "ORDINARY" and _[ColumnF] = "123-456"
)[Amount] )

Below screenshot

Hope it helps
Regards,
sanalytics