Forum Discussion

Applicable88's avatar
Applicable88
Impactful Individual
5 years ago
Solved

PowerM Query extract current month

Hello,

I have following table sample:

YearMonthTurnover
2021050
2021043333
2021043434
20210332222
2021034444

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: 

YearMonthTurnoverYearMonth
2021050202105
2021043333202104
2021043434202104
20210332222202103
2021034444202103

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. 

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • lbendlin's avatar
    lbendlin
    5 years ago

    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's avatar
    Ashish_Mathur
    5 years ago

    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

  • Anonymous's avatar
    Anonymous
    5 years ago

    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

5 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

    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's avatar
    Ashish_Mathur
    Super User
    5 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      Applicable88
      Impactful Individual
      5 years ago

      Hello Anonymous 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.