Latest Contributions by MartinPerez

WHOA Tory!  that is really cool to know; I am so glad you can use it.  These days working from home is quite common, and this may help a lot.  Please take care you all. ... View more

Hey Tory! I hope we get this working, as it worked for me, and I would like to give back a little. May I ask if your are showing the values of the table into the statements for easy understanding of your post, or are they in the table and the statements really have [Value] in the code as in: whereClause = controltable{1}[Value],? I ask because, if they are in the table and the URL has quotes in there, take them out (just as it is in your previous post code, where the URL has no quotes).  Otherwise, as I changed the code for myself, I really have no entries in any table for the URL, so my statement is: fsURL="https://arcgis.p.../FeatureServer/0/", To test it, you can get rid of the tables temporarily and write the code as: let //Custom Variables //Hosted = Excel.CurrentWorkbook(){[Name="Hosted_FS0"]}[Content], fsURL = "https://services1.arcgis.com/E5n4f1VY84i0xSjy/arcgis/rest/services/service_6c410d1aeeec49f4be7a0b951cbf81de/FeatureServer/0/", //Token_auto = Excel.CurrentWorkbook(){[Name="Token_auto"]}[Content],], whereClause = "objectid>0", fieldsOut = "*", token = "868JC4M971rlJUGHDKjd...", //Get Data Source = Json.Document(Web.Contents(fsURL & "query?where=" & whereClause & "&outFields=" & fieldsOut & "&returnGeometry=false&f=pjson&token=" & token), 65001) in Source as these variables are really all strings being concatenated in the Source statement. Take a look and let us know :O) Best regards, Martín  ... View more

Hello Tory! What I see for this code is that there is a missing comma (,) after the fsURL statement; and an extra "]," after the Token_auto = statement. Please take a look and let us know.  We are very tankful with this solution, as we use it quite commonly, and works like a charm.  Hope this helps. Best regard, ... View more

Hello There! Thanks for taking care. I am not the administrator to perform such a change; I am just an end user, without ArcMAP. We just get the data and prepare some statistics and graphics. The layer has over 60,000 features we are interested in (from nearly half a million), and I can only query 10,000 features at a time. So, I thought that a loop would do the trick, but it seems that this is not the case with Power Query (I think a loop can easily be configured in Python from ArcMAP, but ...). I am actually good with the explained approach, and I just need to add a new query from time to time; so it is not a big deal. I am pretty happy with your tutorial on how to access the data; I was just looking for some efficiency improvement. Best regards, ... View more

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 ... View more

It worked like a charm!  I skipped some steps, like creating the tables, and used manual entries for the variables; but worked perfectly. But ... I hit the feature query limit.  I know that there is no limit when querying the objectids, so you can query them first, and then you can query progressively in blocks until getting all the data.  mmmmm... it will take me a while to figure out that.  Any suggestions?  Thank you again for sharing all this information.  Best regards ... View more

Hi! Can't wait to try it.  Sorry for being so pushy; we do really appreciate your support :O) Best regards ... View more

Thank you for sharing all of this information.   There should be a lot of people waiting for Part 3; please publish it.   Best regards  ... View more