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
CVanpat91
Frequent Visitor

Next date that a value appears - Power Query

Hello,

 

I have a set of data where a certain value appears multiple times.  I want to know the next date that the value appears.  I ned to do this in power query as I need to use this data in a query.  Example below of data.  

NameDate
Blue1/1/2022
Red8/4/2021
Blue5/1/2021
Blue4/2/2021
Green4/1/2021
Green2/3/2021

 

Example of what I need for results: 

NameDateNext Date
Blue1/1/2022null
Red8/4/2021null
Blue5/1/20211/1/2022
Blue4/2/20215/1/2021
Green4/1/2021null
Green2/3/20214/1/2021
1 ACCEPTED SOLUTION

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsopTVXSUTLUN9Q3MjAyUorViVYKSk0BClnom4CEDMFCUGWmEGUoYkBVCDH3otTUPLCgIaagkb4xVDAWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Date", type date}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Name"}, {{"Temp", each Table.AddIndexColumn(_,"Index1",0,1), type table [Name=nullable text, Date=nullable date, Index=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Temp1", each let
    DummyTbl=[Temp],
    Result = Table.AddColumn(DummyTbl, "Next Date", each try DummyTbl[Date]{[Index1]-1} otherwise null)
in
    Result),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Temp"}),
    #"Expanded Temp1" = Table.ExpandTableColumn(#"Removed Columns", "Temp1", {"Date", "Index", "Next Date"}, {"Date", "Index", "Next Date"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Temp1",{{"Index", Order.Ascending}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
    #"Removed Columns1"

 

View solution in original post

3 REPLIES 3
Vijay_A_Verma
Super User
Super User

Can you explain the logic behind next date?

So I need to know the range of which a certain value existed so I can expand that list to be able to filter values on a visual by the date.

Example above:  

NameValueDate
Blue11/1/2022
Red28/4/2021
Blue25/1/2021
Blue34/2/2021
Green14/1/2021
Green22/3/2021

 

 

i need to know what the dates Blue was 1, and dates Blue was 2.  So based on the above date it would tell me that the current value of blue is 1.  Blue was 2 between 5/1/2022, and 1/1/2022.

NameValueDateNext Date
Blue11/1/2022null
Red28/4/2021null
Blue25/1/20211/1/2022
Blue34/2/20215/1/2021
Green14/1/2021null
Green22/3/20214/1/2021

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsopTVXSUTLUN9Q3MjAyUorViVYKSk0BClnom4CEDMFCUGWmEGUoYkBVCDH3otTUPLCgIaagkb4xVDAWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Date", type date}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Name"}, {{"Temp", each Table.AddIndexColumn(_,"Index1",0,1), type table [Name=nullable text, Date=nullable date, Index=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Temp1", each let
    DummyTbl=[Temp],
    Result = Table.AddColumn(DummyTbl, "Next Date", each try DummyTbl[Date]{[Index1]-1} otherwise null)
in
    Result),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Temp"}),
    #"Expanded Temp1" = Table.ExpandTableColumn(#"Removed Columns", "Temp1", {"Date", "Index", "Next Date"}, {"Date", "Index", "Next Date"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Temp1",{{"Index", Order.Ascending}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
    #"Removed Columns1"

 

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.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

Top Solution Authors
Top Kudoed Authors