Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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
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.
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 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.
Hi @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, 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
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
ExpandedTable
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 solution