Power Query to Convert List of Links to Grid of Crossings

Question

In Excel I have a data table of Paired Items that are tagged with an identifier. Essentially, named linkages.

Worksheet: Links

Tag	Point-A	Point-B
Route 1	Home	Office
Route 2	Home	Grocery 1
Happy Hour	Office	Bar
Sad Hour	Office	Dump
Headaches	Bar	Pharmacy
Sick	Bar	Dump
Route 3	Office	Moms
Route 4	Office	Park
Victory	Park	Bar
Discard	Park	Dump

I want to transform this data into a grid of all points in rows and columns with the tag placed at the intersection (Much like old paper road maps with grids for city distances)

Worksheet: Grid

A \ B	Bar	Dump	Grocery 1	Home	Home	Moms	Office	Office	Park	Pharmacy
Bar		Sick					Happy Hour		Victory	Headaches
Dump	Sick						Sad Hour		Discard
Grocery 1					Route 2
Home							Route 1
Home			Route 2
Moms							Route 3
Office	Happy Hour	Sad Hour		Route 1		Route 3
Office									Route 4
Park	Victory	Discard						Route 4
Pharmacy	Headaches

I have written the following M code for transforming, but it seems a bit wayward and overwrought. I am using bit coding of points to construct a join key, so the bitting process will probably break around 32 points.

Is there a shorter set of LETs that do the same transform to grid ?

Is there a way to create a key that is Min(Point-A,Point-B) delimited concatenation with Max(Point-A,Point-B), and thus not rely of bitting?

M code (copied from Advanced Editor)

let
    LinksTable = Table.SelectRows(Excel.CurrentWorkbook(), each [Name] = "Links"),
    Links = Table.RemoveColumns(Table.ExpandTableColumn(LinksTable, "Content", {"Tag", "Point-A", "Point-B"}), "Name"),
    AllPoints = Table.Combine(
    { Table.SelectColumns(Table.RenameColumns(Links,{"Point-A", "Point"}), "Point"),
      Table.SelectColumns(Table.RenameColumns(Links,{"Point-B", "Point"}), "Point")
    }),
    ThePoints = Table.Sort(Table.Distinct(AllPoints),{"Point"}),
    PointsIndexed = Table.AddIndexColumn(ThePoints, "Index", 0, 1, Int64.Type),
    PointsBitted = Table.RemoveColumns(Table.AddColumn(PointsIndexed, "Bit", each Number.Power(2, [Index]), Int64.Type),"Index"),
    AllPairsBitted = Table.Join(
      Table.RenameColumns(PointsBitted, {{"Point", "Point-A"}, {"Bit", "Bit-A"}}), {},
      Table.RenameColumns(PointsBitted, {{"Point", "Point-B"}, {"Bit", "Bit-B"}}), {},
      JoinKind.FullOuter
    ),
    AllPairsKeyed = Table.RemoveColumns(
      Table.AddColumn(AllPairsBitted, "BitKeyPair", each Number.BitwiseOr([#"Bit-A"],[#"Bit-B"])),
      { "Bit-A", "Bit-B"}
    ),    
    #"Links-A-Bitted" = Table.Join(
    Links, "Point-A",
    Table.RenameColumns(PointsBitted,{{"Point", "Point-A"}, {"Bit", "Bit-A"}}), "Point-A"
  ),
    #"Links-AB-Bitted" = Table.Join(
    #"Links-A-Bitted", "Point-B",
    Table.RenameColumns(PointsBitted,{{"Point", "Point-B"}, {"Bit", "Bit-B"}}), "Point-B"
  ),
    LinksKeyed = Table.RemoveColumns(
      Table.AddColumn(#"Links-AB-Bitted", "BitKeyLink", each Number.BitwiseOr([#"Bit-A"],[#"Bit-B"])),
      { "Bit-A", "Bit-B"}
    ),
    AllPairsTagged = Table.Sort( Table.RemoveColumns(
    Table.Join(
      AllPairsKeyed, "BitKeyPair",
      Table.SelectColumns(LinksKeyed, {"BitKeyLink", "Tag"}), "BitKeyLink",
      JoinKind.LeftOuter
    ),
    {"BitKeyPair", "BitKeyLink"}
  ),
  {"Point-A", "Point-B"}
),
    Grid = Table.Pivot(AllPairsTagged, List.Distinct(AllPairsTagged[#"Point-B"]), "Point-B", "Tag", List.First)
in
    Grid

Angelo Canepa Angelo Canepa · Accepted Answer · 2021-03-24T07:39:34

I think you can use PIVOT to achieve this. Using directly this functionality would not work because you are looking for symmetry of columns and rows.

The trick is to force that symmetry, appending values from Point-B into values of Point-A.

Steps

Create a secondary table and reorder the columns in the opposite way that the original table, so Tag, Point-B and Point-A.
On the secondary table, rename the columns to Tag, Point-A and Point-B in that order. Append usually take column names literally, so without renaming it would append the names of the same columns.
Pivot on column Point-B without aggregating data.
Reorder the columns using Point-A as a reference, so you have symmetry of columns and rows.

It's worth mentioning that's good practice to Buffer the source table because is used multiple times across the calculations.

Calculation

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc69CsMgEMDxVwnOLv14ghKoS2looEvIcJwXIkEMZxx8+6axiUInwd/99bpOvFxYqDoJKZSztB7PYTBIope7nbPd2SFxXMe/rGCeY6Vc4JxJcQPetAX9Z3Wwc0oJNOBI/hdI0YzAFjCm1uB0yBGldS7lgw9nfWHX0hrgabO3wcVx3K/yirXxCKwzpK/6Dw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Tag = _t, #"Point-A" = _t, #"Point-B" = _t]),
    BufferedSource = Table.Buffer(Source),
    SecondTable = Table.ReorderColumns(BufferedSource,{"Tag","Point-B","Point-A"}),
    SecondTableRenameCols = Table.RenameColumns(SecondTable,{{"Point-A","Point-B"},{"Point-B","Point-A"}}),
    AppendTables = Table.Combine({BufferedSource,SecondTableRenameCols}),
    PivotTables = Table.Pivot(AppendTables, List.Distinct(AppendTables[#"Point-B"]), "Point-B", "Tag"),
    ReorderCols = Table.ReorderColumns( PivotTables, PivotTables[#"Point-A"])

in ReorderCols

Output

Point-A	Bar	Dump	Grocery 1	Home	Moms	Office	Park	Pharmacy
Bar		Sick				Happy Hour	Victory	Headaches
Dump	Sick					Sad Hour	Discard
Grocery 1				Route 2
Home			Route 2			Route 1
Moms						Route 3
Office	Happy Hour	Sad Hour		Route 1	Route 3		Route 4
Park	Victory	Discard				Route 4
Pharmacy	Headaches

Power Query to Convert List of Links to Grid of Crossings

1 Answers

Steps

Calculation

Output