Forum Discussion
HOW-TO REST API calls + M + refresh in Power BI Service (cloud)
Hi Element115 ,
for me, converting the "project_id"-query into a function often worked just fine and I didn't have to rework all my existing queries, inlining them into the API-call: Quick fix for Formula.Firewall issues in Power Query and Power BI (thebiccountant.com)Hi Element115 ,
agree, that sounds slow.
I am doing quite a bit with APIs and don't come across such slow loading times (especially for that litte transformation that you do).
Do you really need to retrieve the token in every every row?
Also, I could believe that the addColumn-function slows it down and I cannot see a reason to use it here, so I would refactore that code bit to use the native function instead.
Using error handlers also slows down the code, so just make sure to only use it where you really need it.
Also, it could be that due to the complexity of the code, PQ will go and attempt to download the data from the SQL server multiple times. To prohibit that, I would buffer it at this stage:
remove_cols = Table.Buffer( Table.RemoveColumns( .... )Hi Element115 ,
you do that "outside" of the query editor: Go to "Schedule refresh". There, under "Data source credentials" you can adjust it:
Then you can try buffering the input step for that pivot-operation.
Or even moving those transformations to a dataflow and just do the pivot in the pbix.
Hi Imke, re creating a dataflow, I noticed that the UI is slightly different from the one in PQ Editor where you set the Data Source Settings. Where is the Edit Permissions to set the Privacy Level? All PQ in the cloud does is fail to automatically pick up the already set gateway and permissions. And by mixing DB and API access inside the same M script, when I select a custom function, say the getToken() func, and then click the Configure connection that appears on the right, the dialog only shows me the DB connection already associated with the gateway, but does not show the internet/API connection that is already set up and used by the report.
Is this a bug? Or is their another UI path?
