Add new columns with recent 13 months (EOM key dates) in PBI Editor

[bgonen7]

Hi

1. I am trying to add 13 new columns in a power query to an existed table. 

2. Each column represent end of month (of recent 13 months with key dates 1/31/22, 2/28/22, 3/31/22, 4/30/22  etc)

3. Then I am trying to add the following logic:

[bgonen7]

I love the code. I get the most recent 13 months but I still need to play with the formula because right now I keep getting the result of "1" on all 13 new columns.

Also, I keep getting refresh errors. (I think it got to do with the Date columns).

Overall, I think this is a great solution but I still need to do tests on my end.

Thank you.

[v-jianboli-msft · Accepted solution]

Hi @bgonen7 ,

Please try:

First add a new column [key date] and then expand it:

=List.Generate(()=>[x=Date.EndOfMonth( Date.AddMonths( Date.From(DateTime.LocalNow()),-12)),i=0], each [i]<13, each [i=[i]+1,x=Date.EndOfMonth(Date.AddMonths([x],1))], each [x])

Then add a new column [value]:

=if [key date]>=[Post Date] and [key date]<=[Cleared Date]
then [Amount] else ""

Then Pivot the two columns:

Here is the M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TcxLCsAgDIThu2TtQDLpy2VrbyHe/xpNLUo3gfn5SK1ySpJN41BJmMLtGw4DKS1VuaLkafKbhzd4JyWKryMf+AvmTu5Iy/yywyYhqNLaAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Invoice = _t, Amount = _t, #"Post Date" = _t, #"Cleared Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Invoice", type text}, {"Amount", Int64.Type}, {"Post Date", type date}, {"Cleared Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "key date", each List.Generate(()=>[x=Date.EndOfMonth( Date.AddMonths( Date.From(DateTime.LocalNow()),-12)),i=0], each [i]<13, each [i=[i]+1,x=Date.EndOfMonth(Date.AddMonths([x],1))], each [x])),
    #"Expanded key date" = Table.ExpandListColumn(#"Added Custom", "key date"),
    #"Added Custom1" = Table.AddColumn(#"Expanded key date", "value", each if [key date]>=[Post Date] and [key date]<=[Cleared Date]
then [Amount] else ""),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Added Custom1", {{"key date", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Added Custom1", {{"key date", type text}}, "en-US")[#"key date"]), "key date", "value")
in
    #"Pivoted Column"

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.