Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
emredici
Regular Visitor

Replicate SUMIF in Power Query

Hi all,

 

I have the below table in Power BI Power query.

I wanted to simplify the subject and paste the Excel copy of it instead.

 

The columns with blue titles are my available data. I want to generate the Green column in Power query.

emredici_2-1656945021631.png

It's so simple in excel. As shown below. 

emredici_3-1656945085508.png

How can I do the same operation in Power BI? I would like to return each sumif figure on each row.

 

Ultimately, my goal is to add a new column and generate a calculated copy of the highligted column in PBI power query.

emredici_4-1656945378582.png

Thank you.

 

2 ACCEPTED SOLUTIONS
ronrsnfld
Super User
Super User

  • Group by Activity ID
  • Aggregate by
    • All Rows
    • Sum of Working Day Count
  • Expand the "All Rows" aggregation

 

View solution in original post

It does not appear that you are in the Query Editor (Power Query).

Select `Home=>Transform`

Then you will find the GroupBy option and, when the dialog box opens, select Advanced to be able to add multiple aggregations/operations

View solution in original post

4 REPLIES 4
emredici
Regular Visitor

Hi @ronrsnfld,

Thanks for the response.

I grouped the data by the Activity ID.

emredici_1-1656959067506.png

I aggregated all with sum of working day count. However, I couldn't see anything regarding "all rows"

emredici_2-1656959148581.png

The end result is this. I can't aggregate or select All Rows.

emredici_4-1656959312028.png

 

 

 

It does not appear that you are in the Query Editor (Power Query).

Select `Home=>Transform`

Then you will find the GroupBy option and, when the dialog box opens, select Advanced to be able to add multiple aggregations/operations

Thank you! It is resolved. You were extremely fast 😄
This forum is lucky to have someone like you.

ronrsnfld
Super User
Super User

  • Group by Activity ID
  • Aggregate by
    • All Rows
    • Sum of Working Day Count
  • Expand the "All Rows" aggregation

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors