Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
Solved! Go to 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"
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"
Check out the July 2025 Power BI update to learn about new features.