Forum Discussion
Group by MIN Date after Threshold
I have a table in this structure:
| Article | Date | Value |
| A | 01.01.2024 | 0,7 |
| A | 01.01.2024 | 0,8 |
| A | 03.01.2024 | 0,9 |
| B | 02.01.2024 | 0,8 |
| B | 06.01.2024 | 0,88 |
Now I would like to get the Date per Article once the 0,9 threshold was reached. If there is no value >= 0,9 then the max date should be taken.
So in the case above
| Article | Date |
| A | 03.01.2024 |
| B | 06.01.2024 |
How is this possible using PQ?
hi joshua1990 ,
you can try this out.
let // Source table Source = YourTableNameHere, // Group by Article GroupedTable = Table.Group(Source, {"Article"}, { {"FilteredRows", each let // Filter rows where Value >= 0.9 Filtered = Table.SelectRows(_, each [Value] >= 0.9), // If there are matching rows, get the MIN Date; otherwise, take the MAX Date Result = if Table.RowCount(Filtered) > 0 then Table.First(Table.Sort(Filtered, {"Date", Order.Ascending}))[Date] else Table.First(Table.Sort(_, {"Date", Order.Descending}))[Date] in Result } }), // Rename and expand the result ExpandedTable = Table.TransformColumns(GroupedTable, {{"FilteredRows", each _, type date}}) in ExpandedTableHi joshua1990, check this:
Output
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIw1AMiIwMjExBHx1wpVgerhAVCwhhFwhIs4QRiG2HRAZYwQ5UAysQCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Article = _t, Date = _t, Value = _t]), ChangedType = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", type number}}), GroupedRows = Table.Group(ChangedType, {"Article"}, {{"Date", each [ a = Table.SelectRows(_, (x)=> x[Value] >= 0.9), b = if Table.IsEmpty(a) then List.Max([Date]) else a{0}[Date] ][b], type date}}) in GroupedRows
7 Replies
Hi ,
Load your table into Power Query, add a Conditional Column (ThresholdMet) to mark rows where Value >= 0.9.
Group by Article, keeping all rows in each group.
For each group, filter rows where ThresholdMet = "Threshold Met", and return the minimum date; if none exist, take the maximum date.
Expand the results to include only Article and the calculated date.
If this post helped please give a kudos and accept this as a solutionhi joshua1990 ,
you can try this out.
let // Source table Source = YourTableNameHere, // Group by Article GroupedTable = Table.Group(Source, {"Article"}, { {"FilteredRows", each let // Filter rows where Value >= 0.9 Filtered = Table.SelectRows(_, each [Value] >= 0.9), // If there are matching rows, get the MIN Date; otherwise, take the MAX Date Result = if Table.RowCount(Filtered) > 0 then Table.First(Table.Sort(Filtered, {"Date", Order.Ascending}))[Date] else Table.First(Table.Sort(_, {"Date", Order.Descending}))[Date] in Result } }), // Rename and expand the result ExpandedTable = Table.TransformColumns(GroupedTable, {{"FilteredRows", each _, type date}}) in ExpandedTableHi joshua1990, check this:
Output
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIw1AMiIwMjExBHx1wpVgerhAVCwhhFwhIs4QRiG2HRAZYwQ5UAysQCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Article = _t, Date = _t, Value = _t]), ChangedType = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", type number}}), GroupedRows = Table.Group(ChangedType, {"Article"}, {{"Date", each [ a = Table.SelectRows(_, (x)=> x[Value] >= 0.9), b = if Table.IsEmpty(a) then List.Max([Date]) else a{0}[Date] ][b], type date}}) in GroupedRowsHi joshua1990
Thank you for reaching out microsoft fabric community forum.I wanted to check if you had the opportunity to review the information provided by dufoq3 . Please feel free to contact us if you have any further questions. If his response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.Hi joshua1990
I wanted to check if you had the opportunity to review the information provided. Please feel free to reach us if you have any further questions. If his response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.NewStep= Table.FromRecords(Table.Group(YourTableName,"Article",{"n",each Table.Sort(_,{each if [Value]=0.9 then 0 else if [Value]<0.9 then 1 else 2,{"Value",1},"Date"}){0}})[n])
Hi joshua1990
I hope the information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.
