Analyzing text with Power Query and Excel

We all know how good Power Query is for cleaning, shaping and analyzing numerical data. But this powerful tool opens doors for analysts that had been closed for long time, such as for analyzing text. In this article, I will give you some ideas how Power Query can be used for analyzing text that is unstructured. This is a new area for me as well so I’ll be grateful for any comments, thoughts, ideas and shared experience that will help to elaborate further on the methodology.

Our scenario

First, let’s put things in a context with simple but realistic example. Assume you are the newly appointed Marketing Manager of a 4-star SPA hotel. You want to analyze reviews about the hotel in sites like booking.com and tripadvisor.com to understand the major service issues. Thanks to Power Query, you will be able to extract, clean and shape data from those sites to receive a nice table like this:

Comments table

* Table contains ~300 real opinions for several Bulgarian SPA hotels in Hissarya, Velingrad, Sandanski and Bansko. Source: booking.com and tripadvisor.com

But how can you get some insights from this data? Obviously you will spend lots of time to read all reviews. Even if you do that, you need to summarize the information somehow. So let’s play a little within Power Query and see how it can help us analyze text.

Preparing data for analysis

First, we load the table in Power Query (From Table) and clean it of punctuation symbols like commas, semicolons, dashes, brackets, etc., as well as replace capital letters with lower cased one. Why do that last one? Because we want to count the most frequently used words and for Power Query ‘hotel’, ‘hotel,’, ‘Hotel’ are different words.

The cleaning can easily be done with tools like Replace Values, Trim, and Lowercase. I believe they are well-known to average user of Power Query so will I skip detailed operations. Instead, here some lessons learnt from practice:

  • First, duplicate the Opinions column so you can have original text.
  • Then, replace all punctuation symbols with a space.
    • Later we will use a period a separator to split opinions into sentences. And since a sentence can finish with exclamation mark or a question mark it is useful to replace ‘!’ and ‘?’ with ‘.’
  • Since this is a hotel, clients may use the symbol ‘*’ in their opinions (i.e. ‘nice 4* hotel’ or ‘definitely not a 4* hotel’). So don’t replace this symbol.

Above points are nice examples that when analyzing text or other data you need to do it in a context and should explore it first, but that is another story.

Here are the steps applied so far. As a final touch the text has been trimmed and cleaned.

Steps taken to clean data

Separating each comment

Our next step is to split the column using a period ('.') as a delimiter, and doing so at each occurrence of the delimiter.

Split columns by delimiter

Power Query creates a number of new columns, each one containing a sentence. I bet you are guessing what will follow. That’s right – select the original two columns and choose Unpivot Other Columns.

Unpivot other columns

After removing the Attribute column and filtering out rows that are blank, our table has a column for the original comments (Opinion) and a column for each sentence contained in the original comments (Sentence). Please be patient here, and you will understand why this is important.

Table containing full comments and each individual sentence

Let’s now duplicate the Sentence columns and repeat the above actions: split each sentence into its individual words, then unpivot. Below is the table after removing the Attribute column and filtering out blank rows:

Table containing full comments, each individual sentence, and each word

As a next step I will add custom column with a simple formula that calculates the length of each word. We can use it to remove common words that bring no value to our analysis – ‘a’, ‘the’, ‘is’, ‘or’, ‘but’ etc.

Custom function to calculate the length of each word

Finally, filter to exclude words that are 1, 2 or 3 letters long. We will use the resulting table as the basis for analyzing text.

Table filtered for words greater than 3 letters

Finding Lookup words

As a first step in analyzing text, let’s Duplicate the query (you may use Reference as well).

Duplicating the query

In the new Query, simply use the Group By function on the Words column to count the rows.

Grouping by the Word column

Thus, we will have a list with all words (above 3 letters long) and how many times they have been used. For convenience, we can sort this and then load it into Excel.

Word count table

Let’s take a look at the list. Obviously it contains words like ‘with’, ‘this’, ‘have’ that we need to ignore because they could be used in many different situations. On the other hand we see ‘good’ and ‘nice’ in the top 20. Those words are highly subjective – what is good for me might not be good for you. Besides, we don’t know whether the reviewer said ‘good food and nice service’ or ‘the food was not good and service far from nice’. So let’s skip them for now.

Apart from the above, we see that customers mention most often words like ‘room’, ‘pool’, staff’, ‘food’. These are our words of interest (I call them ‘Lookup words’). We may now filter all sentences that contain these words, allowing us to read much less information compared to original list of opinions.

Honestly speaking, if you have enough experience or even some common sense, you can skip this step. I mean it is more than expected that ‘room’, ‘staff’ and ‘pool’ are most often mentioned in reviews for a SPA hotel. However, identifying lookup words might be needed for cases like analyzing text of more open-ended questions.

