Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Tdl1982
Regular Visitor

Return / Lookup next date in a column of Dates

Hi all, 

 

I am new to Power BI and I am trying to create a new column based on date information in another (in Power Query). 

 

I effectively want my next column to display the NEXT date in the column it is referencing. If there is no next date then return TODAYs date. 

Current: 

Tdl1982_0-1627359383507.png

Goal output:

Tdl1982_1-1627359441209.png

 

I have been playing aroud with Dates and List.Max and List.Remove but not getting the output I am looking for. 

 

Any help is greatly appreciated. 

 

Thank you.



 

1 ACCEPTED SOLUTION
Vera_33
Resident Rockstar
Resident Rockstar

Hi @Tdl1982 

 

You are only looking at dates regardless of location, here is one way. Please provide sample data in a format which people can copy in the future

Vera_33_0-1627362929759.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjA0NLJR0lR6VYnWglQ1MMISNLJCEnpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Paid = _t, Where = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Paid", type date}, {"Where", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each try #"Added Index"[Paid]{[Index]+1} otherwise Date.From( DateTime.LocalNow()))
in
    #"Added Custom"

 

View solution in original post

3 REPLIES 3
Syndicate_Admin
Administrator
Administrator

Hi @Tdl1982 

 

You are only looking at dates regardless of location, here is one way. Please provide sample data in a format which people can copy in the future

Vera_33_0-1627362929759.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjA0NLJR0lR6VYnWglQ1MMISNLJCEnpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Paid = _t, Where = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Paid", type date}, {"Where", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each try #"Added Index"[Paid]{[Index]+1} otherwise Date.From( DateTime.LocalNow()))
in
    #"Added Custom"

 

Tdl1982
Regular Visitor

Hello Vera_33, Thank you for this. Since I posted I worked out using two index tables and then merging together. Your way is even more efficient again. Thank you! 

Vera_33
Resident Rockstar
Resident Rockstar

Hi @Tdl1982 

 

You are only looking at dates regardless of location, here is one way. Please provide sample data in a format which people can copy in the future

Vera_33_0-1627362929759.png

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjA0NLJR0lR6VYnWglQ1MMISNLJCEnpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Paid = _t, Where = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Paid", type date}, {"Where", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each try #"Added Index"[Paid]{[Index]+1} otherwise Date.From( DateTime.LocalNow()))
in
    #"Added Custom"

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors