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

speedramps's avatar
speedramps
Super User
1 year ago

Try this ....

 

In  Power Query add a custom columm

List.Numbers(1,12)

 

Expand the list

 

Add a another custom columm

Text.From(
Text.PadStart(Text.From([Custom]), 2, "0")
)
&
"-"
& 
Text.From(
Date.Year([Category Date])
)

 

 

Remove the old and temporary custom column and rename the new column to Category Date

 

 

Please click thumbs up and accept solution.

Thanks