Jul 22 2021 08:36 AM
Hi, appreciate if anyone can help with this
i have a workbook with 110 queries on it.
when i refresh all i get the above error being
"datasource.error the web page function didnt finish within timeout of 100 seconds"
is there a fix to this??
Also,
being that i have lots of queries, and i do need to "refresh all" at times
should i have
"Enable background refresh" ON or OFF ??
also, should i have
"Enable Fast data load" ON or OFF ??
i dont know if these have anything to do with the above error i am getting?
thanks very much for your help
steve r
Jul 22 2021 09:31 AM
You may try to add Timeout parameter like
= ...(Web.Contents("URL", [Timeout=#duration(0,0,20,0)])),...
(20 min is here)
Jul 22 2021 09:44 AM
Jul 22 2021 10:27 AM
Hi @stever78
That's not where [Timeout=#duration(0,0,20,0)] should go
Let's assume that your query Appends6 is the only one that gets data from the Web
Edit query Appends6. You should find a line that says something like:
xyz = ...Web.Contents("https://abcd...")
change the above so it says:
xyz = ...Web.Contents("https://abcd...", [Timeout=#duration(0,0,20,0)])
If you can get it to work please attach your workbook to your next reply please
Jul 22 2021 10:34 AM
Timeout is the optional parameter for Web.Contents() Web.Contents - PowerQuery M | Microsoft Docs , not for Table.Combine(). Error says you have timeout error querying some them.
As for your code I'd recommend to add Index column after Table.Combine() to fix table in memory, after that sort, after that remove duplicates and after that remove Index column.
Jul 22 2021 11:02 AM
Jul 22 2021 11:05 AM
Jul 22 2021 12:51 PM
Nope, that not necessary improves performance. The goal is to ensure your table will be sorted correctly. Due to so called lazy evaluation Table.Sort() may sort the table only partially by parts which are currently loaded in memory. The workaround is to push loading of the entire table into memory using Table.Buffer() as here Remove Duplicates and Keep the Last Record with Power Query - Excelerator BI or by adding/removing Index column which do exactly the same.
Above is not golden rule which warranty result on 100%, but in most cases it works.
Related topic is documented here Common Authoring Issues in Power Query | Microsoft Docs
Jul 22 2021 01:27 PM
Jul 23 2021 05:06 AM
@stever78 , yes, more complex the task and larger the data more deep you need to dive into PQ.