Concatenate ESRI JSON geometry rings using Powerquery in Power Bi

Question

I've been attempting to extract the geometry from an ESRI Rest endpoint. I return JSON and can drill down to the point where each row is a list of lists. I would like to concatenate all the points into a new row that Power Bi can display which I believe is in format

POLYGON((lon, lat lon2, lat2 lon3, lat3 lon4, lat4))

There is not a set number of points per polygon. In the example below there are 4 points that make up the polygon. If you check the endpoint url there are many polygons.

"geometry": {
"rings": [
 [
  [
   -91.477749413304764,
   31.470175721032774
  ],
  [
   -91.477709210911314,
   31.470214015064812
  ],
  [
   -91.477676009740037,
   31.470105771763997
  ],
  [
   -91.477749413304764,
   31.470175721032774
  ]
 ]
]
}

Here is my current code

 let
    Source = Json.Document(Web.Contents("https://gispublic.ducks.org/arcgis/rest/services/WMU/PastUnits/MapServer/0/query?where=1%3D1&text=&objectIds=&time=&geometry=&geometryType=esriGeometryEnvelope&inSR=&spatialRel=esriSpatialRelIntersects&distance=&units=esriSRUnit_Foot&relationParam=&outFields=*&returnGeometry=true&returnTrueCurves=false&maxAllowableOffset=&geometryPrecision=&outSR=&havingClause=&returnIdsOnly=false&returnCountOnly=false&orderByFields=&groupByFieldsForStatistics=&outStatistics=&returnZ=false&returnM=false&gdbVersion=&historicMoment=&returnDistinctValues=false&resultOffset=&resultRecordCount=&returnExtentOnly=false&datumTransformation=&parameterValues=&rangeValues=&quantizationParameters=&featureEncoding=esriDefault&f=pjson"), 65001),
    features = Source[features],
    #"convertedtoTable" = Table.FromList(features, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"expandedColumn" = Table.ExpandRecordColumn(#"convertedtoTable", "Column1", {"geometry"}, {"geometry"}),
    geo = Table.ExpandRecordColumn(expandedColumn, "geometry", {"rings"}, {"geometry.rings"}),
    #"geometry rings" = geo[geometry.rings],
    #"Converted to Table" = Table.FromList(#"geometry rings", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Column1 = #"Converted to Table"[Column1],
    #"Converted to Table1" = Table.FromList(Column1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table1", "Column1")
in
    #"Expanded Column1"

See also my edit which shows shortened code to handle dynamic numbers of rings/columns — Ron Rosenfeld

Ron Rosenfeld Ron Rosenfeld · Accepted Answer · 2021-09-10T13:07:59

You can

"drill down" a little further so that each list just contains a single polygon
Extract the list into a delimited array
split the array into columns where each row represents a polygon

With regard to the "splitting", if you will be doing this once, you can just accept the code generated by the UI.

If you will be doing this multiple times, and there might be different numbers of polygons in each run, it would be more efficient to calculate the number of columns needed.

But here is specimen code, replacing all after your #"geometry rings", but shortened as there are over 4000 columns generated.

    #"geometry rings" = geo[geometry.rings],

//drill down to combine each ring into a single list
    comb1 = List.Transform(#"geometry rings", each List.Combine(_)),
    comb2 = List.Transform(comb1, each List.Combine(_)),

//convert to table
    rings = Table.FromList(comb2,Splitter.SplitByNothing(),{"Rings"}),
    #"Extracted Values" = Table.TransformColumns(rings, {"Rings", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Rings", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Rings.1", "Rings.2", ...,

  #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Rings.1", type number}, {"Rings.2", type number}, ...

 in
   #"Changed Type

Edit: shortened code for dynamic numbers of columns

   #"geometry rings" = geo[geometry.rings],

//drill down to combine each ring into a single list
    comb1 = List.Transform(#"geometry rings", each List.Combine(_)),
    comb2 = List.Transform(comb1, each List.Combine(_)),

numCols = List.Accumulate(comb2,
            0,
            (state,current)=> if state > List.Count(current) then state else List.Count(current)),

//convert to table
    rings = Table.FromList(comb2,Splitter.SplitByNothing(),{"Rings"}),
    #"Extracted Values" = Table.TransformColumns(rings, {"Rings", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),

    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Rings", 
        Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), numCols), 

colTypes = List.Transform(Table.ColumnNames(#"Split Column by Delimiter"), each {_, Number.Type}),

    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",colTypes)

in
    #"Changed Type"

Concatenate ESRI JSON geometry rings using Powerquery in Power Bi

1 Answers