Power Query Challenge 6

Are you ready for Power Query Challenge 6?  In this challenge we'll look at splitting nested data sets into a nice table in First Normal Form.

Let's take a look at Power Query Challenge 6:

Where did the challenge come from?

The inspiration for this challenge came from last week's Self Service BI Boot Camp that we hosted in Vancouver, BC (you should have been there, it was awesome).  At the outset, we talked about how to identify if your data is in First Normal Form (the format that is ideal for a PivotTable), and I showed this data set:

Data table with nested data sets

Data table with multiple data points per cell

Notice how the invoice has multiple ItemID, multiple Quantity and multiple Price fields per cell?  That's not good at all.

What we Really Need - Data in First Normal Form

As I explained in the Boot Camp, it is essential that the data source be in First Normal Form in order for a PivotTable to consume it.  What does that mean?  It means that it needs to look like this:

Data in 1NF

Data shown in First Normal Form (1NF)

Notice that in this case the data is atomic - it only has one data point per cell.  In addition, there is now one complete record per row.  The InvoiceID shows on every row now, and each data point has been correctly split up and applied to them.

So what's the thrust of the Power Query Challenge 6?

Well, as it turns out this is a bit tricky.  There are a few issues at play here:

  • The data is separated by line feeds within the cells
  • There are a different number of line feeds in each row
  • At least the number of line feeds is consistent for each cell in the entire row though!

So the challenge is this: break the table apart so that the data is in First Normal Form like the second image.

You can download the data source file (and post your solutions) in our forum here.  I'll give you my version tomorrow.

Power Query Intellisense and Highlighting in Excel

I'm super excited that Power Query Intellisense and Highlighting features are finally here in Excel!  It's been a long time coming, with these features debuting in Power BI Desktop months ago.

Where do Intellisense and Highlighting Show up?

Let's look at all three places that Intellisense and Highlighting are now exposed in Excel's Power Query.

Intellisense and Highlighting in the Formula Bar

The first thing you should notice about the image below is that Excel now adds colour highlighting to the text in the formula bar.  "Text" values are shown in red, keywords in blue, and data types in green:

Intellisense and Highlighting in Excel's Power Query Formula Bar

In addition, you can see Intellisense showing in the picture above as well.  In this case the cursor is immediately after the opening parenthesis character.  Unlike in the past, we now get a nice syntax popup box that tells us what needs to come next in this function.

Intellisense in Custom Columns

This is the place where I really wanted to see this happen.  Here's a look at Intellisense working live as I started to build a column using a Text function:

Intellisense in Excel's Custom Column Dialog

Pretty sweet, as this actually starts to display the options.

(Now, to be fair, in my build of Excel, this feature only seems to consistently work if I rename the column first, then tab into the formula builder.  If I just click straight into the formula, bypassing the tab order, it doesn't seem to kick in.)

Syntax Highlighting in Custom Columns

How about syntax highlighting?  The formula here is only meant for demo purposes, but you get the idea of how it can look:

Syntax Highlighting in Excel's Custom Column Dialog

Blue keywords for if, then, else.  Hard coded values (like 3) show up in green like data types, and text is showing in red.

Intellisense and Highlighting in the Advanced Editor

And finally, Intellisense and highlighting work in the Advanced Editor as well.  Here's a look at the syntax highlighting:

Syntax Highlighting in Excel's Advanced Editor

And here's what happens when we start manually adding a new step, which kicks in the Intellisense in the Advanced Editor window:

Intellisense in Excel's Advanced Editor

How do you get these features?

First, you'll need to be on Office 365.  Sorry Excel 2016 and 2019, but my understanding is that these new features are only coming to subscription users.  (That's another reason that - in my opinion - you should never buy another 4 digit numbered release of Excel ever again.)

If you are on subscription, you get them with your regular updates.  This feature set hit the Insider build channel last month in version 1907.  It's currently also been deployed to those on the Monthly Targeted channel running version 1907, build 11901.20080 or newer.

If you're not on the Monthly Targeted channel, you'll need some patience, as it's coming.  Just keep checking those updates!

Microsoft Business Applications Summit Recordings

Ken had a blast in June going down to Atlanta for the Microsoft Business Applications Summit (MBAS). There, he and co-author Miguel Escobar led an in-person workshop together for the first time. Ken also presented a breakout session on best practices for spreadsheet modeling, data shaping and data analysis. However, his real highlight was helping people with their Excel questions at the Ask the Experts booth.

Ken at the Microsoft Business Applications Summit 2019

Ken Puls hanging out at the Ask The Experts booth with his 'M is for Data Monkey' co-author Miguel Escobar and Excel legend Bill Jelen (aka Mr. Excel).

At MBAS, Microsoft also unveiled their new Power Query website. It's wonderful to finally have an official Microsoft site dedicated to this amazing tool. In addition, we're extremely proud that the Excelguru blog, along with the books and website created as part of our Power Query Training project, are listed in the Resources section!

Microsoft Power Query Website

We are thrilled to be included on the Resources page of the new Power Query official website!

On-demand Session Recordings

If you weren't able to make it to MBAS, or didn't get to all the sessions you wanted to, Microsoft has tons of on-demand recordings available for FREE! This is an amazing resource to help you continue to learn about and explore the Microsoft ecosystem. You can check them all out at the Microsoft Business Applications Summit website. Microsoft broke the sessions down into 4 streams: Power BI, PowerApps, Microsoft Flow, and Microsoft Dynamics.

Excel Sessions

Microsoft included Excel sessions in the Power BI stream under the "Other" category. Thus, you may find them a bit tricky to find. Luckily the Excel team gathered together a list of available recordings that we wanted to share. This includes Ken's session with David Monroy of Microsoft on data modeling and analysis best practices:

Power Query Sessions

The Power Query sessions at Microsoft Business Applications Summit were also part of the Power BI stream. Hence, we've compiled a list of available recordings to make them easier to find:

Unfortunately, we do not have a recording of Ken and Miguel's workshop on Working with Data in the Power Platform and Excel.

Microsoft Business Applications Summit 2020

Mark your calendar - next year's event will be held in Anaheim, CA on April 20 and 21. Additionally, you can sign up to receive updates for MBAS 2020 info on the event site. Perhaps we'll see you there!