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! 🙂
Hi @RandyHayes ,
There is no way of knowing exactly how many results will be returned.
- In this blog post, I found a fun way to create a large Numbers Table.
- Without a way to programatically create a Value Table based on CountRows or the like, we'll always run into this issue. Bummer!
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!
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!
Helpful resources
Microsoft 365 Conference – December 6-8, 2022
Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.
