Forum Discussion
Power BI Paginated Report with BigQuery DirectQuery – Filters not pushed down
Hi m_andel,
You are not missing anything, that is the current design. Power Query handles all query logic internally so the sql text box in the dataset properties is disabled.
So to customize your query or add parameters, edit it directly inside the Power Query editor using Value.NativeQuery.
Inside the M code use a parameterized native query like this:
let
Source = Value.NativeQuery(
BigQuery.Database(null),
"SELECT * FROM ENTER_NAME WHERE Date >= @StartDate AND Date <= @EndDate",
[StartDate = Parameters!StartDate.Value, EndDate = Parameters!EndDate.Value]
)
in
Source
Save and close the power query the dataset will now return filtered data directly from BigQuery.
Thanks and regards,
Anjan Kumar Chippa
Hi, v-achippa
I can now load data. My last issue is the connection to powerbi. Is it possible to use a google service account with a key file? I have found no option to configure the connection this way.
Hi m_andel,
Currently using a google service account key file is not supported in Power Query based connections within Power BI Report Builder or the Power BI Service. Only OAuth authentication with a user account is supported, so please use OAuth sign-in for now.
Thanks and regards,
Anjan Kumar Chippa
Hi @m_andel,
As we haven’t heard back from you we wanted to kindly follow up to check if the solution I have provided for the issue worked? or let us know if you need any further assistance.
Thanks and regards,
Anjan Kumar Chippa
Hi, v-achippa ,
unfortunately there seems to be no solution to realize paginated reports the way we need to. Our next step would be to create such reports in Power BI Desktop.
As I understand there it is possible to query the BigQuery and use a service account for authentication.
And I suppose it is possible to design reports that feel just like paginated reports?
Kind regards,
Martin