1

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.

Matt Cottrill
  • 152
  • 1
  • 1
  • 15

1 Answers1

3

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}})

Alejandro Lopez-Lago - MSFT
  • 4,014
  • 20
  • 25
  • 1
    So I did that and it worked as intended to return the max value, but it removed the other columns. How do I get it to display "Column4" that corresponds to "Column1", "Column2" and "MaxColumn" from the previous step in my query? – Matt Cottrill Jul 25 '18 at 14:40
  • 1
    Remove this step to go back to your original query (call it MainTable). Create a new query which references MainTable by right-clicking on MainTable and choosing Reference. Do the Group By on that new query (MaxTable). Create another new query which references MainTable. Then use Merge Tables on the new query and Max Table, using the first two columns as the key to merge on (you can click on the two column headers). Then, with the new table, click on the button in the final column and choose to expand MaxColumn. – Alejandro Lopez-Lago - MSFT Jul 25 '18 at 16:12