Data Visualizer for #Visio Pro for Office 365 – Part 2

I walked though the steps to create a Cross-Functional flowchart automatically from an Excel table in my last article ( Data Visualizer for #Visio Pro for Office 365 – Part 1 ), but I often prefer to use a relational data source, such as Access or SQL Server, to store and organise my data. Therefore, I describe how Excel‘s Get Data (nee PowerQuery) can be used to merge together the data in a relational data source as a single table suitable for Data Visualizer in Visio Pro for Office 365.

This slideshow requires JavaScript.

It is easier to explain in a video, but the key elements are Pivot and Merge….

In summary, in Microsoft Access, I normalised the process map table in the Microsoft provided Excel template. The minimum related tables are one for each step in the process, and one to hold the relationship between each step, along with any optional text.

I then used Get Data in Excel to load the process steps table, and the related steps table. I used Transform / Pivot Column and Merge Columns to get the related steps into two flattened queries, both keyed on the source step id, but with one query concatenating the target step ids separated by commas, and the other query concatenating the connector labels separated by commas. These two queries were then merged back with the all of the source steps, to provide a flattened query listing all of the steps along with one column of each target step id, and one with the text to be used on a connector to the next step.


One Response to “Data Visualizer for #Visio Pro for Office 365 – Part 2”

  1. Data Visualizer for #Visio Pro for Office 365 – Part 1 | bVisual - for people interested in Microsoft Visio Says:

    […] Next Part : Data Visualizer for #Visio Pro for Office 365 – Part 2 […]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.


O365 and SharePoint

Simplify Tasks

Want to learn the simple way?

Paul Turley's SQL Server BI Blog

sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009

John Goldsmith's visLog

be smart, be clear, be visual ...

Mo's blog

Personal views on Dynamics 365 for Operations and Technical Architecture.

Chris Webb's BI Blog

Microsoft Power BI, Analysis Services, MDX, DAX, M, Power Pivot and Power Query

Azure Solutions for Office 365, and more...

Rob Fahrni



Life with Visio and other Microsoft Toys!

Nilsandrey's Weblog

Just another weblog

Things that Should be Easy

Every so often (too often in the IT industry) I encounter things that should have been very easy to do but turned out to be far too complicated. My favorite topics include SharePoint, .Net development, and software architecture, especially distributed systems.

Visio Guy

Smart graphics for visual people

%d bloggers like this: