PowerM Query extract current month

[Applicable88]

Hello,

I have following table sample:

YearMonth	Turnover
202105	0
202104	3333
202104	3434
202103	32222
202103	4444

Hypothetically the current month is April 2021. Column Year month is not a Date datatype. I connected the YearMonth of this table with a "real" Mastercalendartable. But it gives me way more rows than here, even  tho I did chose left join, which should follow the first tables value, but instead it give me for every date a value as well. I tried inner join as well, still too many rows. 

So now I try to extract the month number and year number into two separate columns (also not date type) like this: 

YearMonth	Turnover	Year	Month
202105	0	2021	05
202104	3333	2021	04
202104	3434	2021	04
202103	32222	2021	03
202103	4444	2021	03

Since the table is much more longer I want a last column which shows me only the turnover data of current year and current month.  Whats the best way to do it? 

Thank you very much in advance.

Best. 

[lbendlin · Accepted solution]

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"

[Ashish_Mathur · Accepted solution]

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

[v-jayw-msft · Accepted solution]

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

[Applicable88]

Hello @v-jayw-msft @Ashish_Mathur @lbendlin ,

thank you very much for the support! All very good approaches, that very well.

You guys proof again that there is many ways to lead to the same outcome.

👍

Best. 

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur · Accepted solution]

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

[lbendlin · Accepted solution]

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"