Filtering Lookup words

So far we have identified our words of interest and listed them in a table:

List of lookup words

So how do we filter all sentences containing these lookup words? We can make it dynamic by creating a custom function. The process is similar to one used to load data from multiple internet pages, as described in M is for (Data) Monkey.

First, we create a query to Filter Rows of the Word column that contain one of our lookup words, ‘staff’ for example.

Filtering for a look up word

Then we go to Home > Advance Editor and change the M code a bit as shown below:

Updating the M code

After we create the function, we rename it as something more user-friendly (I used fxFilter).

Then we load our LookupWords table into Power Query (From Table):

LookupWords table

Next, we Add Custom Column. (Note: in Excel 2016, you have to Invoke Custom Function.)

Adding a custom column

And when we expand the table, we get all rows that contain the words from our lookup list.

Identifying Connected words

We now need to split the Sentence column again to identify the words that were most often used in sentences containing our Lookup words. I call these words ‘Connected words’.

There are some final steps such as eliminating words that appear rarely (i.e. less than 5 times), removing duplicate rows, rearranging columns etc. that I will skip.

Our final table is on the left below:

Final table for analyzing the comments

Analyzing text

As you see, I added two slicers to our table - one for our Lookup words and one for our Connected words. By using them in combination, we can get more insights quite easily. For example, these are the words used most frequently together with ‘staff’:

Slicers used to identify comments regarding staff

Here, I have filtered for the Connected word ‘friendly’, which gives us just 10 rows to read:

Analyzing text for the word 'friendly'

In a separate column, we can manually type “Y” for each positive opinion and “N” for each negative. Now we can calculate that 30% of mentions are actually negative. This is a simple way to quantify and measure our data. And it takes just few minutes to find out that the picture is similar with ‘helpful’ and ‘polite’ – positive feedback is prevailing.

When we filter ‘English’ it is easy to see that 8 people mentioned staff speaks no or poor English. That directly calls for some action - we need to train our staff in English. If you were reading through 300 opinions, you might miss this quite easily.

Analyzing text containing 'staff' and 'English'

It takes just few clicks and couple of minutes or reading to find out that clients are happy with the breakfast:

Analyzing text for comments on breakfast

But are disappointed by the prices and quality of food in the restaurant:

Analyzing text for comments on the restaurant

Of course this is just a starting point and each of the above observations needs further investigation and analysis. But the key takeaway here is that we can successfully use Power Query to summarize unstructured text data. We can then focus our attention on sentences that contain the most commonly used words instead of reading the whole bunch of reviews.

Applying the technique

Many other ideas come up into my mind of how we can use this for further analyzing text data. We can use the Stayed column to study trends in time, we can quantify some key words (as shown with 'friendly') or make a word cloud showing our top 20 words. What are your ideas - how can we use Power Query and Excel to analyse unstructured text?

Power Query Challenge 3 Results

Wow… 46 submissions for Power Query Challenge 3!  Crazy stuff. So many that I haven't even had time to read them all yet!

