Return one row per account number based on Max value of a column

[cathoms]

Good afternoon!

I have a table as follows:

HAR	LineNBR	DRGWt	GMLOS
1	1	1.2795	8.2
2	1	1.2935	5.3
3	1	0.8823	3.6
4	1	0.9735	3.8
4	2	0.9735	3.8
5	1	0.7995	2.9
5	2	0.7995	2.9
6	1	2.5268	5.9
6	2	1.3841	5.62
6	3	1.3841	5.62
6	4	1.3841	5.62
7	1	0.5459	2.5
8	1	1.2197	3.4

I need to filter this so that I have a table with one row per hospital account (HAR) using the max LineNBR for each HAR. That is, I want the following:

HAR	LineNBR	DRGWt	GMLOS
1	1	1.2795	8.2
2	1	1.2935	5.3
3	1	0.8823	3.6
4	2	0.9735	3.8
5	2	0.7995	2.9
6	4	1.3841	5.62
7	1	0.5459	2.5
8	1	1.2197	3.4

What is the best way to accomplish this? I've tried group by and either end up with just one row total for the maximum LineNBR (in this case 4) or one row per line number. My attempted solutions come from the following posts: Solved: Return only rows with max value from specific colu... - Microsoft Fabric Community and Solved: How to filter by max value in power query - Microsoft Fabric Community.

If it helps, in SQL that would look something like this:

SELECT
     HAR,
     DRGWt,
     GMLOS,
     MAX (LineNBR) MaxLine
FROM EDW.Billing.HospitalAccountDRG
GROUP BY
     HAR,
     DRGWt,
     GMLOS

p.s. Ideally, this would be foldable so that I can set up incremental refresh.

[jgeddes · Accepted solution]

You can try this...
Group by HAR, select the row from the resulting nested table that has the max LineNBR and then expand the nested table.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddBBDgQhCATAv3iekLUBgbcY//+N3WEmeHEPJppKh8Y5W2/XcwgW+rs4oa1rNmwIvkGJE/iFD7njfjCNBCkIywSTF+AEWgmLHA6KApxgvAmQYni22pCViV16wkAJ/xU5ilUxFY1nXoLvX+lhuYq0tb4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [HAR = _t, LineNBR = _t, DRGWt = _t, GMLOS = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"HAR", Int64.Type}, {"LineNBR", Int64.Type}, {"DRGWt", type number}, {"GMLOS", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"HAR"}, {{"_nestedTable", each _, type table [HAR=nullable number, LineNBR=nullable number, DRGWt=nullable number, GMLOS=nullable number]}}),
    Custom1 = Table.TransformColumns(#"Grouped Rows", {{"_nestedTable", each Table.SelectRows(_, (x)=> x[LineNBR] = List.Max([LineNBR])), type table [HAR=nullable number, LineNBR=nullable number, DRGWt=nullable number, GMLOS=nullable number]}}),
    #"Expanded _nestedTable" = Table.ExpandTableColumn(Custom1, "_nestedTable", {"LineNBR", "DRGWt", "GMLOS"}, {"LineNBR", "DRGWt", "GMLOS"})
in
    #"Expanded _nestedTable"

[cathoms]

That seems to have worked. Thanks much!