Question

How do I call a BigQuery stored procedure with a parameter from Power BI and pass a date dynamically?

I need to call a BigQuery stored procedure from Power BI and pass a dynamic date parameter, and I’m looking for guidance on how to set this up correctly.

Answer 1

To call a BigQuery stored procedure with a dynamic date parameter from Power BI, you can follow these steps:

1. Set up BigQuery Connector in Power BI:

   • In Power BI Desktop, go to Home > Get Data > More.

   • Choose Google BigQuery and sign in with your Google account.

   • Select the appropriate project and dataset, and then connect to BigQuery.

2. Create a Custom Query:

   • After connecting to BigQuery, choose Advanced options and use a custom SQL query to call your stored procedure.

   • For example, you can pass the dynamic date as a parameter in the query like this:

CALL `project_id.dataset_id.procedure_name`(@dynamic_date);

• Create a Parameter in Power BI:

  • Go to Modeling > New Parameter and create a date parameter (e.g., DynamicDate).

  • You can set the default date or make it a user-input parameter for dynamic selection.

• Update the SQL Query to Use the Parameter:

  • Replace the static date in your SQL query with the dynamic parameter. Power BI will automatically substitute the parameter value when executing the query.

CALL `project_id.dataset_id.procedure_name`(@DynamicDate);

Refresh the Query:

• After setting everything up, clicking Refresh will pass the dynamic date parameter to your BigQuery stored procedure, running the query with the selected date.