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:
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( .... )
Yes, re the getToken() call: the reason is that because the token is valid only for one hour, and since it takes longer than one hour to get everything, and not knowing for sure whether it took one hour plus to call the API or internal PQ processing, I did not want to take the chance of having the token expire before all the records got ingested. And since I have no documention from the API provider...
But I noticed something else yesterday about this blackbox API and I think I can chance calling getToken() only once. Will try.RE error handlers, unfortunately there are unavoidable because... the API again, it is funky. 😉
I'll refactor addColumn (interesting, I wouldn't have thought that this would be so expensive) and add a Table.Buffer. Excellent suggestion! Thanks!
Actually, as I am reading the doc on Table.Buffer, Microsoft suggests using Table.StopFolding. What do you think? If the table is not fully loaded (max is 1,000 records), would this mean that Table.StopFolding will be ignored?
Is it enough to buffer only once? In other words, if buffering early on, at the top of the script as you suggested, does this mean that all subsequent ETL operations will use the buffered table?
