none
Generate a list in Power Query for each index value RRS feed

  • Question

  • Hi,

    I have a simple table in Power Query.

    It contains following columns:

    - index (unique key for each table entry)

    - revenue

    - forecastIntervalMonths

    - forecastStartDate

    - forecastEndDate

    From this, I need to generate a list of a forecasted revenue stream for each "index" value that starts at "forecastStartDate" and ends at or before "forecastEndDate". Revenue stream recurs at each interval "forecastIntervalMonths" which is given in number of months. For example, if it is 3, it means that revenue repeats every three months. Revenue should equal "revenue" amount at each interval. Results should be included in one table.

    I would highly appreciate if someone could share an idea how this could be accomplished.

    Thanks,

    Rolands


    Monday, November 27, 2017 10:15 PM

Answers

  • Maybe a question with a representative example would be a better idea than posting your question twice.

    See my answer in the link.

    Tuesday, November 28, 2017 5:52 AM
  • Hi Bill :-),

    Well it looks easier indeed.

    To be honest, I tend to avoid List.Generate, and only use it if it is not possible to determine in advance how many iterations will be required. In this case it is possible (though with some calculations).
    I don't mean to state that List.Generate should be avoided, but that's the reason why I avoid it (and that may be no good reason at all).

    Otherwise you overlooked the multiplication of i with the forecastIntervalMonths, so your version is a bit too easy ;-).

    For completeness, this was the solution I posted on the Power BI Community forum:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"index", Int64.Type}, {"revenue", Int64.Type}, {"forecastIntervalMonths", Int64.Type}, {"forecastStartDate", type date}, {"forecastEndDate", type date}}),
        AddedMonths = Table.AddColumn(#"Changed Type", "Months", each 12 * (Date.Year([forecastEndDate])-Date.Year([forecastStartDate])) + Date.Month([forecastEndDate]) - Date.Month([forecastStartDate]) - (if Date.Day([forecastStartDate]) > Date.Day([forecastEndDate]) then 1 else 0), Int64.Type),
        AddedDateList = Table.AddColumn(AddedMonths, "Dates", (ThisRow) => List.Transform({0..Number.IntegerDivide(ThisRow[Months],ThisRow[forecastIntervalMonths])}, each Date.AddMonths(ThisRow[forecastStartDate],_ * ThisRow[forecastIntervalMonths])), type {date}),
        RemovedMonths = Table.RemoveColumns(AddedDateList,{"Months"})
    in
        RemovedMonths



    Wednesday, November 29, 2017 6:54 PM
  • Thanks for reply, Marcel.
    To be clear, I'm not against your solution. My solution is an alternative only.
    I rarely use List.Generate  (and recursion) but in this case I think that it has no effect on performance.
    forecastIntervalMonths is not a problem :-))

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"index", Int64.Type}, {"revenue", Int64.Type}, {"forecastIntervalMonths", Int64.Type}, {"forecastStartDate", type date}, {"forecastEndDate", type date}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "ListOfDates", each 
               List.Generate(()=> 
                     [Start = [forecastStartDate], End = [forecastEndDate], ToList = Start, i = 1, Interval = [forecastIntervalMonths]] , 
                     each [ToList] <= [End], 
                     each [Start = [Start], End = [End], ToList = Date.AddMonths([Start], [i]*[Interval]), i = [i]+1, Interval = [Interval]], 
                     each [ToList]))
    in
        #"Added Custom"

    Cheers :-))

    Thursday, November 30, 2017 11:07 AM

All replies

  • Maybe a question with a representative example would be a better idea than posting your question twice.

    See my answer in the link.

    Tuesday, November 28, 2017 5:52 AM
  • Hi Marcel ;-)

    Do not you think that in this case using List.Generate would be easier?

    = Table.AddColumn(#"Changed Type", "ListOfDates", each 
            List.Generate(()=> 
               [Start = [forecastStartDate], End = [forecastEndDate], ToList = Start, i = 1] , 
                each [ToList] < [End], 
                each [Start = [Start], End = [End], ToList = Date.AddMonths([Start], [i]), i = [i]+1], 
                each [ToList]))

    Wednesday, November 29, 2017 2:04 PM
  • Hi Bill :-),

    Well it looks easier indeed.

    To be honest, I tend to avoid List.Generate, and only use it if it is not possible to determine in advance how many iterations will be required. In this case it is possible (though with some calculations).
    I don't mean to state that List.Generate should be avoided, but that's the reason why I avoid it (and that may be no good reason at all).

    Otherwise you overlooked the multiplication of i with the forecastIntervalMonths, so your version is a bit too easy ;-).

    For completeness, this was the solution I posted on the Power BI Community forum:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"index", Int64.Type}, {"revenue", Int64.Type}, {"forecastIntervalMonths", Int64.Type}, {"forecastStartDate", type date}, {"forecastEndDate", type date}}),
        AddedMonths = Table.AddColumn(#"Changed Type", "Months", each 12 * (Date.Year([forecastEndDate])-Date.Year([forecastStartDate])) + Date.Month([forecastEndDate]) - Date.Month([forecastStartDate]) - (if Date.Day([forecastStartDate]) > Date.Day([forecastEndDate]) then 1 else 0), Int64.Type),
        AddedDateList = Table.AddColumn(AddedMonths, "Dates", (ThisRow) => List.Transform({0..Number.IntegerDivide(ThisRow[Months],ThisRow[forecastIntervalMonths])}, each Date.AddMonths(ThisRow[forecastStartDate],_ * ThisRow[forecastIntervalMonths])), type {date}),
        RemovedMonths = Table.RemoveColumns(AddedDateList,{"Months"})
    in
        RemovedMonths



    Wednesday, November 29, 2017 6:54 PM
  • Thanks for reply, Marcel.
    To be clear, I'm not against your solution. My solution is an alternative only.
    I rarely use List.Generate  (and recursion) but in this case I think that it has no effect on performance.
    forecastIntervalMonths is not a problem :-))

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"index", Int64.Type}, {"revenue", Int64.Type}, {"forecastIntervalMonths", Int64.Type}, {"forecastStartDate", type date}, {"forecastEndDate", type date}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "ListOfDates", each 
               List.Generate(()=> 
                     [Start = [forecastStartDate], End = [forecastEndDate], ToList = Start, i = 1, Interval = [forecastIntervalMonths]] , 
                     each [ToList] <= [End], 
                     each [Start = [Start], End = [End], ToList = Date.AddMonths([Start], [i]*[Interval]), i = [i]+1, Interval = [Interval]], 
                     each [ToList]))
    in
        #"Added Custom"

    Cheers :-))

    Thursday, November 30, 2017 11:07 AM
  • Thanks Bill.

    Your solution also works exactly as needed. And the code is somewhat shorter indeed.


    Thursday, November 30, 2017 11:34 AM