Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 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 ret...
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. :)

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:

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi Anonymous Thank you for your reply. I have tried your code. Which is returning as List(please see screenshot) 

        but not table data.

        and i got the output as one line like the below text in Blue color
        SELECT * FROM 'TheValueinParameter'.'Function2'();

         

        I tried below code:

        let
    SchemaVersion = SName,
    YourQuery = "SELECT * FROM '"&SName&"'.'Function2'();",
    QueryName = AnalysisServices.Database(CName, SName, [Query=YourQuery])
in
   YourQuery

//Cname: is the parameter for ServerName i have created
//Sname: is the Schema Name that is used in select statement.

        By above code atleas I am able to get the value inside the paramerter Sname. That raises a hope that this is possible some how. Could you please look into this ? Thanks