Microsoft Analysis Services, MDX, DAX, Power Pivot, Power Query and Power BI

NOTE: This post was written before Data Explorer was renamed as Power Query. All of the content is still relevant to Power Query.

The more I play with Data Explorer, the more I think my initial assessment of it as a self-service ETL tool was wrong. As Jamie pointed out recently, it’s really the M language with a GUI on top of it and the GUI itself, while good, doesn’t begin to expose the power of the underlying language: I’d urge you to take a look at the Formula Language Specification and Library Specification documents which can be downloaded from here to see for yourself. So while it can certainly be used for self-service ETL it can do much, much more than that…

In this post I’ll show you an example of what Data Explorer can do once you go beyond the UI. Starting off with a text file containing the complete works of William Shakespeare (which can be downloaded from here – it’s strange to think that it’s just a 5.3 MB text file) I’m going to find the top 100 most frequently used words and display them in a table in Excel.

Before I do that, though, some things to point out. First, there’s a new update of Data Explorer that appeared a few days ago – the Data Explorer team blog has the details. One of the new bits of functionality is a button that allows you to edit all of the expressions in your query at once:

Second, when you’re building a query, when you want to add a new step manually that refers to the previous step but doesn’t apply any calculations or transforms, you need to click on the fx button next to the expression on an existing step:

I’ve used this quite extensively to write custom steps that aren’t possible with the UI.

Does a ‘group by’ to return a table with one row for each distinct word, and a column containing the number of occurrences of that word:

SortedRows = Table.Sort(FindWordCounts,{{“Count”, Order.Descending}})

Sorts this table in descending order by word count.

KeptFirstRows = Table.FirstN(SortedRows,100)

Only returns the first 100 rows from this table.

And at this point, we have the top 100 words used by Shakespeare (admittedly including the text at the beginning and end of the file inserted by Project Gutenberg that I couldn’t be bothered to remove):

No surprises here really – ‘the’ comes in at #1 with 18444 occurrences, “lord” comes in at #45, “king” at #69 and so on.

While you probably aren’t interested in Shakespeare for your own business purposes, it shows what would be possible if you wanted to analyse what was being said in Facebook updates, Tweets, product reviews and so on. It wasn’t all that hard to write either, especially considering that I’m fairly new to Data Explorer, so more sophisticated variations on this would certainly be possible without much extra effort. And it’s worth pointing out that it’s fast too: it takes a couple of seconds to refresh, and I’m not even sure I’ve done everything in the most efficient way possible.

You can download the sample workbook (but not the Complete Works of Shakespeare) here. If you want to point it at a different text file, just modify the first line of the code.

UPDATE: it turns out there is either a bug in PQ or I’m hitting one of its limits, and the counts here aren’t all correct. See the comments from Frank below for a version that works.

What is Data Explorer targeted to solve and where does it fit in the mix of Microsoft BI tools? It looks like it overlaps some with PowerPivot on the loading of data, and SSIS or Access on the ETL/transformation of data, and VBA or Visual Studio for coding with M(?). I am confused.

I don’t think it overlaps with PowerPivot on ETL, since PowerPivot doesn’t really have any ETL functionality. I don’t think it overlaps with SSIS either since SSIS isn’t a desktop, self-service tool. However I think it does overlap a bit with PowerPivot and VBA for certain types of calculation and reporting though; in some scenarios I think users will prefer to work with Data Explorer rather than PowerPivot because it’s easier to build up calculations with its step-based approach.

Is it targeted for loading data only into Excel? Does it load into PowerPivot directly or are you limited to the Excel row limits? I really like that PowerPivot upgrades to SSAS Tabular when needed. Data Explorer seems like an Excel-only thing. Does the ETL or M(?) steps upgrade to SSIS? What are the realistic data set size and performance limits for a desktop user – are they similar to those in PowerPivot? I think I am missing the bigger picture of when/where Data Explorer makes sense and when I would not want customers to use it but suggest PowerPivot, SSIS or something else. It would be nice to see a diagram with it in the mix of MSBI tools, target use cases, and path from self-service to enterprise usage when one of these self-service Data Explorer projects becomes a mission critical project and then needs to be upgraded to be more of a enterprise ready versus personal solution.

