topic Re: SQL / Power Query recursive - From Startdate to 12 month filled timespan in Developer

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

Do57792 — Tue, 11 Mar 2025 18:25:13 GMT

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.

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

Deku — Tue, 11 Mar 2025 22:09:27 GMT

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"

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

V-yubandi-msft — Fri, 11 Apr 2025 06:32:41 GMT

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.

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

V-yubandi-msft — Mon, 21 Apr 2025 14:33:06 GMT

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.

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

V-yubandi-msft — Fri, 25 Apr 2025 07:33:20 GMT

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.