Due to the overwhelming responses, here's how I'm going to handle this:

  1. Show you how I approached this challenge
  2. Call out a couple of the submissions that I thought were cool and/or interesting
  3. Leave it to you to throw a note in the comments if you think that your (or someone else's) submission should have been listed.

You can find access to all of the submitted solutions (including mine) stored on my OneDrive account.

My solution to Power Query Challenge 3

Step 1 of Challenge 3: Assigning scores to letters

The most important component to Challenge 3 is to have a table that assigns a value to each letter, and there are a couple of ways you could do this.  I elected to do this via the following method:

  • Create a new blank query and name it LetterValue
  • Enter the following formula in the formula bar:
    • = {"A".."Z"}

This creates a nice list of letters from capital A through capital Z:

SNAGHTML99b7af

As great as this is, I still need to add values to the numbers (1 for A, 2 for B, etc..).  It's easy to do with an Index column, but the problem is that the button to do so is not available when you're working with a list.  No problem though, we just convert it to a table first:

  • Go to List Tools --> Transform --> To Table --> OK
  • Go to Add Column --> Index Column --> From 1
  • Rename the columns to Letter and Value
  • Set the Data Types to Text and Value

And that's it.  The query (as shown below) can now be loaded as a connection only for later use:

SNAGHTML9d4b74

Other options for Step 1 of Challenge 3

To be fair, I'm pretty comfortable knocking out a quick list, as I do it all the time for Calendar tables.  But as Bill Syzyz pointed out, you get bonus points for this being 100% UI driven.  Could I have done that?  Of course!  Instead of creating the list, you would:

  • Create an Excel table that holds the letters and values
  • Pull it into Power Query
  • Right click the Letter column --> Transform --> Upper Case
  • Right click the Letter column --> Remove Duplicates
  • Load it as connection only

Is it easier?  Maybe.  It it more robust?  It could be more robust if you also want to score other characters.

Step 2 of Challenge 3: Scoring Words

So now we get into the money part of Challenge 3 - scoring the entire word.  Let's assume that we have a nice little Excel table which stores all the words in a column called "Word" like this:

image

(To be fair, the data could come from a database or anywhere else, the source is really incidental to the problem.)

To score these words we can

  • Pull the data into Power Query
  • Right click the [Word] column --> Duplicate
  • Right click the [Word - Copy] column --> Transform --> UPPERCASE

This leaves us here:

image

Now the trick…(psst… I have a cool pattern card for this… watch this space in the next few days for news!)

  • Right click [Word - Copy] --> Split Column --> By Number of Characters
    • Choose to split by 1 character, repeatedly
    • From the Advanced Options, choose Rows

Your output should now look like this:

SNAGHTMLaa6bfd

Next:

  • Go to Home --> Merge Queries
  • Choose LetterValue and merge [Word - Copy] against [Letter]
  • Expand only the [Value] column from the [LetterValue] column (by clicking the expand icon at the top right of the column)

SNAGHTMLad0db2

The data is out of order now, but it doesn't matter.  It's time to put the finishing touches on Challenge 3…

  • To to Transform --> Group By
  • Configure a basic grouping like this:
    • Group by Word
    • Create a Score column that SUMs the [Value] column

image

And you're done!

image

You can now throw any value into the Excel table, hit refresh, and it will score the words…

image

Well how about that…

And our upcoming Power Query Recipe cards look like they could be way better than hard work too!  (Stay tuned for their release next week!)

Your Challenge 3 Submissions

With 46 submissions for Power Query Challenge 3, it was impossible to go through them all.  I'm hoping that you'll consider being part of the community of reviewers here and check out a few random ones, then post in the comments any that you think I should have mentioned.  I can tell you that in the first four submissions alone there are three different versions of custom functions, and a UI driven approach.

But there are a couple of submissions I looked at that I did want to highlight, as they brought more than just a query to the game.  Smile

Bart Titulaer

Bart's solution includes not only his Power Query work (which he tried more than one way), but he also thought it might be fun to include some frequency distributions with the solution:

image

It's a good reminder that the end goal of Power Query isn't just to clean up data, it's to actually use it.  (Something I probably don't cover enough on this blog!)

Kolyu Minevski

Kolyu decided to compare scoring English vs Bulgarian, and even summed it up for me with a note:

image

Nice to know since I'll be back in Sofia at Bulgaria Excel days on November 1!

Power Query Challenge 3

**Please note that the challenge is now closed, so we are no longer accepting submissions. However, you are still welcome to try it out on your own.

I've got a lot of feedback that you enjoy the Power Query Challenge series we've been running, so it's time for another!  Challenge 3 is just a fun one that was inspired by a conversation I had with Alex J.

Background for Challenge 3

I'm sure you've all seen this before:

If the letters A-Z are worth a value of 1-26 respectively, then:

  • Knowledge = 96%
  • Hardwork = 98%

Of course, the joke is that Attitude is worth 100%.

Your job for Challenge 3

For challenge 3, based on assigning a letter score of 1 for A, 2 for B, 3 for C, etc…:

  • Take a table of words
  • Return the total score using Power Query

So basically… do this:

image

But keep in mind that correctly solving Power Query Challenge 3 requires one very important thing… It needs to work no matter the case of the letters in the original column.

Before you submit your answers to Power Query Challenge 3

After our previous challenges, I got feedback that people really want to see the solutions submitted by others.  I haven't shared them only because I didn't specifically say I was going to, and I can't guarantee that there isn't personally identifiable information in them.  That will change here…

Submitting Your Answer to the Power Query Challenge

**The challenge is now closed, so we are no longer accepting submissions.

