Forum Discussion

jns1087's avatar
jns1087
Regular Visitor
1 year ago
Solved

Q: Sumif using multiple columns and specific text

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,

Need Help
  • sanalytics's avatar
    sanalytics
    1 year ago

    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

  • V-yubandi-msft's avatar
    V-yubandi-msft
    1 year ago

    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.

4 Replies

  • sanalytics's avatar
    sanalytics
    Super User
    1 year ago

    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

  • V-yubandi-msft's avatar
    V-yubandi-msft
    Community Support
    1 year ago

    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.

  • V-yubandi-msft's avatar
    V-yubandi-msft
    Community Support
    1 year ago

    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's avatar
    V-yubandi-msft
    Community Support
    1 year ago

    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.