Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi there,
"I have an issue in Power Query. Is it possible to checkpoint XY coordinates within a multipolygon in Power Query? If we find a point in the polygon, can we show the name of the polygon it belongs to? I have tried using my M code, but I only found a single point."
let
// Load the point data
Source_Point = Excel.CurrentWorkbook(){[Name="Point"]}[Content],
pointX = Source_Point{0}[PointX],
pointY = Source_Point{0}[PointY],
// Load the polygon data
Source_Polygon = Excel.CurrentWorkbook(){[Name="Polygon"]}[Content],
polygonTable = Table.SelectColumns(Source_Polygon, {"VertexX", "VertexY"}),
polyX = List.Transform(Table.Column(polygonTable, "VertexX"), each _),
polyY = List.Transform(Table.Column(polygonTable, "VertexY"), each _),
// Determine the bounding box of the polygon
minX = List.Min(polyX),
minY = List.Min(polyY),
maxX = List.Max(polyX),
maxY = List.Max(polyY),
// Check if the point is inside the bounding box
result = minX <= pointX and pointX <= maxX and minY <= pointY and pointY <= maxY,
#"Converted to Table" = #table(1, {{result}})
in
#"Converted to Table"
That should be possible - please provide some sample data .
// Check if the point is inside the bounding box
I would be really careful with that assumption. Polygons can have all kinds of crazy shapes - like a crescent moon. The point may be inside the bounding box but outside the polygon.
Dear Ibendlin,
This is an example dataset.
i have point and polygon data when i use the code
let
// Load the point data
Source_Point = Excel.CurrentWorkbook(){[Name="Point"]}[Content],
// Load the polygon data
Source_Polygon = Excel.CurrentWorkbook(){[Name="Polygon"]}[Content],
polygonTable = Table.SelectColumns(Source_Polygon, {"PolygonName", "VertexX", "VertexY"}),
// Extract X and Y coordinates into lists
polyX = List.Transform(polygonTable[VertexX], each _),
polyY = List.Transform(polygonTable[VertexY], each _),
// Function to check if a point is inside any polygon
IsInsideAnyPolygon = (pointX, pointY) =>
List.AnyTrue(
List.Transform(
{0..List.Count(polyX)-1},
each
let
currentX = polyX{_},
currentY = polyY{_},
nextX = if _ = List.Count(polyX)-1 then List.First(polyX) else polyX{_+1},
nextY = if _ = List.Count(polyY)-1 then List.First(polyY) else polyY{_+1},
oddNodes = (currentY < pointY and nextY >= pointY or nextY < pointY and currentY >= pointY) and (currentX <= pointX or nextX <= pointX),
isInside = oddNodes
in
isInside
)
),
// Add a new column to the point data to check if it's inside any polygon
ResultTable = Table.AddColumn(
Source_Point,
"IsInsideAnyPolygon",
each IsInsideAnyPolygon([PointX], [PointY])
),
// If the point is inside any polygon, show the actual PolygonName; otherwise, show "OutsidePolygon"
Result = Table.AddColumn(
ResultTable,
"PolygonName",
each if [IsInsideAnyPolygon] then List.FirstN(Table.Column(polygonTable, "PolygonName"), 1) else {"OutsidePolygon"}
),
FinalResult = Table.SelectColumns(Result, {"PointX", "PointY", "IsInsideAnyPolygon", "PolygonName"}),
#"Extracted Values" = Table.TransformColumns(FinalResult, {"PolygonName", each Text.Combine(List.Transform(_, Text.From)), type text})
in
#"Extracted Values"
I have point and polygon data. When I use the code, the result shows that "IsInsideAnyPolygon" is correct, but in the polygon name, only "Polygon1" is displayed. In fact, for points 6,6 and 8,8, they should be inside "Polygon2."
Neither of your polygons are closed, and the polygons are idealized. You may want to use more realistic sample data.
Have you considered implementing a Ray casting algorithm?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.