Archive for May 2014
Last week someone asked me whether it was possible to do the equivalent of a SQL LIKE filter in Power Query. Unfortunately there isn’t a function to do this in the standard library but, as always, it is possible to write some M code to do this. Here’s what I came up while I was waiting around at the stables during my daughter’s horse-riding lesson. At the moment it only supports the % wildcard character; also I can’t guarantee that it’s the most efficient implementation or indeed 100% bug-free, but it seems to work fine as far as I can see…
Using the following test data:
I can run the following query:
And get this output:
You can download the sample workbook here.
I know the Power Query team have been asked for this several times already, but it would be really useful if we could package up functions like this and make it easy to share them publicly with other Power Query users…
When I’m working with XML files, or web pages, or any data with columns containing nested tables in Power Query, I often end up having to expand every expandable column in the table and then expanding any new columns that are revealed after that to find the data that I’m looking for. This is a such a pain I thought I’d write a function to do it for me – which is the subject of this post.
For example, consider the following XML:
If you load this into Power Query you will see the following table created for the first step:
To get to a table where all of the data is visible requires clicking on the expand icons in the address and the employees columns (highlighted), and then three more clicks after that. Sigh.
Here’s my function, called ExpandAll, to expand all the columns in a table that can be expanded:
You can then use this function on the XML file shown above as follows:
And bingo, in one step, you get everything:
You can download the sample workbook here.
It’s that time of year again: the BI Survey, the world’s biggest annual survey of BI users, is looking for your feedback. If you use any Microsoft BI tools they want to know what you think of them – and that applies to you too, Power Pivot/Power Query/Power View/Power BI fans!
Here’s the link to use:
If you take the survey you’ll get a summary of the results and a chance to win some Amazon gift vouchers. For promoting the survey here I get to blog in detail about the results when they come out, and the results are always interesting. I’m particularly looking forward to seeing what people will say about Power BI.
My last post on the new Power BI features announced at the PASS BA Conference was not much more than a list of bullet points written during the keynote. Now that the conference is over and I’ve had a bit more time to reflect, I thought I would try to come to some conclusions about what they all mean. And, of course, indulge in some wild speculation too.
Microsoft’s new-found enthusiasm for working on multiple platforms is clear from two things: first, the announcement that the iOS Power BI app will be coming soon (though we should not forget that this was promised a long, long time ago and is very late), followed by native apps for other mobile platforms; and secondly the work that has gone into the HTML 5 version of Power View. Indeed, the latter was demonstrated using Google Chrome to underline the point. Of course this is the only commercially sane direction to take but it’s very welcome nonetheless.
Power View new features
The cool new Data Exploration features in Power View, which allow you to edit existing Power View reports – creating new graphs and tables and merging existing ones – are I think only going to be available in HTML 5 Power View running inside a Power BI site. The same goes for the new time series forecasting functionality. The demos also seemed to make a point of the touch-friendly interface. Now I can’t imagine that Excel Power View will move from Silverlight to HTML 5 any time soon (the Office team are notoriously conservative when it comes to big changes like this) so maybe we should assume that, going forward, the main focus will be the use of Power View inside a Power BI site to build reports and dashboards rather than Power View inside Excel on the desktop? Maybe the HTML 5 version of Power View will be what is used in the touch-optimised version of Office that is slated to appear this summer? Who knows. I liked what I saw though and these additions will go a long way towards boosting Power View’s credibility as a client tool.
Time series forecasting
It seemed like Microsoft had abandoned ‘data mining for the masses’ after SSAS data mining failed to take off, but clearly not. The time series forecasting functionality seems very easy to use (you can read more about it here and here) and I got the impression that other algorithms might be added soon – maybe Project Sage is relevant here? Another question to ask is whether ease-of-use was the real reason why ‘data mining for the masses’ failed to take off the first time around? It might have been part of the reason but another factor must surely be that business users don’t trust predictions when they don’t understand how they are made, while the data scientists and statisticians are already using other tools that give them a lot more control for forecasting.
The first reaction of several people at the BA Conference (me included) to the new dashboard and KPI creation features in Power BI sites was that this was the replacement for PerformancePoint. Personally I never liked PerformancePoint much and rarely used it, and it doesn’t seem that MS has had much enthusiasm for it in recent years. I don’t think we’ll ever see it in the cloud either. Killing it off would have the added benefit of removing a client tool from a stack that has a confusing number of client tool options right now. However I got the impression that the dashboard and KPI creation features in Power BI sites, as demoed, were fairly basic and they may not be much more than widgets that can be placed on the Power BI home screen with nothing to tie them together, but I don’t know enough to judge properly. I think it might be better to think of Power View as the place to build dashboards.
Integration with old-school SQL Server BI
The ability of Power BI sites to host SSRS reports, and for Power View to connect back to SSAS on-prem, is an important bridge between what most of Microsoft’s BI customers are actually using and the new world of cloud-based Power BI that Microsoft is promoting. For me this was the biggest announcement of all. Will these customers be interested in buying Power BI licences for their users if SSRS and on-prem SSAS is all they want to use though? I don’t know, but I assume that this will also enable mobile access to SSRS and SSAS via the Power BI mobile apps, so that will be a plus for some customers, and all the new Power View functionality makes it quite an attractive web-based reporting tool for SSAS users. The per user cost of a Power BI licence might make it too expensive to buy if all you want are web-based dashboards and mobile support but customers who are already checking out Power BI for self-service will be more likely to buy because of this.
New features in Q&A were not mentioned in the keynote on Thursday, but on Friday afternoon I saw an interesting session that detailed some of the new functionality coming in Q&A later this year including the support for phrasing that is mentioned in this blog post. It’s clear that Q&A is getting a lot of love at MS and technically it is very impressive. I’m still not totally convinced that this is something people actually want or will use but I’m less cynical than I was. I also smell a lot of consultancy money in building and tuning models for Q&A if it does get popular (the session showed that there are a lot more features coming that will help with tuning). If I understand correctly, the output from Q&A is basically a Power View report which can then be edited manually if you wish; this means that Q&A should not be thought of as a standalone tool but as one of the ways that you can start to build a Power View report, and Q&A will benefit from all of the new features that are going to be added to Power View.
Apart from the time series forecasting, which is available now, ‘this summer’ was the most common response to all questions about when the new stuff would be available. Maybe all of these features will be released when the introductory pricing period for Power BI ends? Or in time for the Worldwide Partner Conference in mid July, as Jen Underwood suggests? Hopefully it won’t be in the middle of my summer holidays.
This morning I was wondering whether we’d see any cool new stuff announced at the PASS BA Conference this year, or whether we’d see the same old Power BI demos yet again. It turns out there were a whole load of announcements, some of them very cool indeed – and here’s a brief summary:
- A native Power BI mobile for iOS will be available by the end of the summer. Other platforms (which means Android I guess) will come soon after that.
- SSRS will be available in Power BI sites and will be able to connect back to on-prem data sources. This is big, in my opinion – it will be very attractive for a lot of existing MS BI customers. Also the way to get SSRS reports on mobile?
- Power View in Power BI will be able to connect back to SSAS on premises (just Tabular though, or Multidimensional too?)
- A new KPI editor in the Power BI site will allow you not only create KPIs but arrange them to create dashboards. This looks like the replacement for PerformancePoint.
- Time series forecasting is available in Power View online now. It’s available in charts and you just forecast by dragging the chart forward; outliers can be corrected easily. Will need to check this out later.
- Power View is getting a treemap visualisation.
- Power View is getting a new Data Exploration mode that allows you to edit reports in the browser. This has a lot of cool new stuff, such as the ability to drag data points out of existing charts to create new charts.
Lots to follow up on there… more blog posts on this coming soon, I promise!
Matt Masson and Theresa Palmer gave an excellent presentation on Power Query and M for the PASS DW/BI Virtual Chapter a few days ago (hopefully it will be on their YouTube channel soon). One thing that they showed which I hadn’t seen before was that you can display help for a function in M’s built in library simply by typing its name in the formula bar.
Take, for example, the Text.Replace() function. With a new blank query, if you create a step with the following definition:
You will see help and examples as shown below:
By doing this you are creating a step that returns the function itself – note that this is not the same as invoking the function, although it does mean you can invoke the function in a subsequent step. If you do decide to use the function you just need to click Invoke and a dialog will appear to prompt you for the values to pass to the function:
Clicking OK will show the output of the function:
Here’s the full M code for the query for those of you who are curious:
A very useful tip! Unfortunately you can’t specify help text for your own functions yet, although Matt did say it was something they wanted to do.