Create revenue records from salesorder

[TimothyBohte]

I have a salesorder table with StartDate, EndDate and Interval in Power BI and I would like to create revenue records by PowerQuery. Revenue records should be created per salesorder, from the StartDate to the EndDate with an Interval of 1, 3 or 12 months. These records should have a RevenueDate and salesorderid (=GUID). The following Power Query M formula gives Expression.Error: We cannot apply field access to the type Date.
Details:
Value=01/12/2024
Key=EndDate

The first record of salesorder has 01/12/2024 as StartDate. Both StartDate and EndDate have the Date Type and I filtered out the salesorder records that have a blank StartDate or EndDate just te be sure. I think maybe it has something to do with trying to get the salesorder column values from the list correctly. But I can't figure out how to make it right.

How can I solve this?

let
    Source = salesorder,
	
    AddRevenueDates = Table.AddColumn(Source, "RevenueDates", each 
	List.Generate( 
		() =>     [StartDate], // Starting value
		each _ <=  [EndDate], // Create only when <= EndDate
		each Date.AddMonths( _, [Interval] ) // Take steps of x months
	)   
	),
    ExpandRevenueDates = Table.ExpandListColumn(AddRevenueDates, "RevenueDates"),
    RenameColumns = Table.RenameColumns(ExpandRevenueDates, {{"RevenueDates", "RevenueDate"}}),
    SelectColumns = Table.SelectColumns(RenameColumns, {"RevenueDate", "salesorderid"})
in
    SelectColumns

[Anonymous · Accepted solution]

Hi @TimothyBohte ,
Thanks for Omid_Motamedise reply.
Based on your description, the problem may be in how the List.Generate function accesses the StartDate and EndDate fields. When using List.Generate, you need to make sure that these fields are referenced correctly in the context of each row. You can try the following code to see if it resolves the error

let
    Source = salesorder,
    AddRevenueDates = Table.AddColumn(Source, "RevenueDates", each 
        let
            startDate = [StartDate],
            endDate = [EndDate],
            interval = [Interval]
        in
        List.Generate(
            () => startDate,
            each _ <= endDate,
            each Date.AddMonths(_, interval)
        )
    ),
    ExpandRevenueDates = Table.ExpandListColumn(AddRevenueDates, "RevenueDates"),
    RenameColumns = Table.RenameColumns(ExpandRevenueDates, {{"RevenueDates", "RevenueDate"}}),
    SelectColumns = Table.SelectColumns(RenameColumns, {"RevenueDate", "salesorderid"})
in
    SelectColumns

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Omid_Motamedise]

replace the part of formula by the below code

	List.Generate( 
		() =>     [StartDate], // Starting value
		(x)=>  x<=  [EndDate], // Create only when <= EndDate
		(x)=> Date.AddMonths( x, [Interval] ) // Take steps of x months
	)