Forum Discussion
Power Query: Filter for lowest value for each entry
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!
5 Replies
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.
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...
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
