Seven years ago I gave a presentation at SQLBits called “Building a reporting solution using Power Query”. You can watch the recording here:
https://sqlbits.com/Sessions/Event14/Building_A_Reporting_Solution_Using_Power_Query
In it I showed how you could build a simple reporting solution using just Excel and Power Query, loading data into tables, handling parameterisation, making sure you get the best performance and so on. I think the session holds up pretty well: the functionality I showed hasn’t changed at all, and while in the meantime Power BI has reinvented itself and taken over the world I still think there’s a strong argument for using Excel plus Power Query instead of Power BI for some reporting scenarios (although it may be heresy to say so…).
If you follow the Excel blog you’ll know there have been a number of exciting announcements in the last few months, so I thought it would be interesting to take a look at some of them and consider the impact they have for BI and reporting use cases.
Power Query in Excel for the Mac
One of the priorities for the Excel Power Query team has been to get Power Query working in Excel on the Mac, and in the latest update we now have the Power Query Editor available. Data sources are still limited to files (CSV, Excel, XML, JSON), Excel tables/ranges, SharePoint, OData and SQL Server but they are some of the most popular sources. I’m not a Mac person so this doesn’t excite me much, but this does open up Power Query to a new demographic that has traditionally ignored Microsoft BI; for example, I was leafing through John Foreman’s excellent introductory data science book “Data Smart” recently and all the examples in it are in Excel to reach a mass audience, but… Excel for the Mac.
Power Query in Excel Online
This, on the other hand, is something I do care about: who cares what OS you’re running if you can do everything you need in the browser? Well now you can refresh Power Query in Excel Online, although again only a few data sources are supported at the moment: data in tables/ranges in the current workbook, or anonymous OData feeds. More data sources will be supported in the future and there will also be better integration with Office Scripts, so you’ll be able to refresh queries from Power Automate or via a button without needing VBA; you’ll also be able use the Power Query Editor in the browser too.
Before you get too excited about Power Query in Excel Online, though, remember one important difference between it and a Power BI report or a paginated report. In a Power BI report or a paginated report, when a user views a report, nothing they do – slicing, dicing, filtering etc – affects or is visible to any other users. With Power Query and Excel Online however you’re always working with a single copy of a document, so when one user refreshes a Power Query query and loads data into a workbook that change affects everyone. As a result, the kind of parameterised reports I show in my SQLBits presentation that work well in desktop Excel (because everyone can have their own copy of a workbook) could never work well in the browser, although I suppose Excel Online’s Sheet View feature offers a partial solution. Of course not all reports need this kind of interactivity and this does make collaboration and commenting on a report much easier; and when you’re collaborating on a report the Show Changes feature makes it easy to see who changed what.
More flexibility with Power Query data types
Being the kind of person who stores their data in Power BI I didn’t do much with Power Query data types when they were released; after all, you can create Organisation data types to access Power BI data from Excel and I prefer using Excel cube functions anyway. However if you’re not using Power BI then I can see how Power Query data types could be really useful for building reports that go beyond big, boring tables, making it much easier to create more complex report layouts.
Power Query connector for Power BI dataflows and Dataverse
Lastly, the feature I’m most excited about: the ability to load data from Power BI dataflows and Dataverse into Excel via Power Query. It’s not available yet although I promise it’s coming very soon! The ability to share cleaned and conformed data via dataflows direct to those Excel users who just want a data dump (rather than using Analyze in Excel on a Power BI dataset) will prove to be extremely popular, I think. There are a lot of improvements to dataflows coming soon too (you do remember to check the release notes regularly, don’t you?).
Conclusion
Overall it’s clear that Excel Power Query is getting better and better. It may never be able to keep pace with Power BI (what can?) but all these new features show that, for people who prefer to do everything in Excel, it’s making Excel a much better place to build reports. I feel like I need to update my SQLBits presentation now!
Can you fix the link to “Data Smart”? … the link indicates you wanted to redirect to “John Foreman’s excellent introductory data science book Data Smart”
Done – thanks for spotting that
Chris the dataflows improvements (“next generation” in July) are really exciting.
Do you know if dataflows still store data in CSV as the output format? It says there is support for loading dataflow output to new destinations, but I was wondering if we are likely to see new output formats from dataflows. I would be happy to know that it was using parquet under the hood for the sake of performance. (Frankly any well-structured binary format would make me happy, esp. parquet or sqlite or something along those lines, so that they could be easily downloaded and inspected).
Any improvements we can get to dataflows are most welcome. When I first started using them, it was an other-worldly experience. Most people would expected them to work like a dataset PQ. My biggest problems (1. “validating”… 2. only having a subset of data source types 3. no custom connectors 4. no ability to set parameters that influence the behavior of the dataflow 5. no ability to use personal gateway, etc…) I’ve largely avoided dataflows wherever possible; although I certainly love the idea of them. I especially like the idea of computed entities, and I like the possibilities of sticking data back into cheap storage, rather than always loading it directly into memory-resident tabular datasets.
I can’t say anything more than what’s in the release notes, I’m afraid…
Tell you what, the roadmap document says that in September…
” you’ll be able to load your data into any Azure SQL database by using dataflows. You’ll be able to create new tables or load to existing tables with mapping. There will also be options to control the update methods (append, replace) for loading, so you’ll get full flexibility.”
If that is indeed the case, I am going to cancel my family holiday to Barbados and spend all of September dataflowing straight into Azure SQL dB. Nirvana will have been reached.
Hooray for being able to connect to dataflows in Excel. Its one of those weird little things that seems so useful and simple and obvious, that I’m always baffled that it’s not there already. No kidding; about twice a month, I think “Oh, then I’ll just load the dataflow into Excel…hold on, is that *still* not possible?”. So nice one Excel guys, I’ll use the heck out of that on day 1.
I am so happy I can connect to dataflows in Excel. I use PowerPlatform.Dataflows() in my updated Excel version and it works! I think Excel + Power Query will be the preferred solution over Power BI in some scenario’s in my organization. And I am surprised so many people still don’t know that Power Query in Excel exists and how powerful it can be. I am glad you spread the word.
any updates to this?