Browse 549 attributed reviews, viewable separately for our classroom and online training
Power Query Online now supports flow diagrams!
A life-changing (or at least job-changing) new software update from Microsoft - you can now see a visual view of data flows in Power BI (at least in the online version, anyway).

Posted by Andy Brown on 24 March 2021

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Power Query Online now supports flow diagrams!

Every now and again Microsoft introduce a software update which makes you think: yes, that's the future.  This is one such update.

Power Query Online

You can now see a visual representation of the steps in Query Editor (at least, you can in the online version).

With the ability to see data flows visually, Power Query is moving from a toy to being a full-blown ETL package!

What is Power Query Online, as opposed to Power Query?

If you're used to loading data into Excel or Power BI Desktop, you've probably learnt a bit about Power Query:

Power Query Desktop

Power Query allows you to transform data before you load it (it's sometimes called an ETL package, standing for Extract, Transform and Load).

So what's Power Query Online?  Starty by logging on to the Power BI Service in your browser, so that you can see workspaces that you've created:

Power BI workspaces

Some of the workspaces in our temporary account.

Within each workspace, you can then see reports that you've published:

Reports etc

You can also see dashboards that you've created online, as well as the datasets your reports are based on.

You can also create dataflows online, which is analogous to using Query Editor to load data in Power BI desktop:

Inserting a dataflow

Here I'm about to add a dataflow to my temporary workspace.

You can create a dataflow based upon original data or an existing dataflow that you've created:

Adding a dataflow

Here I'm going to base the dataflow on a SQL Server database, using a gateway Sam has set up.

I've missed out the next two steps (this isn't meant to be a tutorial), but if you have a gateway set up you can use it to load data in the usual way.  If you haven't got a gateway set up, you'll need to find another way to link to your data online.

You're now in Power Query Online!

Power Query Online

You can now choose which tables you want to load.

Diagram view

So initially things look just like vanilla Query Editor, until you switch to diagram view:

Diagram view switch

One way to do this is to click on the icon shown above.

Each query now shows as a separate flow:

Separate queries

You can click on the icon shown above to expand or collapse any query's steps.

I quite like the newly-added Compact view, which shows steps in a column rather than a line:

Compact view

How to turn compact view on and off.

Whether you prefer this is up to you:

Compact view example

The tables take up more vertical room but less horizontal space.

 

There's also a nice option to expand or collapse all of your queries:

Expand all

You can expand or collapse all your queries by clicking on this tool (the toolbar also allows you to zoom in and out, or to make everything fit on your screen).

Working with steps

The easiest way to add a step to the end of a query is now to click on the + symbol:

Adding a step

Click on this icon to add an additional step to (in this case) the Purchase table.

Because I've got a column selected, I'll see the option to remove a column:

Removing columns

You can see the relevant options given your current selection.

You now get a cute little extra icon:

Remove columns step

My extra step.

I can even rename this in situ if I switch to showing the names of my steps, not their standard labels:

Step names

You can choose either to show step labels (the default) or the names you've given to each step.

 

I can then double-click on any name to change it to anything I like:

Renaming a step

Here I'm in the middle of changing this step's name.

Working with multiple queries

Where the new visual diagrams really come into their own is when you want to combine queries.  You can use the Ctrl key to select more than one query at a time:

Selecting multiple queries

Here I've got the Region and Town tables selected (I'm going to merge them).

If you right-click, you see a menu of things you can do when you have more tha one query selected:

Merge queries as new

I'm going to merge the two queries to make one new one, using the option shown selected.

I was going to miss out the details of this, until I saw the funky join diagrams at the bottom of the dialog box which appears:

Joining two tables

Why can't the desktop version of Query Editor have these nice diagrams!!

The diagram now shows me how data is flowing!

Data flow diagram

You can see that the query on the right depends on the two to the left.

Just in case you were in any doubt, you could choose to highlight related queries for the one on the right (say):

Highlight related queries

You can click on this icon to show all of the queries which are linked to this one.

Here's what this shows in this instance:

Selected linked queries

I imagine this feature would be most useful for more complicated flow diagrams!

Limitations of Power Query Online

This should really be "Limitation", as there's one glaring omission from the first released version of the software:

Moving a table

Want to move the Purchase table a bit to the right? You can't!

I'm sure Microsoft are feverishly working on this feature.  Those who have used similar packages like Integration Services or Alteryx will know how useful it is to be able to move boxes round the screen and have the connecting lines follow!

Find out more

You can see more details on the new Power Query Online diagram view at these two Microsoft sites:

We suspect that in a few months (once the vanilla Query Editor is given these new features) you'll also be able to book onto a two-day Wise Owl Query Editor course!

This blog has 0 threads Add post