Forum Discussion
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.
| 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 |
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"
3 Replies
- Vijay_A_Verma
Most Valuable Professional
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: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
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"
