Power Query: Filter for lowest value for each entry

[ExcelAthlete]

Hi,

I have a list of documents with Actions associated with it (in Powerquery coming out of a .csv) and days that these actions have been assigned for. It might look something like this:

Document ID	Action by	Days
DOC 001	Andy	3
DOC 001	Mike	7
DOC 001	Jon	10
DOC 001	Andy	16
DOC 002	Helen	5
DOC 002	Andy	5
DOC 002	Sophie	10
DOC 003	Kevin	7
DOC 003	Scott	8
DOC 003	Jon	14
DOC 003	Mark	17

The relevant columns that I would have to sort by are "Document ID" & "Days" and there's more columns similar to the "Action by" one where I just need the data that is in this specific line that I want to pick out. What I would like to end up with is a list that shows me each row filtered for the most recently assigned actions for each document, together with the name (and other columns that I omitted) that they have been assigned to. Some of these will lead to multiple entries for the same document e.g. DOC 002:

Document ID	Action by	Days
DOC 001	Andy	3
DOC 002	Helen	5
DOC 002	Andy	5
DOC 003	Kevin	7

I was able to achieve sort-of what I was looking for with the "group by" function in Power Query by grouping per Document ID with a MIN column for both the "Days" & "Action by" column. Unfortunately this would only show one of the two entries for DOC 002.

Hope I could convey what I'm looking for and appreciate any input!

[wdx223_Daniel · Accepted solution]

[KEO46]

I am trying to use the formual you provided in power query (which I am new too) to filter each row in the table by the value, "Consignment_Event__c" to locate the lowest number for the column "SH#" that is formated as a whole number.

Here is what the table looks like with all the assocated SH# to the Consignment Event in one table.  I need to locate the lowest SH# for each Consignment Event.  

Id	Consignment_Event__c	Order_Recordtype__c	SH Duplicates	SH#
a8H0a00000002xrEAA	a7m0a000002mECuAAM	0120a0000019Ubi	SH-00051238	00051238
a8H0a00000002xsEAA	a7m0a000002mEBwAAM	0120a0000019Ubj	SH-00051239	00051239
a8H0a00000002xtEAA	a7m0a000002mDroAAE	0120a0000019Ubj	SH-00051240	00051240
a8H0a00000002xuEAA	a7m0a000002mBlyAAE	0120a0000019Ubk	SH-00051241	00051241
a8H0a00000002xvEAA	a7m0a000002mBlyAAE	0120a0000019Ubk	SH-00051242	00051242
a8H0a00000002xwEAA	a7m0a000002mDxhAAE	0120a0000019Ubj	SH-00051243	00051243
a8H0a00000002xxEAA	a7m0a000002mDxhAAE	0120a0000019Ubj	SH-00051244	00051244
a8H0a00000002xyEAA	a7m0a000002mE03AAE	0120a0000019Ubj	SH-00051245	00051245

I am getting an error, "Expression.SyntaxError: Invalid Identifier." with the below formula.  Since I am new to Power Query Editor I am not sure where the error is.

[ExcelAthlete]

Thanks!

What I ended up doing in the meantime is create a separate column in Excel, filling it with an "X" using the minifs() function. I will try to implement your solution since it is more elegant doing it in the Query and report back in case I can't get it to work.

[watkinnc]

You could just group by Document ID, and use an All Rows aggregation, name the column "Details", and then add a custom column to get each Table.Min based on the Days column, and then keep just the new column and expand it. So after grouping Document ID:

= Table.AddColumn(PriorStepOrTableName, "NewTable", each Table.Min([Details], "Days"))

Now keep just your NewTable column and expand it.

--Nate

[jgeddes]

You can try something like this...

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvF3VjAwMFTSUXLMS6kEUsZKsTrIwr6Z2alAyhxN2Cs/D0gaGqAJQw0xNEMSNwIKeKTmpII0mKKJQ9WjCwfnF2RkpqJbYAwU8E4ty8xDcw9IPDg5v6QESFugiUPdaYIm7JtYlA0SBxoTCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Document ID" = _t, #"Action by" = _t, Days = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Document ID", type text}, {"Action by", type text}, {"Days", Int64.Type}}),
#"Filtered Table" = Table.AddColumn(#"Changed Type", "MinValue", ((x) => List.Min(Table.SelectRows(#"Changed Type", each [Document ID] = x[Document ID])[Days]))),
#"Added Custom" = Table.AddColumn(#"Filtered Table", "Matches", each if [MinValue] = [Days] then "Matches" else "noMatch"),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Matches] = "Matches")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"MinValue", "Matches"})
in
#"Removed Columns"

It isn't the most elegent, but it gets the job done...