Protect Power Queries

How you protect power queries is a question that will come up after you've built a solution that relies heavily on Power Query, especially if you're going to release it to other users.

(This is a quick post, as I'm in Australia at the Unlock Excel conference, but still wanted to get a post out this week.)

Can you Protect Power Queries?

The answer to this is yes, you can.  It’s actually very easy, and prevents your users from not only modifying your queries, but adding new queries to the workbook as well. Essentially, it shuts the door on any additions or modifications to query logic, while still allowing queries to be refreshed… at least, it should.

So how do we Protect Power Queries?

To protect Power Queries we simply need to take advantage of the Protect Workbook Structure settings:

  • In Excel (not Power Query), go to the Review tab
  • Choose Protect Workbook
  • Ensure that Structure is checked
  • Provide a password (optional)
  • Confirm the password (if provided)

Once you’ve done this, the Power Query toolsets will be greyed out, and there is no way for the user to get into the editor.

image

Does refresh work when you Protect Power Queries?

This part kills me.  Seriously.

The answer to this question depends on whether or not you use Power Pivot.  If you don't, then yes, you're good to go.  As long as all your tables land on worksheets or as connections, then a refresh will work even when you protect Power Queries via the Protect Workbook method.

If, however, you have a single Power Query that lands in the data model, your stuffed.  If Power Pivot is involved, then the refresh seems to silently fail when you protect Power Queries using this method (and I don't know of another short of employing VBA, which is a non-starter for a lot of people).

It's my feeling that this is a bug, and I've sent it off to Microsoft, hoping that they agree and will fix it.  We need a method to protect both Power Query and Power Pivot solutions, and this would do it, as long as the refresh will consistently work.

Caveats about locking your workbook structure:

Some caveats that are pretty standard with protection:

  • Losing your password can be detrimental to your solution long-term. Make sure you have some kind of independent system to log your passwords so this doesn’t happen to you. And if your team is doing this, make sure you audit them so you don’t get locked out when as staff member leaves for any reason.
  • Be aware that locking the workbook structure also locks the ability for users to get into Power Pivot.
  • Workbook security is hackable with brute force macro code available on the internet for free. (Please don’t bother emailing me asking for copies or links to this code. I don’t help in disseminating code which can be used to hack security.) While protecting the workbook structure will stop the majority of users from accessing your queries, it should not be mistaken for perfect security.

Free ‘DIY BI’ e-Books

Today I wanted to just make a quick announcement that we are currently working on a series of free 'DIY BI' e-Books.

Free 'DIY BI' e-Books? Tell me more!

Over the past few years of working with Excel an Power BI, I've obviously picked up a few different methods, tips and tricks for working with the software.  And looking at how successful our free e-Book "Magic Tricks for Data Wizards" has been through the Power Query Training site, I thought it would be nice to so something similar for Excelguru readers.

One of the cool things about the Excelguru audience at this site is the diversity.  A lot of people originally came here for Excel, but we've been exploring Power Query, Power Pivot and Power BI for the past few years as well.  The one thing that ties us all together is that we are building "Do it Yourself Business Intelligence" or "DIY BI".

My original plan was to release one e-Book with 20 different tips, tricks and techniques; 5 each for Excel, Power Query, Power Pivot and Power BI.  After getting started, however, I realized that it was going to take me a bit longer to get that all done than I wanted.  But since I want to get information out to our readers, I've decided to break this down into four separate e-Books which will be collected under the umbrella of "DIY BI Tips, Tricks and Techniques".  Each e-Book will focus on one specific area of the DIY BI story.

What will the free 'DIY BI' e-Books include?

Well… tips, tricks and techniques, of course.  Smile  Okay, seriously, each is fully illustrated and written to give you some great examples and ideas that I hope will help you in your DIY BI journey.

Here is what is covered in DIY BI Tips, Tricks and Techniques for Excel:

  • The easiest formula to return the end of the month
  • Show a message when cells are hidden
  • Quick alignment of objects
  • Easy to read variances
  • Show a message if your Pivot data is stale

Sample image from DIY BI Tips, Tricks & Techniques for Excel

What areas will the free 'DIY BI' e-Books cover (and when will they be released)?

Those e-Books will be released in the following order:

  • DIY BI Tips, Tricks and Techniques for Excel
  • DIY BI Tips, Tricks and Techniques for Power Query
  • DIY BI Tips, Tricks and Techniques for Power Pivot
  • DIY BI Tips, Tricks and Techniques for Power BI

The first is already written, we just need to lay it out and make it look a bit more awesome.  Our target is to get it released by the end of next week.

With regards to the rest, I'll go as fast as I can on them, but as you can imagine, doing things right does take time.  I would expect that each will take 2-3 weeks to build out properly, but if I can get them out faster I most certainly will.

Am I going to need Excel 2016 to get value from the free 'DIY BI' e-Books?

No.  While I highly advocate being on a subscription version of Excel 2016, you'll find content in each of the first three e-Books which can be used in prior versions of Excel.

How do I receive the free 'DIY BI' e-Books?

You sign up for the Excelguru newsletter.  It's just that easy.  As soon as each e-Book is finished, we'll be emailing it to everyone who is currently subscribed to our newsletter.

And in the mean time, you also get a monthly email from us which now includes news about the latest updates to both Excel and Power BI.

Longer term, once all four e-Books are written, any new subscribers will receive the first e-Book upon signup, and then the next in the series will arrive every couple of days until you have the full set.

So what are you waiting for?  Sign up right here and don't miss out on free DIY BI Tips, Tricks and Techniques for your work!

Subscribe to our mailing list

* indicates required





Solving Business Problems – Power Query Does That

This week's blog post on solving business problems with Power Query is a guest post by Alex Jankowski

Does your business or work group have manual tasks that are repeated regularly, such as data re-entry, copy/paste functions, sending the same old email, cross-referencing lists, etc.? We can automate these kinds of activities with a wide variety of tools, such as Excel (including VBA, Power Query and Power Pivot), Access, SharePoint and MS Power BI, as well as with other, more sophisticated software packages.

Solving Business Problems You Didn't Even Know Existed

I have found the following to be common themes that can inhibit identifying and solving business problems:

  1. Organizations are illiterate about their data.
  2. Organizations are allergic to cost.
  3. Many managers lack technical imagination. They can’t even conceive of the problems that the organization’s data could solve, let alone conceptualize potential solutions.
  4. Organizations think that only financial data is worthwhile.
  5. Organizations roll out massive ‘suites’ without a concept about how to get their money’s worth from the licenses. My guess is that on average less than 25% of the capabilities of MS Excel are utilized due to lack of training/imagination, which is not a good ROI.
  6. Organizations think that the IT group has a handle on this, whereas IT thinks that data belongs to the business or prevents anyone from accessing important data (IT acting as a “department of productivity prevention”).

And I’m not even talking about the mission critical data that resides in big ERP systems (including HR, Corporate Finance, Time Records, and Project Financials) or “Big Data” that everyone is looking over their shoulders at.

What about:

  • Detailing a list of technical drawings on a project?
  • Tracking hours spent on a project activity, or the employee vacation schedule against entitlements?
  • Creating a dataset of employee skills and performance reviews?
  • Linking that scheduling software package to a list of real deliverables?

The Basic Win - Owning Your Data

Every business has what can be termed ‘islands of data’ which are often generated and managed manually.

Islands of Data

There are 'islands of data' that exist in every organization

Let’s say that your business splurges to automate and standardize some elements of the work process to be more effective. What are the benefits? Well, there will be a reduction in repetitive costs that offset the application implementation costs. People can then graduate from data makers to data owners (where they spend more time analyzing report data rather than creating the report itself), and begin solving business problems by making smarter, data-driven decisions based on questions like, "Is that it? Was it worth it? Where’s the big payback?"

The BIG payback is in linking the ‘islands of data’ with each other to gain unexpected insights. Don’t believe me? Let's take a look at a simple real-life example.

The Unexpected Win

A few years back, I was working in an engineering firm with a very large project. The Operations Manager (thankfully, a guy with great data imagination) wanted to a way to track piping isometric drawings as they were reviewed by various project leads. The drawings would get lost in the review process, like when the head of electrical left them on his chair when he went away for a month in the summer. So we came up with a solution to identify every isometric in the review process, who had approved it, when it was approved, and where it was going next. We met the application goal and stopped losing the isometrics in the review process.

Sample Isometric Drawing

Sample Isometric Pipe Drawing (from http://www.svlele.com/drawings.htm)

Was that the BIG win? Nope. The big win was when we started matching the list of drawings that had completed the review cycle with the list of drawings issued for manufacturing in the document control system (which would have been a great opportunity for Power Query – I wish we had it then). Guess what? We found a small count of approved drawings which had not been issued to manufacturing.

The Real Cost of Not Doing the Data Work

You're thinking, "So what?" Well, each isometric drawing represents a section of pipe that is fabricated by the manufacturer with flanges, valves, fittings, etc. The fabricated pipe is then sent to the construction site holding yard to wait its turn to be assembled in the pipe rack by the construction team. The impact of the drawing not being issued to the manufacturer is that the pipe section is not on site when it’s time to construct, and nobody recognizes it is missing because the drawing was never issued.

What’s the cost of that?

  • extra cost: Rush to find and issue the missing isometric drawing
  • Extra cost: Construction team has to re-sequence its work to allow for the missing pipe section
  • Extra Cost: Rush fabrication by the pipe manufacturer, possibly resequencing their production schedule and possibly charging overtime rates
  • EXTRA Cost: Rush shipping charges to get the late pipe section to the construction site
  • EXTRA COST: Possible overall delay in the construction schedule
  • BIG EXTRA COST + LAWSUIT: Possible overall delay in the refinery startup schedule

That extra cost could be thousands of times more than the cost of doing the data work, and possibly end up eating all of the project’s margin and more. (If you’re going to be allergic to cost, this is the one to be allergic to). Creating the application for isometric tracking was a win, but matching it to the document control system drawing list was the BIG win and nobody expected it!

Delivering the BIG wins

Let's take a look at some ways at solving business problems in Power Query using the data you already have. What if you:

  • Need to find files, audit folder contents, or document a folder structure on the server? Power Query does that.
  • Need to develop an email filing system for your project? Power Query can list the emails, their contents, and attachments.
  • Need to open server files from an Excel list without navigating through the File Explorer? Power Query can help with that. (Eliminating the practice of people navigating from "My Computer" into the bowels of the server structure for each individual file is one of my personal lifetime goals.)
  • Need to create a templated report about projects, employees, or hours from an ERP system extract? Power Query does that.
  • Need to pull data from SQL for templated operations reports? Power Query does that.
  • Need to read multiple CSV or Excel files into a single data set? Power Query does that (check out some of Ken's other blog posts, like this one on Combining Excel Files).
  • Need to report on the status of drawings in your document control system? Power Query does that.

Once the 'islands of data' are being processed, Power Query does an awesome job of combining the data sets together, especially if all the column names are different and the data formats need to be aligned.

What data problems do you need to solve? How are you currently solving business problems, and are there opportunities for BIG wins in your business? Give us some feedback in the comments!

 

 

Values Become Text After UnPivoting Other Columns

Have you ever set up a nice query to UnPivot other columns, only to find that the query data types change when you add new columns?  This post will cover why values become text after unpivoting other columns.

Background

We’ve got a nice little table called “Data” showing here.  Nothing special, it just summarizes sales by region by month, and our goal is to unpivot this so that we can use it in future Pivot Tables.  (You can download the source file here.)

SNAGHTML552a2a7

Now, you will notice that April’s sales are outside the table. This is by design, and we’ll pull it in to the table later when we want to break things.  Smile

UnPivoting Other Columns – The Hopeful Start

If you’ve been following my blog for any period of time, you’ve seen this, but let’s quickly go over how to unpivot this:

  • Select a cell in the table
  • Go to Power Query (or Data in Excel 2016) –> From Table

We’re now looking at the Power Query preview of the table:

image

Great, now to unpivot…

  • Hold down the Shift key and select the Country and Prov/State column
  • Right click the header of either of the selected columns and choose Unpivot Other Columns
  • Right click the headers of the two new columns and rename them as follows:
    • Attribute –> Month
    • Value –> Sales

Re-Pivoting from the Data Model

With the table complete, I’m going to load this to the data model and create a Pivot Table:

  • Go to Home –> Close & Load –> Close & Load To…
  • Choose to Load to the Data Model

The steps to create the Pivot depend on your version of Excel:

  • Excel 2013: Go in to Power Pivot –> Home –> PivotTable and choose a location to create it
  • Excel 2016: Click any blank cell and go to Insert –> PivotTable.  As you have no data source selected, it will default to using the data model as your source:

Iimage

With the PivotTable created, I’ve configured it as follows:

  • Rows:  Country, Prov/State
  • Columns:  Month
  • Values:  Sales

And that gives me a nice Pivot like this:

image

Let’s Break This…

Okay, so all is good so far, what’s the issue?  Now we’re going to break things.  To do that, we’re going to go back to our original data table and expand the range:

image

In the picture above, I’ve left clicked and dragged the tiny little widget in the bottom right corner of the table to the right.  The table frame is expanding, and when I let go the Apr column turns blue, indicating that it is now in the boundaries of the table.

With that done, I’m going to right click and refresh my Pivot Table, leaving me with this:

image

Huh?  Why was the sales measure removed?  And if I drag it back to the table, I get a COUNT, not a SUM of the values?  And even worse, when I try and flip it back to SUM, I’m told that you can’t?  What the heck is going on here?

image

Importance of Power Query Step Order

To cut to the chase, the issue here is that when we first created the table in the data model, the Sales column was passed as values.  But when we updated the data to include the new column, then Sales column was then passed entirely as text, not values.  Naturally, Power Pivot freaks out when you ask for the SUM of textual columns.

The big question though, is why.  So let’s look back at our query.

Our original data set

If we edit our query, we see that the steps look like this:

image

To review this quickly, here’s what happened originally

  • Source is the connection that streams in the source data with the following columns:

image

  • Changed Type set the data type for all the columns.  In this case the Country and Prov/State fields were set to text, and the Jan, Feb & Mar columns were set to whole number.  We can see this by looking at the icons in the header:

image

Note that if you don’t have these icons, you should download a newer version of Power Query, as this feature is available to you and is SUPER handy

 

  • We then selected the Country and Prov/State columns and chose to Unpivot Other Columns.  Doing so returned a table with the following headers

image

Notice that the first three columns are all textual, but Sales is showing a numeric format?  Interestingly, it’s showing a decimal format now, but it shows the numeric format because all unpivoted columns had explicitly defined numeric formats already.

The final steps we did was to rename our columns and load to the data model, but the data types have been defined, so they were sent to the data model with Sales being a numeric type.

Why Values Become Text After UnPivoting Other Columns

Okay, so now that we know what happened, let’s look at what we get when we step through the updated data set.

  • First we pulled in all the columns.  We can plainly see that we have the new Apr column:

image

  • The Changed Type step is then applied:

image

Hmm… do you see that last data type?  Something is off here…

So when we originally created this query, Power Query helpfully pulled in the data and applied data types to all the existing columns.  The problem here is two-fold:  First, the Apr column didn’t exist at the time.  The second problem is that Power Query’s M language uses hard coded names when it sets the data types.  The end effect is that upon refresh, only the original columns have data types defined, leaving the new columns with a data type of “any” (or undefined if you prefer).

  • We then unpivoted the data, but now we see a difference in the output

image

Check out that Value column.  Previously this was a decimal number, now it’s an “any” data type.  Why?  Because there were multiple data types across the columns to be unpvioted, so Power Query doesn’t know which was the correct one.  If one was legitimately text and Power Query forced a numeric format on it you’d get errors, so they err on the side of caution here.  The problem is that this has a serious effect on the end load to Power Pivot…

  • Finally, we renamed the last two columns… which works nicely, but it doesn’t change the data type:

image

Okay, so who cares, right?  There is still a number in the “any” format, so what gives?

What you get here depends on where you load your data.  If you load it to the Excel worksheet, these will all be interpreted as values.  But Power Pivot is a totally different case.  Power Pivot defaults any column defined as “any” to a Text data type, resulting in the problems we’ve already seen.

Fixing the Issue

For as long as we’ve been teaching our Power Query Workshop, we’ve advocated defining data types as the last step you should do in your query, and this is exactly the reason why.  In fact, you don’t even need to define your data types in the mid point of this one, that’s just Power Query trying to be helpful.  To fix this query, here’s what I would recommend doing:

  • Delete the existing Changed Type step
  • Select the final step in the query (Renamed Columns)
  • Set the data type for each column to Text except the Sales column, which should be Decimal Number (or currency if you prefer)

image

When this is re-loaded to the Data Model, you’ll again be able to get the values showing on the Pivot Table as Sum of Sales.

Avoiding the Issue

Now, if you don’t want Power Query automatically choosing data types for you, there is a setting to toggle this.  The only problem is that it is controlled at a Workbook level, not at a global Excel level.  So if you don’t mind setting it for every new workbook, you can do so under the Power Query settings:

image

Is Changed Type Designed in the Correct Way?

It’s a tough call to figure out the best way to handle this.  Should the data types be automatically hard coded each time you add a new column?  If the UnPivot command had injected a Changed Type step automatically, we wouldn’t have seen this issue happen.  On the other hand, if a textual value did creep in there, we’d get an error, which would show up as a blank value when loaded to Power Pivot.  Maybe that’s fine in this case, but I can certainly see where that might not be desirable.

Personally, I’d prefer to get a prompt when leaving a query if my final step wasn’t defining data types.  Something along the lines of “We noticed your final step doesn’t declare data types.  Would you like me to do this for you now (recommended)” or something similar.  I do see this as an alternate to the up-front data type declaration, but to be honest, I think it would be a more logical place.

October News and Events

It’s a busy month here at Excelguru. Instead of a technical post we wanted to catch everyone up on our October news and events!

Live Course: Master Your Excel Data October News and Events

Ken is teaching a LIVE, hands on course in Victoria, BC on Friday, October 21 from 9:00am-4:30pm. This session is great for anyone who has to import and clean up data in Excel and will change the way you work with data forever! Ken will teach you how to use Excel Tables, Pivot Tables and Power Query. Space is limited to only 20 attendees, so don't miss out on your chance to sign up. For full details and to register for the session, visit: http://www.excelguru.ca/content.php?291-Live-Course-Master-Your-Excel-Data.

October News and Events: Power BI Meet-up

The next Vancouver Power BI User Group meet-up is happening on Thursday, October 13 from 5:30-7:00pm. Scott Stauffer, Microsoft Data Platform MVP, will be presenting on How to Operationalize Power BI. Together we’ll look at some solutions that might help pass your Power BI solution over to IT to manage enterprise-wide. Dinner and soft drinks will be provided. View the full details and sign up to attend at: http://www.meetup.com/Vancouver-Power-BI-User-Group/events/234126999/.

Microsoft MVP Award Received

For the 11th straight year, Ken has received the 2016 Most Valuable Professional Award from Microsoft! The previous 10 years, Ken’s award has been in the Excel category, but this year’s award is in the Data Platform category. The new category reflects the work he’s been doing this past year with Power Query and Power BI. Congratulations Ken, your guru status remains assured.mvp_horizontal_fullcolor

Our Team Has Grown

As we mentioned the other day, Rebekah Sax has recently joined the Excelguru team. She brings with her a wealth of experience in marketing, communications, event planning and administration. Please join us in welcoming Rebekah as she helps us make new connections and continue to grow.

Making Transformations Re-Usable

We're back with post two of Miguel's week.  This time, he is going to take his previous solution and show us how versatile Power Query can be, leveraging code he's already written and Making Transformations Re-Usable.

Have at it, Miguel!

Making Transformations Re-Usable

In my previous post, I was able to create a Query that would transform form Data to Rearranged Data with Power Query:

(Image taken from Chandoo.org)

...but what if I had multiple sheets or workbooks with this structure and I need to combine all of them?

Well, you could transform that PQ query into a function and apply that function into several other tables. Let’s dive in and see how to accomplish that!

You can dowload the workbook and follow along from here

Step 1: Transform that query into a function

The first step would be transforming that query into a function and that’s quite simple. You see, the query needs an input – and in order to undestand what that input is you need to understand how your main query works.

So our query starts by grabbing something from within our current workbook – a table Sonrisa

This means that our first input in order for our query to work is table so we’re going to replace that line that goes like this:

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

To look like this:

Source = MyTable,

What is "MyTable"?  It is a variable, which will return the contents that we've fed it.  Except we haven't actually declared that variable yet...

The next step would be to declare that variable at the top of the actual query, making the query start like this:

(MyTable as table) =>
let
Source = MyTable, [….]

And we now have the MyTable variable set up to receive a table whenever we call it (That is the purpose of specifying the as table part.)

After you do that, this is how the PQ window will look:

image

Now you can just save this function with the name of your choice.

Step 2: Get the files/sheets <From Folder>

From here, you’ll go to the "From Folder" option of Power Query and grab all the workbooks that contain the tables that you want to transform.

Then you’re going to use the Excel.Workbook function against the binary files of the workbooks that you need like this:

image

Next, you’re going to expand that new Custom column that we just created, giving us the sheets, tables, defined ranges and such that are within each of those workbooks.  The results should look like this:

image

You have to choose the data that you want to use which, in this case,  is stored on the Data Column as a table.  So let's create new column against that column.

Step 3: Apply the function

In order to create such column, we are going to apply the function to the Data column (which is a table) like this:

image

... and we'll get a new Custom column that contains a bunch of Table objects.  We can now compare that table from data against the newly created custom column:

image

(table preview from the data column)

image

after we perform the function against the data column

Final Steps:

Expand the new column (by clicking that little double headed arrow at the top,) reorder the columns and change the data type, until we have a final table that looks like this:

image

We're now got a table that is the combination of multiple sheets from multiple workbooks which all went through our transformation, and then were all combined into a single table.  All thanks to Power Query.

If you want to see the step by step process then you can check out the video below:

Transforming Data with Power Query

This week we've got a guest post from Miguel Escobar, who is going to tell us why transforming data with Power Query is his first preference versus other tools and methods.  In fact, this is part 1 of 2, and we're going to turn this week over to Miguel in full (I'll be back next week with a new post of my own.

Miguel, the floor is yours!

Transforming data with Power Query

Looking for some cool scenarios to test the power of Power Query in terms of transformation I stumble upon a really cool post that Chandoo made a few weeks ago where he basically needed this:

(Image taken from Chandoo.org)

he used a really cool formula as option #1 and for option #2 a VBA approach. Now it’s time to make an option #3 that I think will become your #1 option after you finish reading this! Sonrisa

Step #1: Make this a table

In order for this to work, you’ll need to make the range a table with no headers and that should give you a table like this one:

Column1 Column2 Column3 Column4 Column5 Column6 Column7
29-Sep 30-Sep 1-Oct 2-Oct 3-Oct 4-Oct 5-Oct
58,312 62,441 60,467 59,783 51,276 23,450 23,557
6-Oct 7-Oct 8-Oct 9-Oct 10-Oct 11-Oct 12-Oct
53,194 60,236 62,079 66,489 59,258 27,140 25,181
13-Oct 14-Oct 15-Oct 16-Oct 17-Oct 18-Oct 19-Oct
56,231 62,397 60,978 60,928 52,430 24,772 25,630
20-Oct 21-Oct 22-Oct 23-Oct 24-Oct 25-Oct 26-Oct
59,968 61,044 57,305 54,357 48,704 22,318 23,605
27-Oct 28-Oct 29-Oct 30-Oct 31-Oct 1-Nov 2-Nov
56,655 62,788 62,957 64,221 52,697 25,940 27,283
3-Nov 4-Nov          
53,490 5,302          

Also, if you want to follow along, you can download the workbook from here.

Since now we have a table, we can use that as our datasource in order to work with Power Query:

image

(Choose From Table in the Power Query Ribbon)

Step 2: The actual transformation process

Note: Before we start, I don’t want you to be scared of some M code that we’ll be showing you later. Instead, I’m going to show you how NOT to be scared of this crazy looking M code and tell you how it was originated. And no, I didn’t manually go into the advanced editor to do anything – I did all of my steps within the actual UI of Power Query Sonrisa 

So we start with 3 simple steps:

Add an Index column that starts from 0 and has an increment of 1:

image

image

Then we move forward with defining the odd and even rows with a custom column using the Index as a reference and the Number.IsOdd function:

image

After this, we will create another custom column based on the one we just created with a simple if function;

image

we can see that our table so far has 3 custom columns:

  • Index
    • that starts from 0 and has increments of 1
  • A TRUE/FALSE column that tells us if the row is odd or not
    • we used the Number.IsOdd function here
  • A column that basically gives the same number for the records in every 2 rows. So esentially we are creating a pair – a couple Sonrisa

The next step would be to remove the Index column as we no longer need it.

image

and now select the Custom and Custom.1 columns and hit the unpivot other columns option:

image

and now our query should look like this:

image

its all coming along great so farSonrisa

Now we create another custom column that will help us along the way. This will work as our column ID for the components of a row.

image

and now here’s where the interesting part comes. You need to hit the Add Step button from the formula bar:

image

and that should give us a query with the results from the last step which in our case is the “Added Custom 2” step. In this section we will filter the custom column to only give us the rows with the value FALSE.

image

You’ll go back to the “Added Custom 2” step and basically repeat the “Add a custom Step” process but this time you’ll filter it to be TRUE instead of false. I’ve renamed this 2 steps in my query to be DataSet1 and DataSet2.  (Right click the step in the "Applied Steps" box and choose Rename to do this.)

image

Tip: You can always check what’s going on in a step by hitting the gear icon right next to the step

and now we want to combine those 2. DataSet1 and DataSet2....but how?

image

so we hit the Merge Queries button but you’ll notice that it will only show you the tables and queries that have been loaded to our workbook or that are stored as connections. So for now we’re going to trick the system and just choose a merge between the tables that PQ tell us to choose from like this:

image

and that should give you this:

image

and we need to change what we have in our formula bar in either one of the DataSet2  to be DataSet1 so we can merge the data from DataSet1 and DataSet2 which should give me this:

image

And, as you can see, we have a column that needs our help. Let’s expand that new column but only the column with the name Value like this:

image

and the result of that should be:

image

We are almost there

So we have the data how we want it, but now we want to clean it as it has some columns that we don’t really need, so just remove the columns using the Remove Columns button from the Power Query UI (User Interface):

image

This looks more like it! but wait, we need to add some data type and perhaps rename those columns so it can be readable for an end-user:

So first we change the data type for both columns as desired:

image

and later we proceed with renaming the columns just by double clicking each column name:

image

in Chandoo’s solution he adds an index column for the end result so we’ll do the same:

image

and this is our final result after we reorder our columns just by drag-n-drop:

image

and once its loaded into our Worksheet it’ll look like this

image

Things to take in consideration

  1. This is a dynamic solution. You can add new columns and/or more rows and it will still work as desired.
  2. This WILL work on the cloud with Power BI so you can rest-assured that what we just described is something that we can refresh on the cloud. (VBA doesn't work on the cloud.)
  3. We could potentially transform this into a function and execute that function into multiple sheets, tables and even defined names within one or multiple workbooks. (Read more about that here.)
  4. If the result of this transformation has an extensive amount of rows, we could load it directly into our Data Model instead of experiencing the performance drawback of loading it into a worksheet. (Things get seriously slow when a workbook gets way too many rows.)

Power Query is a tool that was created specifically for scenarios that needed transformation like this one.  Microsoft is on a roll by delivering monthly updates of Power Query and you can bet that it will continue to get better and better each month, as it already has for the past year and a half to date.

Try Power Query if you haven’t done so yet. Download the latest version here