Search code examples
powerquerym

Power Query to Convert List of Links to Grid of Crossings

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
Solution

  • 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

    1. Create a secondary table and reorder the columns in the opposite way that the original table, so Tag, Point-B and Point-A.
    2. 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.
    3. Pivot on column Point-B without aggregating data.
    4. 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