Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
547 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
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). |
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!
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).
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.
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!
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!
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!
Some other pages relevant to the above blog include:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.