Forum Discussion

Do57792's avatar
Do57792
Advocate I
1 year ago
Solved

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

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. (Ver...
Need Help
  • Deku's avatar
    Deku
    1 year ago

    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"

     

Deku's avatar
Deku
Super User
1 year ago

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"