I am struggling to connect Power BI to a BigQuery stored procedure that requires a dynamic date input. I need the report to update based on a date selected by the user or a rolling date. Currently, I can call the procedure with a hardcoded string, but whenever I try to use a Power Query parameter or M variable to pass the date, I get syntax errors. What is the specific M code syntax to concatenate the CALL statement with a dynamic date value?
3 answers
To pass a dynamic date, you must use the Value.NativeQuery function within the Power Query Advanced Editor. First, create a date parameter in Power BI (e.g., TargetDate). In your M code, you need to convert this date into a text format that BigQuery recognizes, typically yyyy-MM-dd. Your string should look like this: SQLQuery = "CALL your_project.dataset.your_proc('" & Date.ToText(TargetDate, "yyyy-MM-dd") & "')". A crucial tip: ensure you set [EnableFolding=false] inside the Value.NativeQuery options. BigQuery procedural calls often fail if Power BI tries to fold them. This method allows the engine to regenerate the SQL string every time the parameter changes before sending it to Google Cloud.
I've followed that logic, but I'm getting an "Access Denied" error when the script runs. Does the service account or organizational account used for the BigQuery connection need specific permissions to execute procedures versus just reading tables
Make sure you wrap the date in single quotes inside your concatenated string. BigQuery expects CALL proc('2024-01-01'), so your M code must include those literal quotes.
Great catch, Patricia. I’d also add that if you are using DirectQuery with Dynamic M Parameters, you must bind the date column in your model to the M parameter. This allows a slicer on the report page to actually drive the CALL statement in real-time. It's a game changer for BigQuery performance since the filtering happens at the source via the procedure rather than loading all rows into Power BI memory.
You definitely need the bigquery.jobs.create permission at the project level to run a stored procedure, Steven. Unlike a standard SELECT statement that might just read from a cached view, a CALL statement starts a script job. I also recommend checking your "Billing Project" setting in the connection string. If your data is in one project but you're billing another, you must explicitly define [BillingProject="your-id"] in the GoogleBigQuery.Database step, or the procedure call will fail to find the routine.