CODEWITHSUNDEEP
sqlexcelpowerquery
Matt Cottrill
Matt Cottrill

Reputation: 148

PowerQuery Conditional Max

I'm trying to use Power Query with this table: enter image description here

My goal is to find the max value in Column 3 "GRPEFFDTE" for each Column 1 & Column 2. If this was normal excel I would use:

=+IF(MAXIFS(C:C,B:B,B5,A:A,A5)=C5,1,0)

To make it like the ISMAX column here: enter image description here

I think I need to use an if statement with a table.max, but I have no familiarity with Power Query. If there is a better way than what I'm describing above, that can be a calculated field or a column in the power query, please advise!

I will update as I get rolling, I really have no idea where to start.

EDIT 1: The comment from below got me started, but as my comment indicates, I need to get the GRPEFFPRC that corresponds to my other 3 columns, and I'm not sure how to do it.

EDIT 2: See comments on accepted answer for full explanation.

Upvotes: 1

Views: 16517

Answers (1)

Alejandro Lopez-Lago - MSFT
Alejandro Lopez-Lago - MSFT

Reputation: 4134

If you just want the maximum, then you can do this with a Group By. Group By is an operation that groups rows together based on shared column values and then performs an operation on that group of rows (like count, min, max, etc.). If you want to do this in Power Query, you can do the following:

  1. Click on Group By in the Home ribbon in the Power Query Editor.
  2. Click on "Advanced"
  3. Click on "Add grouping". The two dropdowns should be the names of your first two columns.
  4. For the Operation, choose "Max", and for the Column choose the name of your third column. You can choose whatever you want for the new column name (let's call it MaxColumn).

The result should be a table where MaxColumn is the maximum value of the third column for each pair of values in the first two columns.

Relevant Power Query formula:

= Table.Group(TableStep, {"Column1", "Column2"}, {{"MaxColumn", each List.Max([Column3]), type number}})

Upvotes: 3

Related Questions