Top 2 Features of Power Query
Power Query is one of the 4 add-ins for Excel which integrate with Power BI for Office 365. I'd like to share what I see are the 2 biggest features of Power Query.
Repeatable Steps
We've heard lots of times that cleaning up data to get it in the correct format can take many hours, and sometimes this work has to be done routinely such as every month. Here's the perfect situation where Power Query can help.
Each step you perform to change the data in some aspect is shown in the "Applied Steps" pane. If you click on each step, you can actually see the state of the data before and after each step was applied.
What makes this ultra cool? The fact that these steps are repeatable the next time you need to go through the same exercise. This is similar to the idea of an Excel macro, but much easier to use.
To me, the time savings and efficiency of repeating steps to cleanse and restructure data is *the* biggest benefit of Power Query.
Shared Queries
One of the inherent downsides to asking business users to publish their own datasets and reports is that the quality and integrity of the data can be questionable depending upon knowledge of the person preparing the data and how thoroughly it's been verified. There's also an increased chance of duplicating work that's already been done by someone else which isn't efficient. Here's where sharing Power Queries comes in.
If you have a Power BI license, and you are signed in to Power BI from within Excel, you can share a Power Query (once it's complete & validated of course).
When you share a Power Query, the name and description become all searchable. You can also specify who to share the query with - this would usually include other people, but you could just specify yourself if that makes the most sense. And, you can even specify a URL with more information about the query (which is a truly excellent feature that I fear will be underutilized).
After it's been shared, you and other users can discover this query when using the Online Search functionality in Power Query. You would search for "Organization" data to discover a shared Power Query.
When you are logged into the Power BI for O365 app (via a web browser), if you click on "My Power BI" at the top right you will be able to view how often your shared queries show up in search + how often your queries are selected for use and by whom.
What's really great about sharing Power Queries is the underlying logic is done just once and repeated potentially over and over - and you have visibility into who else is using your shared queries - now that takes things to the next level.