PowerM Query extract current month

Tie your "YearMonth"  column value to a fake date, for example the first date of each month.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMjQwVdJRMlCK1YFyTYBcYyBAEzExNkGIGINEjIAAVcgECJRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [YearMonth = _t, Turnover = _t]),
    #"Added Custom" = Table.AddColumn(Source, "YearMonthDate", each #date(Number.From(Text.Start([YearMonth],4)),Number.From(Text.End([YearMonth],2)),1))
in
    #"Added Custom"

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Date", each Date.From(Text.From(Number.ToText([YearMonth])&"01", "en-US"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Turnover", type number}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"YearMonth"})
in
    #"Removed Columns"

Hope this helps

Hi Applicable88 ,

There's no need to use calendar table. You will need to create a custom column to get current yearmonth and compare it with yearmonth column.

Check the following formula.

custom = if [YearMonth] = DateTime.ToText(DateTime.LocalNow(),"yyyyMM") then 1 else 0

Then filter the 0 values and remove the custom column.

The complete m code is as follows.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMjQwVdJRMlCK1YFyTYBcYyBAEzExNkGIGINEjIAAVcgECJRiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [YearMonth = _t, Turnover = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if [YearMonth] = DateTime.ToText(DateTime.LocalNow(),"yyyyMM") then 1 else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom"})
in
    #"Removed Columns"

Best Regards,

Jay