Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Insert Parameter in M language

Hi,

 

I'm trying to build a Parameterised Query using M Language. I have my variables stored as text fields but when I add them to the final query, it doesnt seem to pick up the string but just returns the variable name. So where I might expect the result of the query, I get the query name.

 

I'm quite new to M, but if I were in VBA, I would change the syntax from "Text" to  "" & variable & "" to enter variable as a string. How do I achieve the same with M?

 

Thanks

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi All,

     

    So after many variations, it turns out it's the same as VBA without spaces. I was building a Paramaeterised Query and wanted a single place to change the value and it update the 6 Queries I have.

     

    The Date value appeared like this "201801", so I created a variable and then managed to insert that using ""&Date&""

     

    All working, thanks for you help. :)

15 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi All,

     

    So after many variations, it turns out it's the same as VBA without spaces. I was building a Paramaeterised Query and wanted a single place to change the value and it update the 6 Queries I have.

     

    The Date value appeared like this "201801", so I created a variable and then managed to insert that using ""&Date&""

     

    All working, thanks for you help. :)

    • tmarton's avatar
      tmarton
      Helper I
      6 years ago

      This worked for me but had to enter as ""&request)), that was the name of my parameter

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Anonymous ImkeF v-jiascu-msft 

      I would like to pass paramter to my queries. So when i change parameter it change my all queries. Can you help me on this? 

      What i have is multiple queries like this:
      SELECT * FROM "Schema1"."Function1"();

      SELECT * FROM "Schema1"."Function2"();
      SELECT * FROM "Schema1"."Function3"();

       

      what I want is all query change like this:

      SELECT * FROM "Schema2"."Function1"();

      SELECT * FROM "Schema2"."Function2"();
      SELECT * FROM "Schema2"."Function3"();

       

      Thanks in advance

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi Kulchandra,

         

        let
    SchemaVersion = ParameterName,
    YourQuery = "SELECT * FROM '"&SchemaVersion&"'.'Function2'();",
    QueryName = AnalysisServices.Database("Server", "Database", [Query=YourQuery, Implementation="2.0"])
in
    #"QueryName"

        I would do something like above. First you need to make a Variable to hold your Query as you wont be able to add the Parameter inside the AnalysisServices Call, named "YourQuery" in the above example. You'll need to make a Parameter to feed in, above I have called that "ParameterName".

         

        I would guess you will need to change your " to ' in your original query, though you will have to test that as I have no test environment. And of course replace the Server & Database fields with your details.

         

        Hopefully that helps. :smileyhappy:

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    7 years ago

    Hi Anonymous,

     

    Can you share the code you have tried? We can use it simply with its name like below.

    Insert_Parameter_in_M_language

     

    Best Regards,

    Dale

  • ImkeF's avatar
    ImkeF
    Community Champion
    7 years ago

    Make sure you don't use escape signs (") when referencing your variables.

    If they have blanks or special characters, you have to write it like so:

     

    #"My Variable"

     

    The # escapes the escapes ;)

     

    So you can either reference your variable so:

     

    MyVariable or #"MyVariable",  but NOT: "MyVariable"

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Anonymous Thank you for your effort to help me to solve this problem for me. I am happy to tell you that finally I found the exact solution for passing parameter into select statement in SAP HANA.

    Here is the simple one line code that worked for me. :)

     

    let
Source=SapHana.Database(CName, [Query="SELECT  * FROM "&SName&".""FN_PowerBI_SampleFunctionName""();"])
in
Source

    Thanks, Kulchandra Neupane