cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

15 REPLIES 15
Anonymous
Not applicable

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

 

Anonymous
Not applicable

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

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

Anonymous
Not applicable

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
Not applicable

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. Smiley Happy

Anonymous
Not applicable

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

Text line.png

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

 

Anonymous
Not applicable

 

 

let
    SchemaVersion = SName,
    YourQuery = "SELECT * FROM '"&SName&"'.'Function2'();",
    QueryName = AnalysisServices.Database(CName, SName, [Query=YourQuery])
in
   YourQuery << Should be QueryName
//Cname: is the parameter for ServerName i have created
//Sname: is the Schema Name that is used in select statement.

So in your above code, you have YourQuery as the IN, it should be the last step, QueryName

Anonymous
Not applicable

@Anonymous Almost there!. Code works perfectly only one thing need to fix, which i am unable to figure out. Please see the screenshot below. I have marked up everything. This code is appending the server name with SName as well. I tried to remove the SName variable from 

QueryName = AnalysisServices.Database(CName, SName, [Query=YourQuery])

But it leads to error. Could you please look into below image:

Schema and server change.jpg

Thanks, Kulchandra

The image you've posted is not an error-message, but a warning. If you click "Run", you should either get the desired result or an error-message. Please post that error-message if you need further help on that.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

@ImkeF @Anonymous  Yes, that is not an error. But the if you see the blue highlighted part in my image. This is in addion of server name. that means the code is changing my server name to value from paramter(Cname;Sname) including semicolon as well.

  1. Sname is SchemaName (parameter 1)
  2. Cname is ServerName (parameter 2)

Which makes the wrong server name and lead to error message. Please see the screenshot below. I have marked everything to make it more clear for you. Thank you for your help.

 

ServerName.jpg

 

 

Anonymous
Not applicable

Hi Kulchandra,

 

In your message you call the Sname variable your Schema, I believe this should be the Database within your Server. Maybe lost in translation, but that stands out to me.

 

If this is correct, your original query states the Database in the Select statement, therefore giving the Database twice.

 

My advice would be to first, run a simple query on your database using the non parameterised Server:Port and Database details, then examin the code it produces, using the Advanced Editor window. For my instance, I need to specify the implementation, if you see my original code. I got that from using the editor after using the above technique.

 

Hope that helps.

 

Anonymous
Not applicable

Hi @Anonymous @ImkeF  As per you suggestion I have made my query without Parameter. And, it looks like this(plz see screenshot) in Advance Editor windows and works perfectly fine without parameter.

What I am trying to do is embed the Parameter2 (Sname) in to Select Statment. I am able to embed server name Parameter1 (CName) already.

Hope you will find something on this image that will help to solve the problem i am facing. I am very thankful you guys are following up on this.

 

Without Parameter.jpg

Anonymous
Not applicable

Thanks @ImkeF , an honour to have you posting in the same thread! Thanks for all the effort you put in. Smiley Happy

ImkeF
Super User
Super User

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"

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

v-jiascu-msft
Microsoft
Microsoft

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors