Geometry.FromWellKnownText - Record Function

Geometry.FromWellKnownText is a Power Query M function that translates text representing a geometric value in Well-Known Text (WKT) format into a structured record. The function returns a record representing the geometric value.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

Geometry.FromWellKnownText( input as nullable text ) as nullable record

Description

Geometry.FromWellKnownText interprets geometric data encoded in WKT format and transforms it into a structured record. This function allows geometric data, typically used in spatial databases including SQL Server, to be utilized within Power Query, facilitating geometric calculations and transformations.

Examples

Conversion from POINT Format

In the world of geometric data, a point is defined by its X and Y coordinates. Using Geometry.FromWellKnownText, a WKT POINT format is converted into a structured Power Query record that clearly outlines its type and coordinates.

// Output:  [ Kind = "POINT", X = 10, Y = 20 ]
Geometry.FromWellKnownText( "POINT(10 20)" )

This output represents a specific location in a two-dimensional space, using X and Y values instead of longitude and latitude as in Geography.FromWellKnownText.

Conversion from LINESTRING Format

When working with a sequence of points that form a line, the LINESTRING format is used. Geometry.FromWellKnownText transforms a WKT LINESTRING into a sequence of geometric points within Power Query.

Geometry.FromWellKnownText( "LINESTRING(10 10, 20 20, 21 30)" )

The output mirrors that of its geographic counterpart but uses X and Y coordinates, showcasing the linear connection between these points in a planar space.

[
  Kind   = "LINESTRING", 
  Points = 
  {
    [ Kind = "POINT", Longitude = 10, Latitude = 10 ], 
    [ Kind = "POINT", Longitude = 20, Latitude = 20 ], 
    [ Kind = "POINT", Longitude = 21, Latitude = 30 ]
  }
]

Conversion from POLYGON Format

A polygon in geometric data is defined by a series of points that create a closed shape. The transformation of a WKT POLYGON into a structured Power Query record using Geometry.FromWellKnownText emphasizes these defining points.

Geometry.FromWellKnownText( "POLYGON((0 0, 0 40, 40 40, 40 0, 0 0))" )

This output visualizes a polygon through a series of X and Y points.

[
  Kind  = "POLYGON", 
  Rings = 
  {
    [
      Kind   = "LINESTRING", 
      Points = 
      {
        [ Kind = "POINT", Longitude = 0,  Latitude = 0  ], 
        [ Kind = "POINT", Longitude = 0,  Latitude = 40 ], 
        [ Kind = "POINT", Longitude = 40, Latitude = 40 ], 
        [ Kind = "POINT", Longitude = 40, Latitude = 0  ], 
        [ Kind = "POINT", Longitude = 0,  Latitude = 0  ]
      }
    ]
  }
]

Conversion from MULTIPOINT Format

The MULTIPOINT format represents a collection of independent points. When transformed using Geometry.FromWellKnownText, each point defined by its X and Y coordinates is structured into a cohesive MULTIPOINT record in Power Query.

Geometry.FromWellKnownText( "MULTIPOINT((0 0), (10 20), (15 20), (30 30))" )

Its output (shown below) organizes each distinct point into a larger structure, allowing you to store multiple geometric points within a single, unified data structure.

[
  Kind       = "MULTIPOINT", 
  Components = 
  {
    [ Kind = "POINT", Longitude = 10, Latitude = 10 ], 
    [ Kind = "POINT", Longitude = 10, Latitude = 20 ], 
    [ Kind = "POINT", Longitude = 15, Latitude = 20 ], 
    [ Kind = "POINT", Longitude = 30, Latitude = 30 ]
  }
]

Conversion from MULTILINESTRING Format

The MULTILINESTRING format is used for representing multiple, interconnected lines. Using Geometry.FromWellKnownText, this format is converted into a Power Query record, where each line is depicted as a series of points with X and Y values.

Geometry.FromWellKnownText( "MULTILINESTRING((10 10, 20 20), (15 15, 30 15))" )