To submit your answer:

  • Please name your file using your name - or the name alter-ego if you prefer.  (Keep in mind that your real name could be in the file properties, and it's up to you to clean that out.)  The reason we need a name is so that anyone reading solutions can ask questions about your solution.
  • We have to cap this somewhere, so will allow submissions until the end of Sunday, Sep 16 only.
  • While the challenge is open, you can submit your answer to Rebekah at Excelguru dot ca with the subject Power Query Challenge 3.

After Submissions are closed:

I will post my solution to Power Query Challenge 3, and share a link to a folder of all the submitted solutions.

Please note that last challenge we had over 30 submissions.  While I may post some highlights from the solutions, I won't be doing a full write-up of all them due to the volume we expect to see.

Have fun!

Disaster Recovery in Puerto Rico with Power Query

When Ken was at the Microsoft Business Applications Summit a few weeks ago, he met Mr. J.A. Garcia who has been doing some amazing work with Power Query. We wanted to share his story about how he has been using Power Query in helping with disaster recovery efforts in Puerto Rico:

"[In] my line of work there's been two defining moments that have changed the way we look at our tools. The first one was the Zika outbreak and the second one was Hurricane Maria.

The first time I saw Power Query was [as part of] Power BI during the Zika outbreak [in 2016]. One of our clients needed up-to-date information of the Zika outbreak and its effect on healthcare. With the help of a consultant, we started using Power BI and Power Query.

Aedes aegypti mosquito

An Aedes aegypti mosquito, one of the main transmitters of Zika virus.

I began taking courses during that time, and one of them was about Excel. That's when I learned about Get & Transform in Excel 2016.

Any new job that I received, I tried to use Power Query. I taught myself SQL so I could understand better the process of extracting data and how to integrate it into Power Query.

Our job was changing. We could give the tools to our clients that would let them refresh when they needed it the most. No more waiting [on] our area for a data refresh!

Then Hurricane Maria hit Puerto Rico [in September/October 2017]. It was a harsh two weeks of no communication. As soon as I came back from work, I noticed the change in attitude. As a healthcare company, we began doing Public Health.

Hurricane Maria - Disaster Recovery with Power Query

Hurricane Maria is regarded as being the worst natural disaster on record to affect Dominica and Puerto Rico and the deadliest Atlantic hurricane since Hurricane Stan in 2005.

My main job was identifying members with certain serious conditions. I used Power Query and Excel to create processes that obtain information from the assessment done to keep track of the efforts of the company. The clients could refresh the data and see who was missing, fix any data entry errors and more.

I'm very proud of my work, and Power Query in Excel and Power BI has been a large part of my growth. In the present, we have created a tool that refreshes constantly to help identify members with serious conditions. Now in case of any emergency, we'll know who to attend."

~ J.A. Garcia

We were very inspired how Mr. Garcia began is Power Query journey as part of the disaster recovery efforts after these emergencies, and that he and his team continue to leverage this powerful tool in both Excel and Power BI. Power Query really can help save lives!

Do you have an story to share about your Power Query journey? Maybe it hasn't saved your life literally, but perhaps it has saved you hours of time and effort, a significant amount of money, or even your sanity! Let us know in the comments below or contact us through the Excelguru site.

Power Query Challenge #2 Results

What an overwhelming response to Power Query Challenge #2!  We had 40 submissions, and some with multiple entries in a single submission.  Plainly you all enjoyed this!

Naturally, there were a couple of submissions that involved custom functions, and a couple who wrote manual grouping functions to get things done.  These folks obviously know how M works, so I'm going to focus more on the other entries to show different UI-driven routes to accomplish the goal.  Winking smile  Each of those is included in the workbook that you can download here.

The Base Query

I'm going to start this by creating a base query called "Source Data" which has only 2 steps:

  • Connect to the Data Table
  • Set the data types

This is going to make it easy to demo certain things, but also replicates what a lot of you did anyway.

Most Popular Solutions to Power Query Challenge #2

By far the most popular solution to Power Query Challenge #2 was by starting using one of the following two methods:

Method 1A - Group & Merge

  • Reference the Source Data query
  • Merge Customer & Membership
  • Remove duplicates on the merged column
  • Group by the Customer column and add a Count of Rows

Method 1B - Group & Merge

  • Reference the Source Data query
  • Remove all columns except Customer & Membership
  • Group by the Customer column and add a Count of Distinct Rows

Either of these methods would leave you with something similar to this:

image

Method 1 Completion

No matter which way you sliced the first part, you would then do this to finish it off:

  • Filter the Count column to values greater than 1
  • Merge the filtered table against the original data set:
    • Matching the Customer column
    • Using an Inner join
  • Remove all columns except the new column of tables

image

  • Expand all columns
  • Set the data type of the Data column and you're good

image

Of the 34 entries, this variation showed up in at least 25 of them.  Sometimes it was all done in a single query (referencing prior steps), sometimes in 3 queries, and sometimes it wasn't quite as efficiently done, but ultimately this was the main approach.

A Unique Solution to Power Query Challenge #2

I only had one person submit this solution to Power Query Challenge #2.  Given that it is 100% user interface driven and shows something different, I wanted to show it as well.  I've labelled this one as Pivot & Merge.

Here's the steps:

  • Reference the Source Data query
  • Remove all columns except Customer & Membership
  • Select both columns --> Remove Duplicates
  • Pivot the Customer column (to get of products by customer)

image

  • Demote the headers to first row
  • Transpose the table

And at that point, you have this view:

image

Look familiar?  You can now finish this one using the steps in "Method 1 Completion" above.

Personally, I don't think I'd go this route, only because the Pivot/Transpose could be costly with large amounts of data.  (To be fair, I haven't tested any of these solutions with big data.)  But it is cool so see that there are multiple ways to approach this.

The Double Grouping Solution to Power Query Challenge #2

This is the solution that I cooked up originally, and is actually why I threw this challenge out.  I was curious how many people would come up with this, and only a couple of people put this out there.  So here's how it works:

  • Reference the Source Data query
  • Stage 1 grouping:
    • Group the data by Customer and Membership
    • Add a column called "Transactions" using the All Rows operation

This leaves you here:

image

Now, you immediately group it again using a different configuration:

  • Group by Customer
  • Add columns as follows:
    • "Products" using the Count Distinct Rows operation
    • "Data" using the All Rows operation

Which leaves you at this stage:

image

It's now similar to what you've seen above, but we have a nested table that contains our original data.  To finish this off, we now need to do this:

  • Filter Products to Greater than 1
  • Expand only the Transactions column from the Data column
  • Right click the Transactions column --> Remove Other Columns
  • Expand all fields from the Transactions column
  • Set the data types for all the columns

And you're there!

image

Final Thoughts

Again, there were more solutions submitted for Power Query Challenge #2.  We had:

  • A couple of custom function submissions (of which each was slightly different)
  • A couple of custom grouping solutions (not written through the UI)
  • A couple of solutions that used grouping, then used a custom column to create a table based on the grouped output which filtered to distinct items

If I haven't covered yours here and you feel that I missed something important, please drop it in the comments below!

The part that fascinates me most about this is that we had UI driven submissions involving merging, transposing and grouping.  Three different methods to get into the same end result.

Thanks for the submissions everyone!

Update on the Master Your Data Book (Data Monkey v2)

Miguel and I were at the Microsoft Business Applications Summit last week, and we were frequently asked for an update on the Master Your Data book (aka M is for Data Monkey version 2.0). We were told that it’s time. People pointed out that they had pre-ordered it on Amazon ages ago. Enthusiasts asked why we don’t have a subscription model with monthly updates like Power Query does.

Master Your Data Book Cover

There’s a hunger to see the new version. We’re flattered that you rely on us, and honestly, we’re gutted that you are still waiting for it. And after attending the summit, we know that we need to give you an update on the Master Your Data book.

Some background on publishing…

Before we tell you what’s happening, I’d like to just explain a bit about the back story on what we have to consider when we write books on technology. The primary factors are:

  1. How to fit it in with our schedules. Even over the long term, books don’t come close to earning anywhere near the financial rewards of just dedicating time to consulting projects. (This is a big factor in the subscription question.)
  2. In today’s world of constant updates, we know that there are new features added on a monthly basis. The question is, which ones are serious enough to cause us to delay the release?

If you look back at M is for Data Monkey, we are really proud of its long-term value and continued relevance. It kills us that merges aren’t in there… they came out a few days after the book went to print. Would we have held the book for them if we had known? Yes. Would we have held the book for conditional columns? No. This is just one of the kinds of decisions we have to face.

No matter which way we go, we’ll always wish we waited for the next great feature. And we can’t. We know that. But our goal is to make sure that the material inside the book stands the test of time well and continues to hold relevance as features are added and changed. We believe that we’ve managed to do that with M is for Data Monkey fairly well, all things considered. Are there easier ways to do some things today? Yes. But does the book give you a deeper understanding and still let you accomplish the same goals? We believe it does.

The factors that lead to delays

Features are one thing. They generally add new functionality. But User Interface changes are something else entirely…

Earlier this year, we made the call to delay the Master Your Data book in order to get a clearer picture on what Excel 2019 was going to look like. We needed to know which Power Query features would be there, and which wouldn’t. It just doesn’t make sense to publish a book around the same time of Excel 2019’s release with Excel 2016 screen shots. We’ve already been told that some stuff in M is for Data Monkey looks “dated”. We certainly didn’t want the new Master Your Data book to be “dated” on the day of release due to a User Interface change.

And now, at the Microsoft Business Applications summit, we saw a preview of what is targeted for release into Power Query in the next few months. These new features are significant, they are impactful. But most relevant to us is that they contain a significant change to the Power Query User Interface. They will affect every single screen we use. They will affect every single screen shot we take. And if we don’t wait, we will deliver to you a brand-new book that has pictures that don’t look anything like the User Interface you see on screen. Even if we were to push material to the publisher today, it takes 2-3 months to get the book to Amazon, so best case, you get 2-3 months' use out of the book.

To us, that is irresponsible. We refuse to take your money and deliver you a substandard product. It’s just not right.

Just how significant were the MS Biz Apps announcements?

If you weren’t at the Microsoft Business Applications Summit, you might not know about these announcements. You can read the full list here, but let’s recap the key ones for us here. We can divide them into two categories:

  1. Awesome-but-not-critical (i.e. we would cry because we couldn’t include these, but wouldn’t delay the book for them)
    • New data connectors (including extract from PDF)
    • Fuzzy lookup
  2. Critical features (stuff that must be in the Excel version of Power Query before we can test material, write about nuances, shoot images and release the book)
    • Data profiling (quality) previews
    • M Intellisense in the Advanced Editor, formula bar and Add Custom Column window

These last two features will have a significant effect on the images of the book, as you can see here:

Power Query UI Preview

What is the revised timeline for the Master Your Data book?

The new Power Query features are estimated to arrive in Power BI Desktop by October 2018. And based on the historical pattern, these features will show up in Excel within 2-3 months of their Power BI release date. Giving us time to test the new features, take screenshots, revamp the book order to best tell the data story the way we need to… We are hoping to have the book in print by the end of Q1 2019. It’s still aggressive on our side, but that is our refined target. If the builds ship later, or things take longer then anticipated, it could slide it into Q2.

Yes, we know it’s a long way away. We know you’ve been waiting, and we wish it could be faster. But again, we hope that you understand that we are doing this to truly give you the best book that will last longer than it could otherwise.

In the mean time – can we give you something else?

We have been working on another product as well: Master Your Data in Excel & Power BI recipe cards.

Naturally, all members of our Power Query Academy will get a free copy of these. And due to the significant delay of the book, we’d also like to offer a free copy to anyone who has pre-ordered Master Your Data on Amazon. (More on this below.)

So, why can we do these, but not the book? It’s because they assume you already know Power Query’s User Interface, so only provide the steps on how to accomplish the goal. Loaded with before and after pictures, and the route to get from one to the other, we aren’t bound by User Interface design changes.

Here’s a quick sample of one of the cards:

Power Query Recipe: Pivoting Stacked Data

And another:

Power Query Recipe: Split Records into Columns

How will these be sold?

Ultimately, we plan on selling this product on a subscription basis through our web store as follows:

  • $14.99 for the purchase of the downloadable card set
  • $2.99/quarter for a subscription to updates

We already have 26 cards designed, with more on the way. As we expand the set, the original purchase will include the new cards. But for those on subscription, we will update your original purchase and give you access to the new cards when we release them. We’re not intending to hold these for quarterly release, but rather send a new one every time we build it. You might get five in one quarter and one in the next, but our intention is to keep delivering new patterns as we discover them and build summary tips cards to illustrate them.

Wait… didn’t you say subscription doesn’t work for publishers?

For books, yes, it’s really hard. They’re complicated and require ensuring that things are taught in the right order, with all the updated techniques along the way. These Master Your Data recipe cards are snapshots of what to do in certain data cases when working with data in Excel and Power BI, so are a much more refined scope.

It’s way easier for us to update cards, or add entirely new ones, as it doesn’t require re-writing precedent chapters. So in this case, it makes sense, as we can provide an initial catalogue of patterns, and add more over time. We’ve already got ideas for a bunch more to expand this set.

How can I get my hands on the Master Your Data Recipe Card set?

There are a few ways…

For those of you who are members of the Power Query Academy, we will add the tip cards as a resource as soon as we have them ready. It’s part of your subscription so, as long as you’re still an Academy member, you’ll get all the new ones we create. In addition, we will also make sure you get a copy of the new Master Your Data book as soon as it is released (even if your subscription has expired and you’re no longer an Academy member).

For those of you who have pre-ordered our new book on Amazon, please follow the Excelguru blog. We will post when the recipe cards are ready and will let you know what you need to do to get your free download of the initial package of cards. The subscription for updates will be available as well, but will be entirely optional.

And if you’ve just been waiting for the Master Your Data book and haven’t purchased yet, all good. We’ll be setting these up in an online store to allow you to buy the download version and (optionally) sign up for the updates as well.

When will the Master Your Data Recipe Card set be available?

Soon. We are in final design for the card set now and need to set up our web store to handle subscriptions. Our target is to have that all done by September 15, 2018, if not earlier. Keep watching here for the official announcement.

Ultimately…

…we wish we could send the Master Your Data book to you today, but hope that this will make a reasonable substitute to get you over the hump until we can. Thank you for your patience, understanding and trust in us as we work to deliver you the best version we possibly can.

Power Query Challenge #2

I'm at the Microsoft Business Application Summit this week, so I thought I'd post another Power Query challenge, especially since our last one was so successful.

For this Power Query challenge …

Our business challenge here is that we are in the process of working out how to reward customers that buy memberships in multiple business areas across our organization.  To perform this analysis, we have been provided a list of transactions that looks like this:

image

What we've been asked to generate here is a list of all the transactions pertaining to customers who have purchased from multiple business units.  In other words, we want this output:

image

There's a couple of pieces we need to watch for here:

  1. Susan and Bob are the only people in this list who bought memberships to multiple business units.
  2. Susan bought multiple Golf Course memberships (one for her and one for her spouse).  We need to keep both those transactions - even though they are in the same division - as she also bought a Marina and Fitness Club membership.
  3. Claire also bought two Golf Course memberships.  While she bought multiple products, they are from the same business area, so we want to ignore them.

What are the rules for Power Query challenge #2?

It's pretty simple really.  This is a Power Query challenge.  That means you can use Power Query in Excel, or Power Query in Power BI.  VBA, SQL and Excel formulas results don't count.  Winking smile

I've got a Power Query driven solution all cooked up to return the results above.  And now I'm curious to see how you would solve this problem using the same tool.

Ready to give this Power Query challenge a try?

Like we did with our last Power Query challenge, we're going to ask you: Please Do NOT post your answer below.  (We don't want to spoil it for anyone who wants to play along.)

Please note: the submission period for the Challenge is now closed. The submissions are being reviewed and will be discussed next week!

You can download the source data for this Power Query challenge here.  Have fun!  Smile

Power Pivot eBook Coming Soon

It's been a long time coming, but we are putting the finishing touches on the third installment of our free 'DIY BI' series. Consequently, we are excited to announce that the Power Pivot eBook will be officially released on Tuesday, July 3, 2018!

Power Pivot eBook

This brand new book will feature five of Ken's top tips, tricks, and techniques for Power Pivot, including:

  • Hiding fields from a user
  • Hiding zeros in a measure
  • Using DAX variables
  • Retrieving a value from an Excel slicer
  • Comparing data using one field on multiple slicers

Power Pivot eBook

 

About the 'DIY BI' Series

This free eBook series is available to anyone who signs up for the monthly(ish) Excelguru email newsletter. The series includes four books, one edition each for Excel, Power Query, Power Pivot, and Power BI. Each book contains five of our favourite tips, tricks, and techniques which Ken developed over years of research and real-world experience.

DIYBI eBook Series

We first launched this series in the spring of 2017 with the Excel Edition, and the Power Query edition followed later that summer. You can read some more about why Ken decided to create this series in his initial blog post about it.

The Excelguru Newsletter

The monthly Excelguru email newsletter features the latest updates for Excel and Power BI, as well as upcoming training sessions and events, new products, and other information that might be of interest to the Excel and Power BI community.

Don't Miss Out, Get Your Free Copy of the Series

If you're not already a newsletter subscriber, you can sign up here. We will send you the Excel Edition right away, and the Power Query Edition a few days later. All of our current and new subscribers will receive the Power Pivot edition once it is released on July 3, 2018. Be sure to keep an eye on your inbox for the new book.

We will be continuing to work on the fourth and final book, the Power BI Edition, over the coming months so stay tuned for details!

Number rows by group using Power Query

After one of my previous sorting posts, Devin asked if we can number rows by group.  Actually, that's a paraphrase… what he really asked was:

Any thoughts on how to produce something like a ROW_NUMBER function with PARTITION from T-SQL? Similar to this: https://docs.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql?view=sql-server-2017#d-using-rownumber-with-partition

I've never used the PARTITION function in SQL, so I checked in with him just to confirm what he was after.  And here it is: take the first three columns of the table below, and add a final column with index numbers sorted by and restarting with each group:

Number rows by group example

And of course there is.  For this one, though, we actually do need to write a little code.

Preparing to Number rows by Group

Now here's the interesting part.  The source data looks exactly the same as what you see above, the only difference being that in the output we also added a number rows by group.  So how?

Well, we start by grouping the data.  Assuming we start by pulling in a table like the above with only the first 3 columns:

  • Sort the data based on the Sales column (in descending order)
  • Group the data by Group
  • Add a aggregation column for "All Rows"

Like this:

Group By dialog

Which yields this:

Table after aggregation

We are now ready to add the numbering.

Now to Number rows by Group

In order to number rows by group, what we really want to do is add an Index column, but we need to add it for each of the individual tables in our grouped segments.  If we try to add it here, we'll get 2 new values, showing 1 for Alcohol and 2 for Food.  That's not exactly what we need.  But if we expand the column, then the index will not reset when it hits Food, and the numbering won't be right either.

The secret here is to add an Index column to each of the individual tables in the Data column, then expand the columns.  The numbering will then be correct.

To do this, I added a custom column using the following code:

=Table.AddIndexColumn([Data], "Index", 1, 1)

Where did I get this code?  I actually added an Index column to the whole table.  That added the following code in the formula bar:

=Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1)

