Forum Discussion

Nimai123's avatar
Nimai123
Post Patron
6 years ago

Automate the M query

My data source is SAP BW and I have 12 years of data in it, so I have to apply the filter and extract the data.   The filter is the starting line of the code.   {Cube.ApplyParameter, "[0I_CALMO]"...
Need Help
v-kelly-msft's avatar
v-kelly-msft
Community Support
6 years ago

Hi Nimai123 ,

 

Create a query as below:

 

  #"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "End date", each Date.AddMonths([Date],13))

 

And you will see:

 

Best Regards,
Kelly
Did I answer your question? Mark my post as a solution!
Nimai123's avatar
Nimai123
Post Patron
6 years ago

Hello v-kelly-msft 

 

I don't want to create a custom column I want the M query to be updated automatically as the new month comes.

 

Regards.

 

Greg_Deckler 

 

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    6 years ago

    Hi  Nimai123 ,

     

    It's  M query created in query editor,see the full query in my example:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMLBQitWJVrKAc2IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Date.AddMonths([Date],13)),
    Custom = #"Added Custom"{0}[Custom]
in
    Custom

    And you will see:

     

     
    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!
    • Nimai123's avatar
      Nimai123
      Post Patron
      5 years ago

      Hello v-kelly-msft 

       

      I understood your solution, but I want to automate the below query

       

      Before

      {Cube.ApplyParameter, "[0I_CALMO]", {"[0CALMONTH].[201908]", "[0CALMONTH].[202008]"}},

       

      After the new month begins

      {Cube.ApplyParameter, "[0I_CALMO]", {"[0CALMONTH].[201908]", "[0CALMONTH].[202009]"}},

       

      I tried using a variable but that was not successful.