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
Nimai123's avatar
Nimai123
Post Patron
6 years ago

Hey Greg_Deckler 

Example

Current month query for August 

 

Start Date "[0CALMONTH].[201908]"

End Date "[0CALMONTH].[202008]"      -----  August - 2020

 

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

 

Required Query to be in September Automatically.

 

Start Date "[0CALMONTH].[201908]"

End Date "[0CALMONTH].[202009]"      -----  September - 2020

 

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

 

ImkeF 

Pragati11 

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
        6 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.