How to take an input string on Power Bi server that then would be passed to a Python script

[mahmoud_kfawzy]

Hi all,

I have a Python script as my data source that that goes something like that:

user_date = input()

query = "select ... from... where date = '{}'".format(user_date)

df = pd.read_sql(query, conn)

print(df)"

I want the user to enter the data on the Pwer Bi reporting server (or preferably choose from a drop down menu that has the last 30 days' dates) and then this date would be passed to the python script somehow so it'd run and query the correct data.

Thank you.

[d_gosbell · Accepted solution]

You cannot pass parameters into python scripts at runtime, it's not currently supported and it's not secure as it leaves you open to things like SQL injection attacks.

But you could possibly achieve the end result you are after by generating a table or view of dates for a slicer, then adding this table of dates and the main table from your SQL query to a data model in direct query mode. Then when your user clicks on a date in the date slicer it will generate a SQL query that looks like "select ... from... where date = '{}'". If you want you can even go into the options and turn on the Query Reduction settings for this report to cause an 'Apply' button to appear on the slicer so the query to the main table is not triggered until the user clicks the Apply button.