Creating Rows -Adding Dates

[jerryr125]

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	Category 02	Category Date
A	20	8	12-2025
B	36	24	12-2025
C	60	48	12-2025

Final Output:

Project	Category 01	Category 02	Category Date
A	20	8	12-2025
A	20	8	11-2025
A	20	8	10-2025
A	20	8	09-2025
A	20	8	08-2025
A	20	8	07-2025
A	20	8	06-2025
A	20	8	05-2025
A	20	8	04-2025
A	20	8	03-2025
A	20	8	02-2025
A	20	8	01-2025
B	36	24	12-2025
B	36	24	11-2025
B	36	24	10-2025
B	36	24	09-2025
B	36	24	08-2025
B	36	24	07-2025
B	36	24	06-2025
B	36	24	05-2025
B	36	24	04-2025
B	36	24	03-2025
B	36	24	02-2025
B	36	24	01-2025
C	60	48	12-2025
C	60	48	11-2025
C	60	48	10-2025
C	60	48	09-2025
C	60	48	08-2025
C	60	48	07-2025
C	60	48	06-2025
C	60	48	05-2025
C	60	48	04-2025
C	60	48	03-2025
C	60	48	02-2025
C	60	48	01-2025

Current Power Query Structure:

Current Advance Editor:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Category 01", Int64.Type},
{"Category 02", Int64.Type}, {"Category Date", type date}})
in
#"Changed Type"

[jerryr125]

 Thank you everyone  - appreciate it !

[BA_Pete · Accepted solution]

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]

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