You can load data directly into PowerPivot and don’t have to load it into the worksheet.

No, it doesn’t upgrade to SSIS.

I don’t know what its scalability is like; PowerPivot is likely to be faster for some things, like aggregating and filtering, but PowerPivot is a different type of tool.

Really, Data Explorer is being positioned as a self-service ETL tool and this positioning means it complements PowerPivot very well. The point I’ve been making here is that actually Data Explorer is capable of much more than ETL, but its more advanced capabilities are unlikely to be interesting to most users.

Nice blog post Chris, very interesting.
There seems to be a small error in the copy-pastable code:
RemoveBlanks = Table.SelectRows(RenameColumnToWord, each ([Word] “”)),

The semicolon shouldn’t be there.

I recreated the same set-up in SSIS using the Term Extraction component (which is an Enterprise edition component) and it outperforms Data Explorer as expected (so I don’t have to fear for my job :). Nonetheless I’m very impressed with Data Explorer and its capabilities and I very much enjoy your blog posts about it in the past few weeks.

Hi, this query is perfect. But, i’m quite bad in code. I would like know, if it’s possible to delete or remove some words listed in a .txt files or in a sheet of Excel, actually, i have created a new entry in this code with more than 100 words (filter text, does not contain) = Table.SelectRows(KeptFirstRows, each ([Word] “04″ and [Word] “11″ and [Word] “12″ and [Word] “20″…. But, i would like listed words in a file or a sheet. Thank you !

Hi Chris, I tried to simplify your code using the function ‘Text.Split’.
Your count of ‘the’ and ‘and’ was 18.444 and 17.811, respectively.
My count of ‘the’ and ‘and’ is 27.816 and 26.777, respectively.
I have no idea why there is such a huge difference.
Have I missed something? Do you have an explanation?

The data in my example is being truncated somewhere very early on. It looks like a bug in PQ – I don’t think I am running into any of the published limits. Thanks for letting me know about this! I’ll see what the PQ team say about this problem.

I also think it is a bug in PQ.
Table.FromValue(Lowercase) still has the correct length (5.3MB).
But, Table.TransformColumns(TurnIntoTable, {“Value”, Splitter.SplitTextByWhitespace()}) only returns about 600k items, instead of about 900k.
Thanks for reporting this issue to the PQ team.
Hopefully, you will get an answer.

I now have an answer from the nice people on the PQ team. It seems like the problem is with the Splitter function which, by default, ignores any delimiters inside quotes. Using QuoteStyle.None in
= Table.TransformColumns(TurnIntoTable, {“Value”, Splitter.SplitTextByWhitespace(QuoteStyle.None)}),
Should stop this happening.

[…] one of my first posts on Power Query (and still my favourite) I found the top 100 words in the complete works of Shakespeare. As always when you’re learning a new tool, though, I look back at what I wrote then and realise […]

[…] one of my first posts on Power Query (and still my favourite) I found the top 100 words in the complete works of Shakespeare. As always when you’re learning a new tool, though, I look back at what I wrote then and realise […]

Hi. Thank you for a fine code for word counting. I have almost got it to work but I still need to remove ” (quote sign) from the text.
I have made this line to remove text but Power Query will not accept that I add “”” to the list to remove ” from the text:

Follow Blog via Email

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 7,729 other followers

Public Power BI and SSAS Training Courses

I'm running several SSAS, MDX and Power BI-related training courses through Technitrain in 2017. Check out the Technitrain course catalogue for full details, and to see other upcoming courses from the likes of Alberto Ferrari, Andy Leonard, Allan Hirt and Alex Yates.

Need some help?

As well as being a blogger, I'm an independent consultant specialising in Analysis Services, MDX, DAX, Power BI, Power Query and Power Pivot. I work with customers from all round the world solving design problems, performance tuning queries and delivering training courses, and I am happy to work on short-term engagements. For more details see http://www.crossjoin.co.uk