Forum Discussion

Amir851's avatar
Amir851
Helper I
7 years ago
Solved

Pass Python Variable as a query parameter in Advance Editor

Hi All,

 

I am trying to create a query parameter for a variable named as start present in my Python script into Advance Editor in Power BI Desktop.

Basically, my script is trying to extract data from the server using Python web API concept. (Script run without any issue)

I have followed the procedure mention in the below two links :

 

https://community.powerbi.com/t5/Power-Query/How-to-pass-query-parameter-to-Data-Query-R-integration/m-p/39958

 

https://social.technet.microsoft.com/Forums/en-US/790ee8c0-297a-43b8-a8c9-08804218d555/question-about-power-query-variable-passing?forum=powerquery.

 

But I am encountering an error while applying those procedures.

 

Can you please help me with this & let me know how I can pass a string variable as a parameter into an Advance editor of Power BI.

 

Below is the code :

 

let
Source = Python.Execute("import requests#(lf)session = requests.Session()#(lf)....remaining code....start = ""2019/05/17 06:29"" #(lf)end = ""2019/07/17 06:29""),
Parameter Required Here
Parameter passed in script
start="2019/05/17 06:29" string format
end = "2019/07/17 06:29"

Need Help
Tutorial Requests
  • SteveCampbell's avatar
    SteveCampbell
    7 years ago

    My mistake, we lost the quitation marks. Try:

    Source = Python.Execute( "import requests.. other code.... """ & MyVariable & """ .. other code.... "),

     

    If this works I'll update the original answer.

6 Replies

  • SteveCampbell's avatar
    SteveCampbell
    Memorable Member
    7 years ago

    You need end the string with " and append the parameter with the &

     

    for example:

     

    Source = Python.Execute( "import requests.. other code.... " & MyVariable & " .. other code.... "),

     

    Power query will build eveything out as a string, and pass that to python. so the " & MyVariable & " .

    It reall breaks it down into three strings ...

    "import requests.. other code.... " & MyVariable & " .. other code.... "

    First string, your variable string, and the end string. The & is a concatenate and concatenates the strings, which it then passes as one big string to python.

     

    • Amir851's avatar
      Amir851
      Helper I
      7 years ago

      Dear SteveCampbell ,

       

      First of all, Thank you for your reply.

       

       I followed the same procedure as you mentioned.

       

      But encountering below error :

       

      DataSource.Error: ADO.NET: Python script error.
      File "PythonScriptWrapper.PY", line 91
      start=2019/05/17 06:29
      ^
      SyntaxError: invalid token

      Details:
      DataSourceKind=Python
      DataSourcePath=Python
      Message=Python script error.

       

      Screenshot for your reference : 

       

      • SteveCampbell's avatar
        SteveCampbell
        Memorable Member
        7 years ago

        This is a python error, in that you should not have leading 0s in your date.

        2019/05/17 06:29

         

        Maybe you should use the datetime library:

        datetime.datetime(2019, 5, 17, 6, 29)