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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
DimasArend
New Member

Power Query: Latest date based on a condition

Hi,

 

I am struggling to solve a problem in Power Query. Could someone help me?

 

My table look like this:

DATEXXX
05/01/2021102
04/01/2021

115

04/01/2021102
03/01/2021102
01/01/2021115

 

And i need that return this: The lastest date of xxx if they are in a sequence

DATEXXX
05/01/2021102
04/01/2021

115

01/01/2021115

 

Thanks in advance.

1 ACCEPTED SOLUTION
Payeras_BI
Solution Sage
Solution Sage

Hi @DimasArend,

This is what I understood, let's see if I got it right.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDVNzDUNzIwMlTSUTI0MFKK1QEKmiALGppiEYSpNMYmaIiuPRYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DATE = _t, XXX = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DATE", type date}, {"XXX", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"XXX", Order.Ascending}, {"DATE", Order.Ascending}}),
    AddedIndex = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(AddedIndex, "Custom", each let 
     Check1 = try Duration.Days(AddedIndex[DATE]{[Index]} - AddedIndex[DATE]{[Index]-1}) otherwise 0,
     Check2 = try AddedIndex[XXX]{[Index]} = AddedIndex[XXX]{[Index]-1} otherwise false
     in 
     if Check1 = 1 and Check2 = true then null else [DATE], type date),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"XXX", "Custom"}, {{"DATE", each List.Max([DATE]), type nullable date}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"DATE", "XXX"}),
    #"Sorted Rows1" = Table.Sort(#"Removed Other Columns",{{"DATE", Order.Descending}})
in
    #"Sorted Rows1"

 

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain

View solution in original post

2 REPLIES 2
Payeras_BI
Solution Sage
Solution Sage

Hi @DimasArend,

This is what I understood, let's see if I got it right.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDVNzDUNzIwMlTSUTI0MFKK1QEKmiALGppiEYSpNMYmaIiuPRYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DATE = _t, XXX = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DATE", type date}, {"XXX", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"XXX", Order.Ascending}, {"DATE", Order.Ascending}}),
    AddedIndex = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(AddedIndex, "Custom", each let 
     Check1 = try Duration.Days(AddedIndex[DATE]{[Index]} - AddedIndex[DATE]{[Index]-1}) otherwise 0,
     Check2 = try AddedIndex[XXX]{[Index]} = AddedIndex[XXX]{[Index]-1} otherwise false
     in 
     if Check1 = 1 and Check2 = true then null else [DATE], type date),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"XXX", "Custom"}, {{"DATE", each List.Max([DATE]), type nullable date}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"DATE", "XXX"}),
    #"Sorted Rows1" = Table.Sort(#"Removed Other Columns",{{"DATE", Order.Descending}})
in
    #"Sorted Rows1"

 

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain
KNP
Super User
Super User

I don't fully understand what you're trying to get to.

Why is 115 in your result twice and 102 not?

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.