Merging Linked Data from Similar Tables

I was recently asked how to link data from different tables but with similar column names to Visio shapes. In this case, each table has the same unique identifier, but some of the column names are the same. The problem is that the data linking matches the column name with the label of a Shape Data row label, so the shared columns were updated with the values from the last linked data recordset. In this article I present a simple way that this restriction can be overcome.

As the images above show, the solution is to rename some or all of the column names so that they can all be linked to the Visio shapes using Data | External Data | Custom Import or Quick Import. I don’t use Quick Import much because I prefer the better control that I can get with Custom Import for Excel tables. I have used Power Query in Excel via Data | Get & Transform Data | From Table/Range to create these examples, but it could also be done in, say, SQL Server to create Views or Stored Procedures.

In this example, new queries, qryX and qryY, are created from the source tables, tblX and tblY, and the non-unique id columns are renamed.

The first option is to link each these queries to Visio shapes separately as multiple data recordsets.

The second option is to merge qryX and qryY together to make a new query, qryXY, and link just this single data recordset to the Visio shapes.

The following images shows how these two options manifest themselves in the Visio UI. In each case, the shapes have five Shape Data rows, and they are both using the ID column as the primary key.

There could be operational reasons for choosing one option over the other, such as the frequency that each recordset gets updated and the diagram refreshed.

Related articles

Co-authoring and Commenting with Visio Documents

Microsoft Visio can be used in the web browser and on the desktop, but there are several licensing options available, so which ones can be used concurrently whilst supporting co-authoring and commenting? This article tests the various scenarios but assumes that the Visio documents are stored in OneDrive for Business or SharePoint Online\Teams. The Microsoft…

Linking Data to Shapes in Visio after using Data Visualizer

Data Visualizer (DV) in Visio Plan 2 (Data | Create from Data | Create ) is great because it provides a way of automatically creating a diagram from data, but it also prevents some of the other data-linking features in Visio from being used. This is because DV wants to take control of the data…

Using Visio Color by Value on Connectors

Data Graphics in Visio Plan 2 and Visio Professional is great, but it only enables us to use them with 2D shapes in Visio, i.e. not on connectors. So, what if you want to change the line colour of the connectors between the 2D shapes because of the data flowing between them? Well, it is…

Pushing Data Visualizer in Visio to the limits!

Regular readers of my blog will know that I like to use the Data Visualizer (DV) in Visio Plan 2, but I recently tried to help a user who really decided to push it to the limits. In this scenario, there were multiple connections, but with different labels, being created between the same flowchart shapes,…

Linking Data to Visio Shapes in Code

One of the most useful capabilities of Visio Professional and Visio Plan 2 is to link external data to shapes and have them refreshed by changes in the data source. So, many of my solutions involve writing code to make these links, and they are covered with some VBA examples in my book, Mastering Data…

Update any Visio ShapeSheet cell with External Data

When Microsoft introduced a new way of linking external data to Visio shapes in 2007, I initially bemoaned the inability to update anything but Shape Data row values, unlike the old database add-on that I had been using for 10 years. The new method, though, has many advantages over the old way, not least that…