BLOGS BY TOPIC▼
BLOGS BY AUTHOR▼
BLOGS BY YEAR▼
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.
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 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:
Some of the workspaces in our temporary account.
Within each workspace, you can then see reports that you've published:
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:
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:
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!
You can now choose which tables you want to load.
So initially things look just like vanilla Query Editor, until you switch to diagram view:
One way to do this is to click on the icon shown above.
Each query now shows as a separate flow:
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:
How to turn compact view on and off.
Whether you prefer this is up to you:
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:
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:
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:
You can see the relevant options given your current selection.
You now get a cute little extra icon:
My extra step.
I can even rename this in situ if I switch to showing the names of my steps, not their standard labels:
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:
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:
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:
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:
Why can't the desktop version of Query Editor have these nice diagrams!!
The diagram now shows me how data is flowing!
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):
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:
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:
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!