Power Query Retravailler un fichier JSON avec PQ

[out:xml][timeout:100];
//la commune
area["ref:INSEE"="89197"]->.a;
rel(area.a)["admin_level"="8"]["boundary"="administrative"];
out geom;
//le canton
>;
is_in;
rel(pivot)
["ref:INSEE"="8907"]["boundary"="political"];
out geom;
//le département
>;
is_in;
rel(pivot)
["ref:INSEE"="89"]["boundary"="administrative"];
out geom;
{{style:
  relation{text:name}
  node {width: 0;opacity:0;fill-opacity:0;}
}}

Sub test()
Dim a, w, e, dico As Object, txt As String
Dim i As Long, ii As Byte
Set dico = CreateObject("Scripting.Dictionary")
Application.ScreenUpdating = False
'la feuille où figurent les données représentées au post #67
a = Sheets(1).Range("A1").CurrentRegion.Value2
    For i = 2 To UBound(a, 1)
       'txt = la commune et l'index forme la clé du dico
        txt = Join$(Array(a(i, 2), a(i, 3)), "|")
        If Not dico.exists(txt) Then
            ReDim w(1 To UBound(a, 2), 1 To 1)
        Else
            w = dico(txt)
            ReDim Preserve w(1 To UBound(w, 1), 1 To UBound(w, 2) + 1)
        End If
        For ii = 1 To UBound(a, 2)
            w(ii, UBound(w, 2)) = a(i, ii)
        Next
        'on associe l'item à la clé soit un tableau à 2 dimensions
        'soit l'équivalent de la variable Qry_JSON dans le code
        'figurant au post #66 de mromain
        dico(txt) = w
    Next
    For Each e In dico
       'w = Qry_JSON
        w = Application.Transpose(dico(e))
        MaxLatitude = Application.Max(Application.Index(w, 0, 5))
        MaxLongitude = Application.Max(Application.Index(w, 0, 4))
        MinLatitude = Application.Min(Application.Index(w, 0, 5))
        MinLongitude = Application.Min(Application.Index(w, 0, 4))
        'à compléter avec le code de mromain
 
 
    Next
Set dico = Nothing
Application.ScreenUpdating = True
End Sub

let
    fnExtractGeometryInfos = (geometry as record) as table =>
        let
            fnExtractPointsCoordinates = (list as list) as list => List.Transform(list{1}{0}, each [ShapeID = list{0}, Longitude=_{0}, Latitude=_{1}]),
            shapesList =
                let
                    list = geometry[coordinates],
                    listCount = List.Count(list),
                    geoType = geometry[type]
                in
                    if geoType = "Polygon" then {{1, geometry[coordinates]}} else if geoType = "MultiPolygon" then List.Zip({{1 .. listCount}, list}) else {},
            shapeCoordinates = List.Combine(List.Transform(shapesList, fnExtractPointsCoordinates)),
            toTable = if List.Count(shapeCoordinates) > 0 then Table.FromRecords(shapeCoordinates) else #table({"ShapeID", "Longitude", "Latitude"}, {}),
            changeTypes = Table.TransformColumnTypes(toTable,{{"ShapeID", Int64.Type}, {"Longitude", type number}, {"Latitude", type number}})
        in
            changeTypes,

    Source = Json.Document(File.Contents("C:\...\fichier.geojson")),
    RecupFeatures = Source[features],
    ToTable = Table.FromList(RecupFeatures, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ExpandDatas = Table.ExpandRecordColumn(ToTable, "Column1", {"properties", "geometry"}, {"properties", "geometry"}),
    ExpandProperties = Table.ExpandRecordColumn(ExpandDatas, "properties", {"ref:INSEE", "name"}, {"ref:INSEE", "name"}),
    TransformShapesCoordonates = Table.TransformColumns(ExpandProperties,{{"geometry", fnExtractGeometryInfos, type table}}),
    ExpandShapesCoordonates = Table.ExpandTableColumn(TransformShapesCoordonates, "geometry", {"ShapeID", "Longitude", "Latitude"}, {"ShapeID", "Longitude", "Latitude"}),
   
    AddColumnPlanedLongitude = Table.AddColumn(ExpandShapesCoordonates, "PlanedLongitude", each [Longitude]*Number.Cos([Latitude]*Number.PI/180)),
    CoordinatesArea = [MinLongitude=List.Min(AddColumnPlanedLongitude[PlanedLongitude]),
                       MaxLongitude=List.Max(AddColumnPlanedLongitude[PlanedLongitude]),
                       MinLatitude=List.Min(AddColumnPlanedLongitude[Latitude]),
                       MaxLatitude=List.Max(AddColumnPlanedLongitude[Latitude])],
    ShapeMaxArea = [MaxHeight=500, MaxWidth=750],
    ShapeArea = let ratioCoordinates = Number.Abs((CoordinatesArea[MaxLongitude]-CoordinatesArea[MinLongitude])/(CoordinatesArea[MaxLatitude]-CoordinatesArea[MinLatitude])) in
                if ratioCoordinates <= Number.Abs(ShapeMaxArea[MaxWidth]/ShapeMaxArea[MaxHeight])
                then [MaxWidth=ShapeMaxArea[MaxHeight] * ratioCoordinates, MaxHeight=ShapeMaxArea[MaxHeight]]
                else [MaxWidth=ShapeMaxArea[MaxWidth], MaxHeight=ShapeMaxArea[MaxWidth]/ratioCoordinates],
    CoefConvert = [aLat = -ShapeArea[MaxHeight] / (CoordinatesArea[MaxLatitude] - CoordinatesArea[MinLatitude]),
                   bLat = -aLat * CoordinatesArea[MaxLatitude],
                   aLong = ShapeArea[MaxWidth]/ (CoordinatesArea[MaxLongitude] - CoordinatesArea[MinLongitude]),
                   bLong = -aLong * CoordinatesArea[MinLongitude]],
    AddColumnTop = Table.AddColumn(AddColumnPlanedLongitude, "Top", each CoefConvert[aLat] * [Latitude] + CoefConvert[bLat]),
    AddColumnLeft = Table.AddColumn(AddColumnTop, "Left", each CoefConvert[aLong] * [PlanedLongitude] + CoefConvert[bLong]),
    SelectColumns = Table.SelectColumns(AddColumnLeft,{"ref:INSEE", "name", "ShapeID", "Longitude", "Left", "Latitude", "Top"})
in
    SelectColumns

Source = Json.Document(Web.Contents("https://data.explore.star.fr/api/explore/v2.1/catalog/datasets/communes-france/exports/geojson?lang=fr&timezone=Europe%2FBerlin")),

[out:xml][timeout:100];
area["ref:INSEE"="35"]->.a;
rel(area.a)["boundary"="administrative"]["admin_level"="8"];
out geom;
{{style:
node{width: 0;opacity:0;fill-opacity:0;}
}}
>;
is_in;
rel(pivot)
["admin_level"="5"];
out geom;

[out:xml][timeout:25];
rel(around:4000,47.47966,4.05892)["boundary"="administrative"]["admin_level"="8"];
{{style:
relation{text:name}
node{width: 0;opacity:0;fill-opacity:0;}
}}
out geom;