Forum Discussion
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 | 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"
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
3 Replies
Thank you everyone - appreciate it !
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
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