The resulting structure, as shown below, captures the essence of multiple lines, each defined by its points.

[
  Kind       = "MULTILINESTRING", 
  Components = 
  {
    [
      Kind   = "LINESTRING", 
      Points = {
        [ Kind = "POINT", Longitude = 10, Latitude = 10 ], 
        [ Kind = "POINT", Longitude = 20, Latitude = 20 ]
      }
    ], 
    [
      Kind   = "LINESTRING", 
      Points = 
      {
        [ Kind = "POINT", Longitude = 15, Latitude = 15 ], 
        [ Kind = "POINT", Longitude = 30, Latitude = 15 ]
      }
    ]
  }
]

Conversion from MULTIPOLYGON Format

For intricate polygonal shapes, the MULTIPOLYGON format is used. Geometry.FromWellKnownText transforms this format into a structured MULTIPOLYGON record within Power Query, reflecting the complex relationships of its constituent polygons.

Geometry.FromWellKnownText( 
  "MULTIPOLYGON (((
  4.37151083818814 51.8058337,
  4.44772770816376 51.9716711044139, 
  4.47281312478682 51.8948221207453,
  4.47445171328713 51.8061076713834,
  4.47438826214634 51.8058337,
  4.37151083818814 51.8058337)))"
 )

The output (provided below) contains multiple polygons, each represented by a series of X and Y points. It shows how Power Query can store intricate geometric shapes into a structured and analyzable format.

[
  Kind       = "MULTIPOLYGON", 
  Components = 
  {
    [
      Kind  = "POLYGON", 
      Rings = 
      {
        [
          Kind   = "LINESTRING", 
          Points = 
          {
            [ Kind = "POINT", Longitude = 4.371510838, Latitude = 51.8058337  ], 
            [ Kind = "POINT", Longitude = 4.447727708, Latitude = 51.9716711  ], 
            [ Kind = "POINT", Longitude = 4.472813125, Latitude = 51.89482212 ], 
            [ Kind = "POINT", Longitude = 4.474451713, Latitude = 51.80610767 ], 
            [ Kind = "POINT", Longitude = 4.474388262, Latitude = 51.8058337  ], 
            [ Kind = "POINT", Longitude = 4.371510838, Latitude = 51.8058337  ]
          }
        ]
      }
    ]
  }
]

Conversion from GEOMETRYCOLLETION Format

The GEOMETRYCOLLECTION format, which combines various geometric shapes from points to linestrings, is also handled by Geometry.FromWellKnownText.

Geometry.FromWellKnownText( 
  "GEOMETRYCOLLECTION(
     POINT (10 10), 
     POINT (30 30), 
     LINESTRING (15 15, 20 20))"
 )

The output of this transformation (shown below) presents a versatile record, containing different geometric types within a single collection, all defined using X and Y coordinates.

[
  Kind       = "GEOMETRYCOLLECTION", 
  Components = 
  {
    [ Kind = "POINT", Longitude = 10, Latitude = 10 ], 
    [ Kind = "POINT", Longitude = 30, Latitude = 30 ], 
    [
      Kind   = "LINESTRING", 
      Points = 
      {
        [ Kind = "POINT", Longitude = 15, Latitude = 15 ], 
        [ Kind = "POINT", Longitude = 20, Latitude = 20 ]
      }
    ]
  }
]

Learn more about Geometry.FromWellKnownText in the following articles:

Power Query Geography And Geometry Functions In Power BI And Excel
The article delves into Power Query functions for handling geographic and geometric data in Power BI and Excel, offering practical examples of their use in converting and managing Well Known Text format. » Read more
Working with Geospatial Data in Power BI
The article provides a guide on using geospatial data in Power BI, including converting geospatial data for use in visualizations, extracting longitude and latitude from WKT data, and employing the icon-map visualization to render complex geospatial shapes. It covers the essentials from converting data types to extracting coordinates and using custom visuals for enhanced mapping capabilities. » Read more

Other functions related to Geometry.FromWellKnownText are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/geometry-fromwellknowntext