I copied that code, and deleted the step. Then I added my custom column, pasted the code, and replaced #"Grouped Rows" (the name of the previous step) with the column I wanted to use.

The result was this:

Table after Index column added

The final steps to clean this up:

  • Remove the Data column
  • Expand all columns from the Partitioned table except the Group (since we already have it)

Which leaves us with our table in place including the new column which does number rows by Group as originally planned.

If you want to play with this one, the example file can be found here.

I would also be remiss if I didn't mention that we have a great video in our Power Query Academy that covers this kind of operation (among others).  It's called "Advanced Row Context" (in our M deep dive section) where Miguel shows all kinds of cool stuff that you can do by adding new columns to Grouped Rows.

Trick to Protect Excel Tables

Slobodan emailed me to describe a trick to protect Excel tables that he is using to drive data validation lists.  The data validation lists are sourced from tables loaded via Power Query, and leverage a little hack to hide them from prying users eyes.  I thought it would be cool if he shared it with everyone, so asked him to write up a little blog post on it, and here it is!

Take it away Slobodan…

Hello everybody,

Recently, my team and I had faced a problem with refreshing PQ tables that we managed to solve with a simple trick (no VBA coding), and shared it with Ken who asked me to share it with community. Thank you Ken for this opportunity! Glad to make some kind of contribution, to all of you PQ users.

