Power BI中使用Power Query拼接ESRI JSON geometry rings为POLYGON格式

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],
    // 转为表同时展开属性和几何字段，避免丢失业务属性
    #"转换为表" = Table.FromList(features, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"展开要素根节点" = Table.ExpandRecordColumn(#"转换为表", "Column1", {"attributes", "geometry"}, {"attributes", "geometry"}),
    #"展开属性字段" = Table.ExpandRecordColumn(#"展开要素根节点", "attributes", Record.FieldNames(#"展开要素根节点"{0}[attributes])),
    #"展开几何rings字段" = Table.ExpandRecordColumn(#"展开属性字段", "geometry", {"rings"}, {"geometry.rings"}),
    // 核心逻辑：拼接WKT格式的POLYGON字符串
    #"添加WKT列" = Table.AddColumn(#"展开几何rings字段", "PolygonWKT", (row) => 
        let
            // 取出当前要素的所有环
            rings = row[geometry.rings],
            // 遍历每个环，拼接环内的坐标点
            formatRings = List.Transform(rings, (ring) => 
                let
                    // 每个坐标对拼接为"lon lat"格式
                    coordList = List.Transform(ring, (coord) => Text.From(coord{0}) & " " & Text.From(coord{1})),
                    // 所有坐标用空格拼接
                    ringStr = Text.Combine(coordList, " ")
                in
                    ringStr
            ),
            // 多环用逗号分隔，外层套POLYGON格式
            wkt = "POLYGON((" & Text.Combine(formatRings, ", ") & "))"
        in
            wkt
    ),
    // 可选：删除不需要的原始几何列
    #"删除冗余列" = Table.RemoveColumns(#"添加WKT列", {"geometry.rings"})
in
    #"删除冗余列"

• 保留了原始要素的所有属性字段，可直接用于和Power BI内其他数据集关联
• 新增的PolygonWKT列直接输出符合Power BI地图识别标准的WKT多边形格式，无需额外转换
• 兼容单环、多环多边形场景，可自适应任意数量的坐标点
• 输出结果可直接导入Power BI形状地图视觉对象使用

内容的提问来源于stack exchange，提问作者mkmitchell