Hello!
Looking for some insights on this one... not sure how its done and tried a few things that didn't work.
Scenario: I have a table with the schema below. I want to Collect a new collection of ONLY the first two items under the "coordinates" subtable.
{ "type": "FeatureCollection", "metadata": { "generated": 1555194002000, "url": "https://earthquake.usgs.gov/fdsnws/event/1/query?format=geojson&starttime=2014-01-01&endtime=2014-01-02&latitude=47&longitude=-122&maxradiuskm=300&orderby=magnitude-asc", "title": "USGS Earthquakes", "status": 200, "api": "1.8.1", "count": 9 }, "features": [ { "type": "Feature", "properties": { "mag": -0.33, "place": "27km NNW of Packwood, Washington", "time": 1388615267370, "updated": 1469215317030, "tz": -480, "url": "https://earthquake.usgs.gov/earthquakes/eventpage/uw60664496", "detail": "https://earthquake.usgs.gov/fdsnws/event/1/query?eventid=uw60664496&format=geojson", "felt": null, "cdi": null, "mmi": null, "alert": null, "status": "reviewed", "tsunami": 0, "sig": 2, "net": "uw", "code": "60664496", "ids": ",uw60664496,", "sources": ",uw,", "types": ",general-link,geoserve,nearby-cities,origin,phase-data,", "nst": 9, "dmin": 0.01894, "rms": 0.08, "gap": 71, "magType": "md", "type": "earthquake", "title": "M -0.3 - 27km NNW of Packwood, Washington" }, "geometry": { "type": "Point", "coordinates": [ -121.7506667, 46.8501667, 1.436 ] }, "id": "uw60664496" }, { "type": "Feature", "properties": { "mag": 0.19, "place": "22km S of Sequim, Washington", "time": 1388595112000, "updated": 1469215316190, "tz": -480, "url": "https://earthquake.usgs.gov/earthquakes/eventpage/uw60664386", "detail": "https://earthquake.usgs.gov/fdsnws/event/1/query?eventid=uw60664386&format=geojson", "felt": null, "cdi": null, "mmi": null, "alert": null, "status": "reviewed", "tsunami": 0, "sig": 1, "net": "uw", "code": "60664386", "ids": ",uw60664386,", "sources": ",uw,", "types": ",cap,general-link,geoserve,nearby-cities,origin,phase-data,", "nst": 3, "dmin": 0.1492, "rms": 0.22, "gap": 306, "magType": "md", "type": "earthquake", "title": "M 0.2 - 22km S of Sequim, Washington" }, "geometry": { "type": "Point", "coordinates": [ -123.108, 47.881, 4.572 ] }, "id": "uw60664386" }, { "type": "Feature", "properties": { "mag": 0.22, "place": "21km NNW of Amboy, Washington", "time": 1388536764680, "updated": 1469215315430, "tz": -480, "url": "https://earthquake.usgs.gov/earthquakes/eventpage/uw60664276", "detail": "https://earthquake.usgs.gov/fdsnws/event/1/query?eventid=uw60664276&format=geojson", "felt": null, "cdi": null, "mmi": null, "alert": null, "status": "reviewed", "tsunami": 0, "sig": 1, "net": "uw", "code": "60664276", "ids": ",uw60664276,", "sources": ",uw,", "types": ",cap,general-link,geoserve,nearby-cities,origin,phase-data,", "nst": 9, "dmin": 0.08565, "rms": 0.1, "gap": 182, "magType": "md", "type": "earthquake", "title": "M 0.2 - 21km NNW of Amboy, Washington" }, "geometry": { "type": "Point", "coordinates": [ -122.5188333, 46.0926667, 13.97 ] }, "id": "uw60664276" }, { "type": "Feature", "properties": { "mag": 0.43, "place": "19km E of Kelso, Washington", "time": 1388577684900, "updated": 1469215315950, "tz": -480, "url": "https://earthquake.usgs.gov/earthquakes/eventpage/uw60664346", "detail": "https://earthquake.usgs.gov/fdsnws/event/1/query?eventid=uw60664346&format=geojson", "felt": null, "cdi": null, "mmi": null, "alert": null, "status": "reviewed", "tsunami": 0, "sig": 3, "net": "uw", "code": "60664346", "ids": ",uw60664346,", "sources": ",uw,", "types": ",cap,general-link,geoserve,nearby-cities,origin,phase-data,", "nst": 13, "dmin": 0.06314, "rms": 0.15, "gap": 143, "magType": "md", "type": "earthquake", "title": "M 0.4 - 19km E of Kelso, Washington" }, "geometry": { "type": "Point", "coordinates": [ -122.6526667, 46.1491667, 15.17 ] }, "id": "uw60664346" } }
JUST THE FIRST TWO ITEMS in the "Coordinates" subtable. I want a Collection that looks like:
{id: 1, long: -122.6526667, lat: 46.1491667} {id: 2, long: -122.5188333, lat: 46.0926667} {id: 3, long: -123.108, lat: 47.881}
Tried so far:
ClearCollect( colSecondCollection, First(colFirstCollection.features).features.geometry).geometry ); // Results in a table of nested tables. I need the coordinates alone // Also tried: ForAll( colFirstCollection, ClearCollect( colSecondCollection, First(First(colFirstCollection.features).features.geometry).geometry.coordinates).coordinates ) ); // Error: "this action is not allowed within a ForAll"
Anyone have some idears?
Thank yoU!
You can get part of the way there with this formula:
ClearCollect(finalResults, ForAll(Ungroup(colFirstCollection.features, "features"), {id:Value.id, long:First(Value.geometry.coordinates.Value).Value, lat:Last(FirstN(Value.geometry.coordinates.Value,2)).Value} ) )
This will return the record table like you wanted - with one exception...the sequential ID numbering. In the above formula, you will get the id that is in the original JSON payload not a sequence.
Trying to do sequential numbering in PowerApps is oddly challenging.
I hope this puts you on the right track.
Clear(colSecondCollection);ForAll(colFirstCollection.features,Collect(colSecondCollection,features.Value));Clear(colThirdCollection);ForAll(colSecondCollection,Collect(colThirdCollection,Value.geometry))
This will give you the main collection colThirdCollection which will contain all the coordinates
Now, if you only need the first 2 coordinates then use
First(colThirdCollection).coordinates.Value - First coordinates
Last(FirstN(colThirdCollection,2)).coordinates.Value - Second coordinates
And to fetch individual values for the coordinates
Example : coordinates: [
-121.7506667,
46.8501667,
1.436
]
First(First(colThirdCollection).coordinates.Value).Value = -121.7506667
Last(FirstN(First(colThirdCollection).coordinates.Value,2)).Value = 46.8501667
Last(FirstN(First(colThirdCollection).coordinates.Value,3)).Value = 1.436
Hope this helps
Hi @RandyHayes and @RezaDorrani ,
Thank you both for the thoughtful replies. This was the easiest for me to implement:
ForAll( Ungroup(colEarthquakeResults.features, "features"), Collect(colEarthquakeLatLongs, {long: First(geometry.coordinates.Value).Value, lat: Last(FirstN(geometry.coordinates.Value,2)).Value} ) )
It results in:
@RandyHayes, you were right! It is not easy to implement an incremental ID column in this case! Here's what I tried so far:
ClearCollect(colEarthquakeLatLongs, AddColumns( ForAll( Ungroup(colEarthquakeResults.features, "features"), {long:First(geometry.coordinates.Value).Value, lat:Last(FirstN(geometry.coordinates.Value,2)).Value} ), "id", If( 1 = 1,
CountRows(colEarthquakeLatLongs) + 1 ) ) );
ClearCollect(colEarthquakeLatLongs, ForAll( Ungroup(colEarthquakeResults.features, "features"), {id: Text(CountRows(colEarthquakeLatLongs)) + 1, long: First(geometry.coordinates.Value).Value, lat:Last(FirstN(geometry.coordinates.Value,2)).Value} ) )
ForAll( Ungroup(colEarthquakeResults.features, "features"), Collect(colEarthquakeLatLongs, {id: Value(CountRows(Ungroup(colEarthquakeResults.features, "features"))) + 1, long: First(geometry.coordinates.Value).Value, lat: Last(FirstN(geometry.coordinates.Value,2)).Value} ) )
ClearCollect(colEarthquakeLatLongs, AddColumns( ForAll( Ungroup(colEarthquakeResults.features, "features"), {long:First(geometry.coordinates.Value).Value, lat:Last(FirstN(geometry.coordinates.Value,2)).Value} ), "id", If( !IsEmpty(First(First(First(colEarthquakeResults.features).features.geometry).geometry.coordinates)), Text(Value(varCounter) + 1) ) ) )
I bet @mr-dang knows how to do this! 🙂
Well, here's the loaded question...do you have any idea on how many records you anticipate?
If you have knowledge that there is a preset amount or at least an amount of rows that will never exceed a certain number, then you can alter the formula like this:
ClearCollect(colEarthquakeLatLongs, ForAll([1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30], If(Value<=CountRows(Ungroup(colEarthquakeResults, "features")), {id:Value, long:First(Last(FirstN(Ungroup(colEarthquakeResults, "features"), Value)).Value.geometry.coordinates).Value, lat:Last(FirstN(Last(FirstN(Ungroup(colEarthquakeResults, "features"), Value)).Value.geometry.coordinates,2)).Value } ) ) )
Of course, in this case, that gives you only a max of 30 sequential ID's...but you could change that if needed.
See if that fits better for you.
Hi @RandyHayes ,
There is no way of knowing exactly how many results will be returned.
Ended up going with this MONSTER!:
Set(varNumTable, [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]); Clear(colNumbersTable); Clear(colRecordToCount); ClearCollect(colNumTemp, varNumTable); ForAll(colNumTemp, ForAll(colNumTemp, ForAll(colNumTemp, Collect(colRecordToCount, { record: 1 } ); Collect(colNumbersTable, { number: CountRows(colRecordToCount) } ) ) ) ); ClearCollect( colEarthquakeResults, earthquakeAPI.GETearthquakes( { format: "geojson", starttime: Today() - 1, endtime: Today(), latitude: Location.Latitude, longitude: Location.Longitude, maxradiuskm: 1000, orderby: "magnitude-asc" } ) ); ClearCollect(colEarthquakeLatLongs, ForAll(colNumbersTable, If(number <= CountRows(Ungroup(colEarthquakeResults.features, "features")), { id: number, long: First(
Last(
FirstN(
Ungroup(colEarthquakeResults.features, "features"),
number
)
).geometry.coordinates
).Value, lat: Last(
FirstN(
Last(
FirstN(
Ungroup(colEarthquakeResults.features, "features"),
number
)
).geometry.coordinates,2
)
).Value } ) ) )
...all to get an ID column in this Collection!:
@RandyHayesand @RezaDorrani , thanks for the Sunday help! Have a great week!
I just hope we don't have that many earthquakes!!
Glad things are working now.
Thanks for the help here @RezaDorrani @RandyHayes.
One thing I'll add. When I want to understand the structure a little bit more and figure out when I need to use First() Last() etc., I use a JSON viewer:
This one is Code Beautify which is one of the prettier viewers. The collapsing helps to understand things.
@RandyHayes, hep meh! I'm missing the pattern here... Looking to do a similar thing, but this time I have a different schema. Instead of earthquakes, I want to take the results of a Fuzzy Search and plot pins on a map.
Heres the schema:
{ "summary": { "query": "pub", "queryType": "NON_NEAR", "queryTime": 39, "numResults": 10, "offset": 0, "totalResults": 30, "fuzzyLevel": 1, "geoBias": { "lat": 47.56328695, "lon": -122.37870965 } }, "results": [ { "type": "POI", "id": "US/POI/p0/8718662", "score": 2.575, "dist": 128.26041102796466, "info": "search:ta:840539001007346-US", "poi": { "name": "West Seattle Brewing Company", "phone": "+(1)-(206)-7086627", "categorySet": [ { "id": 9376007 } ], "categories": [ "café/pub", "microbrewery/beer garden" ], "classifications": [ { "code": "CAFE_PUB", "names": [ { "nameLocale": "en-US", "name": "microbrewery/beer garden" }, { "nameLocale": "en-US", "name": "café/pub" } ] } ] }, "address": { "streetNumber": "4415", "streetName": "Fauntleroy Way SW", "municipalitySubdivision": "Seattle, Genesee, Fairmount Park", "municipality": "Seattle", "countrySecondarySubdivision": "King", "countryTertiarySubdivision": "Seattle", "countrySubdivision": "WA", "postalCode": "98126", "extendedPostalCode": "981262631", "countryCode": "US", "country": "United States", "countryCodeISO3": "USA", "freeformAddress": "4415 Fauntleroy Way SW, Seattle, WA 98126", "localName": "Seattle", "countrySubdivisionName": "Washington" }, "position": { "lat": 47.56428, "lon": -122.37784 }, "viewport": { "topLeftPoint": { "lat": 47.56518, "lon": -122.37917 }, "btmRightPoint": { "lat": 47.56338, "lon": -122.37651 } }, "entryPoints": [ { "type": "main", "position": { "lat": 47.56412, "lon": -122.37767 } } ] } ] }
Problem: I can only get the first set of Lat / Longs from the Fuzzy Search results
Tried so far:
/*==================================================================== Create a numbers table for ForAll looping purposes ====================================================================*/ Set(varNumTable, [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]); Clear(colNumbersTable); Clear(colRecordToCount); ClearCollect(colNumTemp, varNumTable); ForAll(colNumTemp, ForAll(colNumTemp, ForAll(colNumTemp, Collect(colRecordToCount, { record: 1 } ); Collect(colNumbersTable, { number: CountRows(colRecordToCount) } ) ) ) ); /*==================================================================== Collect all nearby "pubs" within a 2mile (~3200 meter) radius ====================================================================*/ ClearCollect( colNearbySpots, azureMapTimezone.GETSearchFuzzy(1.0,1,3, { query: "pub", lon: Location.Longitude, lat: Location.Latitude, radius: 3200 } ) ); /*==================================================================== Using the numbers table, create a Collection of all nearby spot Lat/Longs returned from the previous API call. Thank god for the PowerApps forums!: https://powerusers.microsoft.com/t5/General-Discussion/Complex-Collection-of-a-Collection/m-p/267273#M78095 ====================================================================*/ ClearCollect(colNearbySpotLatLongs, ForAll(colNumbersTable, If(number <= CountRows(Ungroup(colNearbySpots.results, "results")), { id:number, long: First(First(colNearbySpots.results).results.position).position.lon, lat: First(First(colNearbySpots.results).results.position).position.lat } ) ) ); /*==================================================================== Using the table of nearby spot Lat/Longs and the numbers table, call the Azure Maps API to get a map image of these spots with pins, at each zoom level! ====================================================================*/ Clear(colNearbySpotMaps); ForAll( Filter(colNumbersTable, number >= 10 && number <= 14), AddColumns( Collect(colNearbySpotMaps, azureMapTimezone.GetMapImageWithPins( 1.0, 600, 600, { zoom: number, center: Concatenate(Text(Location.Longitude), ",", Text(Location.Latitude)), pins: Concatenate("default||", Concat(colNearbySpotLatLongs, lat & " " & lat, "|")), path: Concatenate("ra3200", "||", Text(Location.Longitude), " ", Text(Location.Latitude)) } ) ) , "id", number ) ); ClearCollect(colNearbySpotMapsWithIDs, ForAll(colNumbersTable, If(number <= CountRows(colNearbySpots), { id:number, mapImage: colNearbySpotMaps } ) ) )
I also tried First(Ungroup(colNearbySpots.results, "results").position).position.lon (/lat).
I end up with a lat/long list that only shows the first Result copied a bunch:
...and colNearbySpotMapsWithID is nested instead of a flat table:
I know this is a long post 🙂 . Hopefully the patterns that emerge for unfolding JSON will help others too.
Thank you!
So, I breezed through some of the post because I had a question from the start. I see only one record with a position record in it...is the assumption that you have multiple records in the results array?
I'll start there - I saw some issues with the remaining formulas, but we can cover that after I clarify the above as I don't want to start down a road that is on the road trail.
This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.
Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.
User | Count |
---|---|
196 | |
67 | |
46 | |
41 | |
28 |
User | Count |
---|---|
255 | |
121 | |
84 | |
80 | |
80 |