Solution Background

We created calculation model for our sales people (Full cost calculation).  Inside this Excel file, they have a lots of drop down lists from which they can choose customer, partner etc. The idea is to make these dropdown lists dynamic.  In other words, whenever a new customer is created in SAP, they should be able to select this customer in Excel using a dropdown list. This is where Power Query comes to the rescue.

We have scheduled daily export of all our customers from SAP to a file on a network drive, and use this file as the data source for a local PQ table in the workbook. We then use our Power Query table “Customers” as the source for dropdown lists in calculation model.

The Challenge

How to make it fully automated? We have two goals here:

  1. We want Power Query to be scheduled for automatic refresh on a daily basis
  2. At the same time, we would like to protect Excel tables sourced via Power Query from careless users

For the first point, we have Power Update - a tool which allows you to schedule daily refresh.

Note from Ken: I haven't seen Slobodan's model, so there may be a need to use Power Update to do what he's doing.  If you only need your Power Queries to update each time the Excel workbook is opened, however, you could force an update by changing the table's connection properties to force an update upon open.

Second issue, in order to protect Power Query table, we need to hide these sheets and protect the workbook.  The end result is that our Customers table is hidden and cannot be unhidden and everything looks promising.

clip_image002

Of course, Excel protects the whole workbook structure using this method, which causes Power Update to fail. In fact, query refreshes also fail if we try to refresh data manually.

