How to priodize a payment in Power BI

[Anonymous]

Hello experts. 

Can you please show me a way to periodize a payment that happens on a certain date, but I want the payment to be shown as four payments in the current quarter (in reference to the date) and the next three quarters in Power Query (Power BI). Please see the imiage below for an illustration. 

Thank you for the help.

Best regards,

RR

[wdx223_Daniel]

NewStep= #table({"Project","Quarter","Income"},List.TransformMany(Table.ToRows(PreviousStep),each {0..3},(x,y)=>let a=Date.AddQuarters(x{1},y) in {x{0},Number.ToText(Date.QuarterOfYear(a),"\Q0 ")&DateTime.ToText(a,"yyyy"),if y<3 then x{2}/4 else x{2}-x{2}/4*3}))

[v-jingzhang · Accepted solution]

Hi @Anonymous 

Here is my solution. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PcnBCQAgDEPRXXIWaaN2mdL91zCiGHL5vEw4Gsy7TuNUuGmoluCjOLQU/DRUw7rzUCjmpdo=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, #"Income date" = _t, Income = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", Int64.Type}, {"Income date", type date}, {"Income", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Income Quarter", each List.Generate(
    () => [x = 1, y = [Income date]], 
    each [x] <= 4,
    each [x = [x] + 1, y = Date.AddQuarters([y], 1)],
    each [y] 
)),
    #"Expanded Income Quarter" = Table.ExpandListColumn(#"Added Custom", "Income Quarter"),
    Custom1 = Table.TransformColumns(#"Expanded Income Quarter", {{"Income Quarter", each "Q" & Text.From(Date.QuarterOfYear(_)) & " " & Text.From(Date.Year(_))}}),
    #"Added Custom1" = Table.AddColumn(Custom1, "Quarterly Income", each [Income]/4),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Project", "Income Quarter", "Quarterly Income"})
in
    #"Removed Other Columns"

Steps are:

1. Create a custom column to have a list of 4 dates on every row. 

2. Expand the lists to new rows. 

3. Add a custom step to transform the dates to corresponding quarter year values. 

4. Add a custom column to divide income values by 4. 

5. Remove unnecessary columns.  

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.