SQL / Power Query recursive - From Startdate to 12 month filled timespan

[Do57792]

Hello experts !

I hope this is at the right part of the forum.

I have a table with project costs. The projects are clearly numbered. There is also a column with the start date and costs. (Very simplified). The costs are distributed over 12 months starting from the start date. In some cases the costs differ and are not total costs divided by 12.

I would like to have an overview table that shows the projects, the months and the amount for the month to make it easier in Power Bi and not have to map everything via Dax:

Origin data:

Project	Startdate	Costs
1	01 Jan 2025	120
2	07 Sep 2020	12
3	09 Oct 2021	60

Target:

Project	Date	Partly Costs
1	Jan 2025	10
1	Feb 2025	10
1	Mar 2025	10
1	...	10
1	Dec 2025	10
2	Sep 2020	1
2	Oct2020	1
2	...	1
2	Aug 2020	1
...	...	...

The first idea was to map this using SQL and CTE.
Unfortunately I failed here. Is CTE supported at all? Within the interval, the plan was to use case queries to change the costs per property of the projects if necessary. I didn't get that far, because not even the body outputs the values accordingly.

WITH RECURSIVE months as (
    -- Basis: Startmonat (Monat 0)
    SELECT 

        Project,
        Costs,
        Startdate AS month_date,
        Costs/ 12.0 AS monthly_costs,
        0 AS month_number
    FROM Projects
    
    UNION ALL
    
    SELECT 
        Project,
        Costs,
        DATEADD(month,  1, Startdate) AS month_date,
        monthly_costs,
        month_number + 1
    FROM months
    WHERE month_number < 11 
)



SELECT 
    Project,
    month_date AS 'Month',
    monthly_costs AS 'Monthly Costs'
FROM months
ORDER BY Project, month_date

Is it necessary and also possible to map something like this via Power Query and if so, is there a corresponding guideline?

Many thanks in advance for any help.

[V-yubandi-msft]

Hi @Do57792 ,

We noticed we haven't received a response from you yet, so we wanted to follow up and ensure the solution we provided addressed your issue. If you require any further assistance or have additional questions, please let us know.

Your feedback is valuable to us, and we look forward to hearing from you soon.

[V-yubandi-msft]

Hi @Do57792 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[V-yubandi-msft]

Hi @Do57792 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[Deku · Accepted solution]

On the CTE part, what SQL engine are you using? that is going to determine if recursion is supported.

If you want to use powerquery

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIwVPBKzFMwMjAyBfIMjQyUYnWilYxAMuYKwakFIBkDsAxYwhgkYangn1wCkgAZYAbUEQsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, Startdate = _t, Costs = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", Int64.Type}, {"Startdate", type date}, {"Costs", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "months", each 
List.Generate(() => 0, each _ < 12, each _ + 1)),
    #"Expanded 12 months" = Table.ExpandListColumn(#"Added Custom", "months"),
    #"Added Custom1" = Table.AddColumn(#"Expanded 12 months", "Dates", each Date.AddMonths([Startdate], [months])),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Partly Costs", each [Costs] / 12),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Costs", "months"})
in
    #"Removed Columns"