Next date that a value appears - Power Query

[CVanpat91]

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.  

Name	Date
Blue	1/1/2022
Red	8/4/2021
Blue	5/1/2021
Blue	4/2/2021
Green	4/1/2021
Green	2/3/2021

Example of what I need for results: 

Name	Date	Next Date
Blue	1/1/2022	null
Red	8/4/2021	null
Blue	5/1/2021	1/1/2022
Blue	4/2/2021	5/1/2021
Green	4/1/2021	null
Green	2/3/2021	4/1/2021

[Vijay_A_Verma]

Can you explain the logic behind next date?

[CVanpat91]

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:  

Name	Value	Date
Blue	1	1/1/2022
Red	2	8/4/2021
Blue	2	5/1/2021
Blue	3	4/2/2021
Green	1	4/1/2021
Green	2	2/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.

Name	Value	Date	Next Date
Blue	1	1/1/2022	null
Red	2	8/4/2021	null
Blue	2	5/1/2021	1/1/2022
Blue	3	4/2/2021	5/1/2021
Green	1	4/1/2021	null
Green	2	2/3/2021	4/1/2021

[Vijay_A_Verma · 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"