Forum Discussion
Solved
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 qu...
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"
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 |
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"