Forum Discussion

TimothyBohte's avatar
TimothyBohte
Frequent Visitor
2 years ago
Solved

Create revenue records from salesorder

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

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

2 Replies

  • Omid_Motamedise's avatar
    Omid_Motamedise
    Icon for Super User rankSuper User
    2 years ago

    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
	)
  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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