clip_image001

So the obvious solution doesn't work.  I spent time Googling for solution to this but could not find one 🙂

Our Solution

I am not VBA guy, but I remembered one tip from Mynda Treacy’s dashboard course which I applied here.

Step 1

  • Hide the worksheet and open the Visual Basic Editor (press Alt+F11)

Step 2

  • In the Project Explorer Window (Ctrl + R if it's not showing) select the sheet which  contains the Power Query table

clip_image003

Step 3

  • In the Properties Window (press F4 to display this), set the Visible property to "2 - xlSheetVeryHidden"

clip_image004

Step 4

  • Go to Tools --> VBAProject Properties --> Protection
  • Check the box next to "Lock Project for Viewing"
  • Set a password so only you can access it
  • Close the Visual Basic Editor

The Effect

Our sheet containing the Customers table is hidden, and there is no possibility to unhide it.  It doesn't even show up in the menu!.

clip_image005

At this point the only way to unhide the worksheet is to go into the Visual Basic Editor, and reset the worksheet's Visible property - but you protected the VBA project with a password so no one can get in there.

The great thing is that refreshing the Power Query tables will work, because you didn’t actually lock the workbook structure.

Caveat

This solution is intended to protect data from regular excel users, who can easily mess up your workbook.  Do be aware that users with VBA skills will be able to break the password, or extract the hidden sheet contents.

Hopefully someone finds this useful 🙂

Take care!