Direct Query and BigQuery template

[mbranon]

I have a big query project with multiple datasets containing multiple tables. Is there a way to set up a template in Power BI for a report that utilizes direct query and a big query datasource? Ideally I would like to specify specific big query tables and/or datasets using Power BI parameters.

The big query connector does not seem to support any kind of parameters and the closest I've gotten is by manually changing the navigation step in the query editor. Does anyone have any ideas?

Thanks!

[Anonymous]

HI @mbranon,

You can use query parameters to create a template file with direct query mode.

Reference link:

Deep Dive into Query Parameters and Power BI Templates

>>The big query connector does not seem to support any kind of parameters and the closest I've gotten is by manually changing the navigation step in the query editor. Does anyone have any ideas?

You can try to use parameter to instead navigation item names.

BTW, you can turn on 'composite mode' feature to add both import mode and direct query mode datasource to one report.

Notice: publish feature will been disabled when you use both directquery and import mode datasource or multiple directquery data sources.

Regards,

Xiaoxin Sheng

[mbranon · Accepted solution]

Thanks!

The interface doesn't support query parameters for bigquer+direct query. However, using parameter variables in the advanced editor did the trick.

[alndellazari]

Hi @mbranon,

Sorry for bringing this post back, but would you be able to share your code for passing the parameter?

I'm new with Power BI and I couldn't find how do to it.

Thanks,

Allison

[mbranon]

Hi @alndellazari,

After creating your parameter you can drop it into the advance editor with the following syntax: #"MY_PARAM". You can even concatenate it with other strings:

#"project" = Source{[Name="project"]}[Data],
dataset= #"project"{[Name="a_dataset" & #"MY_PARAM" & "using_a_param",Kind="Schema"]}  [Data]

My specific example uses a parameter to determine which big query dataset to access.

Hope that helps!