Hi!
There are some forums that request you to show some sort of research and effort to solve your issue before asking for help. So, this is what I tried; that worked, but I understand it is not an efficient way to do it. Please bear in mind that this is my first experience with Excel Power Query :O)
- I modified the first data acquisition query to include my intended Where Clause as:
//Custom Variables
.
.
whereClause = "some_field is not null",
//(remember that I skipped the table creations, so I needed to include here the actual values)
- I added &orderByFields=objectid before &returnGeometry=false in the Source clause:
Source = Json.Document(Web.Contents(fsURL&"query?where="&whereClause&"&outFields="&fieldsOut&"&orderByFields=objectid&returnGeometry=false&f=pjson&token="&token),65001),
- I created a new blank query and copied the first one into it, and made the following modification:
whereClause = “some_field is not null and objectid > 55000“,
This is because I looked for the last objectid from the first query, remember that it is ordered by objectid. This created another worksheet with the next 10,000 block of data.
- Repeated the stetp 3. six more times to get the over 60,000 points I were interested in. So I ended with seven worksheets with all the data scattered.
- Went to Query tab of the Ribbon (shown when selecting any cell of a queried table) and selected Append, Three or more tables, and selected all these queries:
This created the Append1 query you can see in the list; and it worked quite well for my experience with Power Query.
- I deleted the worksheets with the fragmented data, and left only the new one with all the data collected together. This changed all these queries to Connection only:
- Improved this process a little, by querying the objectids into a table, and using these values for the where clause of all other queries, to “automate” it a little more.
I ran your original process changing the where clause to:
whereClause = "worked_by is not null",
and adding &orderByFields=objectid&returnIdsOnly=true to the source line:
Source = Json.Document(Web.Contents(fsURL&"query?where="&whereClause&"&outFields="&fieldsOut&"&orderByFields=objectid&returnIdsOnly=true&returnGeometry=false&f=pjson&token="&token),65001),
then converted into table and drilled down etc. etc. etc.
- I then used this new table to address the query limit thresholds as:
let
//Custom Variables
Query7=Excel.CurrentWorkbook()
{[Name="Query7"]}[Content],
objectid = Number.ToText(Query7{9999}[Objectid]),
whereClause = "worked_by is not null and objectid > ",
Source = Json.Document(Web.Contents(fsURL&"query?where="&whereClause&objectid&"&outFields="&fieldsOut&"&orderByFields=objectid&returnGeometry=false&f=pjson&token="&token),65001),
for the second query.
Then:
objectid = Number.ToText(Query7{19999}[Objectid]),
objectid = Number.ToText(Query7{29999}[Objectid]),
objectid = Number.ToText(Query7{39999}[Objectid]),
objectid = Number.ToText(Query7{49999}[Objectid]),
objectid = Number.ToText(Query7{59999}[Objectid]),
for the remaining queries (two to six, respectively, as the first and second ones where already correctly configured). I have a Query0 because I implemented the whereClause = “some_field is not null and objectid > 55000“, as my first query, and missed the first 10,000 points where objectid > was not needed.
Now, I only need to refresh the objecids query, and then the Append1 query to gather all the data at once. Again, not efficient, but working :O)
So, what when my data layer increases over 100,000 features, or over 200,000? Do I create another four, five, ten more queries and append them all? I don’t think so. But looping around in Power Query seems a scary thing, as Power Query is not a conventional programming language, and remember that this is my first experience with it. Any help will be greatly appreciated.
Best regards
Exciting News Martin!
I have got the script to work and it is providing the table of attributes as hoped:
The only issue now is when i go to 'Close & Load' i am getting the error message 'Download failed' with no detail. Even though its showing all the rows for the survey.
If there's any advice you have for this step it would be great. Thanks so much for all your help, it has been much appreciated!
