Forum Discussion

jerryr125's avatar
jerryr125
Helper IV
1 year ago
Solved

Creating Rows -Adding Dates

Hi - I would like to create a row for each month/year from the starting data. Any help would be appreciated!  Jerry   Example:   Table01 - input (starting data):   Project Category 01 Cat...
Need Help
Tips and Tricks
  • BA_Pete's avatar
    BA_Pete
    1 year ago

    Hi jerryr125 ,

     

    speedramps has provided a perfectly good solution already, but I think you'll find this alternative a bit simpler, and it will keep your query a bit cleaner as it's just one step to create the list.

    Use the following formula in a new custom column:

    List.Distinct(
    List.Transform(
        {Number.From(Date.AddMonths([Category Date], -11))..Number.From([Category Date])},
        each Date.StartOfMonth(Date.From(_))
    )
)

     

    Once you expand the resulting list, you get the following output:

     

     

    Pete

BA_Pete's avatar
BA_Pete
Super User
1 year ago

Hi jerryr125 ,

 

speedramps has provided a perfectly good solution already, but I think you'll find this alternative a bit simpler, and it will keep your query a bit cleaner as it's just one step to create the list.

Use the following formula in a new custom column:

List.Distinct(
    List.Transform(
        {Number.From(Date.AddMonths([Category Date], -11))..Number.From([Category Date])},
        each Date.StartOfMonth(Date.From(_))
    )
)

 

Once you expand the resulting list, you get the following output:

 

 

Pete