As noted, it's a little ambiguous as to what you want. What if there are multiple later dates in the other table? When you say "increment", do you really mean adding a single date, or do you mean adding all dates up to the latest in the other table? To give you an idea, I walked through a few different options.
Given the starting table that we plan to increment and the other table we want to check against...
Increment:
| Date | Column A | Column B |
| 1/1/2024 | a | 1 |
| 3/1/2024 | b | 2 |
| 6/1/2024 | c | 3 |
Check:
| Date | Column C | Column D |
| 1/1/2024 | a1 | 20 |
| 3/1/2024 | b1 | 30 |
| 6/1/2024 | c1 | 12 |
| 6/3/2024 | d1 | 10 |
| 6/10/2024 | e1 | 60 |
Here is some M that figures out potential different lists of dates you may want to consider the "increment" to add and converts them into Increment table rows (as a table) that you can easily add to Increment (i.e. with Table.Combine)
let
//A function that converts list of dates into same dates in Increment table schema.
//If input is an empty list, we just get an empty table as output.
DatesToIncrRows =
(dates as list) as table =>
Table.FromRecords(
List.Transform( dates, each [Date=_] ),
Value.Type( Increment ),
MissingField.UseNull
),
//Get max date from Increment.
MaxFromIncr = List.Max( Increment[Date] ),
//Get ALL dates in other table later than Increment max.
//Will be empty list if no later dates.
AllLaterFromCheck =
List.Select( Check[Date], each _ > MaxFromIncr ),
//Get max date out of dates later than Increment max.
//Will be null if AllLaterFromCheck is empty list {}.
MaxFromLater = List.Max( AllLaterFromCheck ),
//If a MaxFromLater exists, wrap in list to work with our function.
//Otherwise, return empty list {}.
MaxToList =
if MaxFromLater = null then {} else { MaxFromLater },
//Get all dates between Increment max (exclusive) and Check max (inclusive).
//Or return empty list if no later dates exist in Check.
AllBetweenIncrAndCheck =
if MaxFromLater = null then {} else List.Dates(
Date.AddDays( MaxFromIncr, 1 ),
Duration.Days( List.First(MaxToList) - MaxFromIncr ),
#duration(1,0,0,0)
),
//Sticking different options into a record for easy access.
//Use this in other queries with reference like: IncrementOptions[All Later Dates From Check].
Options = [
Only Latest Date In Check = DatesToIncrRows( MaxToList ),
All Later Dates From Check = DatesToIncrRows( AllLaterFromCheck ),
All Later Dates Between Check And Incr = DatesToIncrRows( AllBetweenIncrAndCheck )
]
in
Options
Output:
And to be clear, the idea would be to union the desired output with your original Increment using Table.Combine to get the final table. And note that Table.Combine( { Whatever Table, Empty Table } ) = Whatever Table - i.e. it handles the case where there are no later dates in Check. Example where the above query is called IncrementOptions:
= Table.Combine( { Increment, IncrementOptions[All Later Dates Between Check And Incr] } )
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Join our Community Sticker Challenge 2025
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Fabric Community Update - February 2025
Find out what's new and trending in the Fabric community.
