Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The 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.

Reply
joshua1990
Post Prodigy
Post Prodigy

Group by MIN Date after Threshold

I have a table in this structure:

ArticleDateValue
A01.01.20240,7
A01.01.20240,8
A03.01.20240,9
B02.01.20240,8
B06.01.20240,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

ArticleDate
A03.01.2024
B06.01.2024

 

How is this possible using PQ?

7 REPLIES 7
v-shamiliv
Community Support
Community Support

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.

 

wdx223_Daniel
Super User
Super User

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])

v-shamiliv
Community Support
Community Support

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.

v-shamiliv
Community Support
Community Support

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.

 

 

dufoq3
Super User
Super User

Hi @joshua1990, check this:

 

Output

dufoq3_0-1737973706430.png

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

Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

rohit1991
Super User
Super User

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

Akash_Varuna
Responsive Resident
Responsive Resident

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Top Solution Authors
Top Kudoed Authors