Custom Column: End of Month data and current date data

Hi crln-blue 

You can add today's date to the front of that Month List like this

List.Combine({{DateTime.Date(DateTime.LocalNow())},#"Added Custom"[End of Month]})

This will give you a list as a result. 

The full example query is

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VcnJCQAgDATAXnwLZuNdS0j/bUhAYX0OY5ZQIEVFJXkObcZiTMZgdEZjVIYy8CEK+wlXfgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "End of Month", each Date.EndOfMonth([Month])),
    Months = List.Combine({{DateTime.Date(DateTime.LocalNow())},#"Added Custom"[End of Month]})
in
    Months

You can add a column to the table like so 

= Table.FromColumns({{Table.ToColumns(#"Added Custom")},Months})

But you are inserting a new row, which is today's date, so the first row in your table will end up with nulls or a list.  If you could supply your data it might be easier to explain and implement.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VcnJCQAgDATAXnwLZuNdS0j/bUhAYX0OY5ZQIEVFJXkObcZiTMZgdEZjVIYy8CEK+wlXfgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Month = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "End of Month", each Date.EndOfMonth([Month])),
    Months = List.Combine({{DateTime.Date(DateTime.LocalNow())},#"Added Custom"[End of Month]}),
    NewCol = Table.FromColumns({{Table.ToColumns(#"Added Custom")},Months})
in
    NewCol

But here's the full code and a sample PBIX

Regards

Phil

Hi crln-blue ,

Followed by your previous query, the table is like this:

Combine current date and the Custom date to list and transform to table, the whole query is like this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjRSitUBUoYQygBMWYJJCzBpDibNwKQpmDQBk8ZgEqpbKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Month List" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month List", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Date.EndOfMonth(
    Date.AddMonths(
        Date.From(DateTime.LocalNow()),
        0 - 13 + [Month List]
    )
)),
    Custom1 = List.Combine({{DateTime.Date(DateTime.LocalNow())},#"Added Custom"[Custom]}),
    #"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

Attached a sample file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.