Power Query Dependencies Viewer

The November 2016 update is now out and it finally brings a way to view the Power Query dependencies viewer.  While it’s been out in Power BI Desktop for a while, (as Matt posted about a while ago,) this is huge news for Excel, as this feature has been badly needed.

Viewing Power Query Dependencies

To get to the Power Query Dependencies view, you simply need to perform the following steps:

  • Edit any query (just to get into the Power Query editor)
  • Go to the View tab
  • Click the Query Dependencies button

image

Once you do so, you’ll be launched into the Power Query dependencies windows as shown below:

image

At first glance…

So at first glance, this is pretty exciting and yet – if you work with complicated Power Query setups like I do – you’ll find the Query dependencies view a bit… lacking in some areas too.

First off, if your query is complicated, it really does open that small.  Wow.  Now there is a scaling button down the bottom, but that quickly scales so that stuff is off-screen.  No problem, right?  We’ll just drag some stuff around then… oh… except we can’t.  Dragging any box around drags the entire model, not just that one box.  Sad smile

What can you do with the Query Dependencies viewer?

Rather than focus on the stuff we can’t do, I want to take a look at what we can (although I won’t be able to help making a couple of suggestions here as well.)

Maximizing the model layout

The first thing to point out is that despite the fact that it isn’t obvious, the window is resizable.  If you mouse over any border or corner you’ll get the arrows that indicate you can left click and drag to make the window bigger.

So normally the first thing I do is:

  • Move the window to the upper left of the screen
  • Drag the bottom right corner to make the model fill the entire screen
  • Click the little box icon in the bottom right corner by the scroll bar to “Fit to Screen”

After all, the reason I’m using this view is because the models are big!

Some things that would be really useful here:

  • It would be awesome if there was a Maximize button near the X in the top right (like the Power Query window and every other app has.)
  • It would also be good if we could double click the title bar and have it maximize the window (again, like so many apps out there.)

Either (or both) of those features would save me a lot of time.

Alternate Views for Tracing Query Dependencies

In the default view, the data sources are plotted at the top, and the queries cascade down below.  Fortunately you’re not stuck with this view, there are four different ways to display the model:

image

In this instance I’ve chosen Left to Right, which puts the data sources  on the left and fans the query dependencies out to the right hand side.

Honestly, if I had my preferred way it would probably be to use Bottom to Top (data sources at the bottom and data model tables on the top.)  To me this should basically “bubble up” the model tables to the top of the screen.  Unfortunately it doesn’t quite work like that… all we can guarantee is that the data sources will be at the bottom, but the model tables could end up anywhere.

Ideally, I’d love to have an option to force the Data Sources to be lined up based on the first choice in that menu, and the Load Destinations (whether table or data model) be lined up in the viewer based on the option chosen for the second choice.  This would allow me to easily see the “From” and “To”, with the chain of what happened in between.

Tracing Query Dependencies

In the image below (click on it to see the larger version), I’ve selected one of the tables in the middle of the query dependencies tree:

image

The effect is that it highlights all child and dependent queries in the data flow.  That’s cool, and I’m okay with this being the default behaviour when I select a query step.  Wouldn’t it be cool though, if we also had:

  • A right click option to trace precedent queries only
  • A right click option to trace dependent queries only

Those would be super helpful in tracing a queries flow without the extra noise, something that is really important in able to quickly dig in to the key factors you probably want to know about your query dependencies.

Identifying Load Destinations

So the very first thing I did when I threw this specific model into the query dependencies view was identify two queries that were not in the query chain.  “Awesome,” I though, so I went and deleted them.  Then I restored from backup, as one of them was in use!

Don’t get me wrong, the view was correct, it’s just that the distinction for load destinations is so weak that I saw no arrows and assumed it was good to be removed.  As it turns out, the words actually matter here:

image

The Day Types table is created from a hard coded list.  Since there are no queries flowing in or out of it (it is floating above the lines) I nuked it.  I missed the fact – especially with it being on the left), that it was actually loaded to the data model.

Raw Data-Departments, on the other hand, is pulling from the Current Workbook and is loaded as “Connection Only”.

So here’s my thoughts here:

  • I’d love to see nodes that are loaded to worksheets or the data model identified.  Either an icon in the top right, or a shading  in place would be ideal.  Something that makes them a bit less subtle than they are today.
  • I’m not a fan of the “Not loaded” term… it’s about as awesome as the “Load Disabled” that Power Query used to use about two years ago.  This should – in my opinion – be consistent with the UI and should read “Connection only”.  Not loaded makes it look like it isn’t working.

Navigating Query Dependencies

One of the issues I had above is that my Day Types table – being standalone – should not sit on top of any arrows… that’s just scary bad and misleading… but that’s actually part of a much bigger issue as this is kind of the style used throughout the entire tool:

image

This also leads me to another issue in that I need to be able to follow these arrows.  Today the only ability you have – because you can’t move the boxes – is to essentially print the query dependencies window (you’ll need screen capture software for that since there isn’t a print button) – and trace with a highlighter.

What I’d love to see in this instance is the ability to select a single (or multiple arrows) and have them turn bold.  It would be an even bigger bonus if they shaded the tables on each end of the arrow and allowed you to select multiple arrows.  That would actually solve a few issues mentioned earlier too, allowing us to really drill into the relationships we need to trace.

Overall Impressions of the Query Dependencies Viewer

Overall it’s a good first version.  I’d really love to see some (or all) of the improvements I mentioned above, but it’s a HUGE amount better than what we had a month ago.  Smile

Extract Data from a Mixed Column

More and more I’m seeing examples where people are trying to extract data from a mixed column.  In other words, they have two data types in a single column, but need to find a way to extract one from the other.

Examining the issue

The sample data I’m using can be downloaded from this link.

I’m going to use Power BI Desktop for this, but the results will look identical in Excel using Power Query (except for the colour, of course.)

So let’s get started:

  • Get Data (new Query in Excel) –> From CSV –> MixedDataInColumn1.csv
  • Promote First Row as Headers

The issue can be seen in the red circles below… the report author injected the name of each vendor for the parts above their first part in the list.

image

So the issue here is how to extract the vendor name from Part No column.  The problem is that there isn’t any obvious way to do this.  We have different textual values in all columns, which could change over time.  There’s really nothing that we can test for reliably in this case.

How to Extract Data from a Mixed Column

There are actually a few different ways to extract data from a mixed column… a few of which we demonstrate in our Power Query workshop.  I’m going to show just one here.

Step 1 – Identify a column with a pattern you can exploit

The key we are really looking for is a column which has values or dates for all rows other that the one with our vendors.  In this case we actually have two: Part No and Cost.  Both have text on the Vendor lines, but what looks like values on the rest.  The challenge we have here is that we can’t always guarantee that Part No won’t have text in it.  It’s completely possible we could see a part number like TH-6715 or something. So this leaves us with the Cost column.

Step 2 – Duplicate the identified column

This next set of steps is actually the trick that lets us work this out.

  • Right click the column in question and choose Duplicate Column
  • Right click the Cost – Copy column –> Change Type –> Whole Number
  • Right click the Cost – Copy column –> Replace Errors –> null

You should now have null values where the textual values were located:

image

Step 3 – Use a little conditional logic

We now have something that we can use in order to extract the Vendor name.  So let’s build a little bit of conditional logic:

  • Add Column –> Conditional Column
  • Configure the Conditional Column as follows:

image

The only trick here is to make sure you change the Output to a column so that you can select from the list of columns.

  • Click OK
  • Right click the Vendor column –> Fill Down

The result is shown below:

image

Step 4 – Clean up

We’re now at the point of clean up which entails:

  • Filter the Cost – Copy column to remove null values
  • Delete the Cost – Copy column
  • Set the data types on all columns

The results now look as follows:

image

At this point we can commit the query and we are good to go.

Final Thoughts

This is not a new trick by any means; I’ve been using it for a long time.  The biggest key is really about identifying patterns and thinking outside the box.

It’s unfortunately very easy to get focused on the primary column we want to solve, and lose site of the others.  (Trust me, I’ve been there too.)  Sometimes though, when a column is particularly tough to deal with, we need to just step back and take a look at the bigger picture to see if there is a pattern in another column that we can exploit.  In fact, I’d say that this is probably one of the most important things to master when working with Power Query.

Using Aggregate to Count Visible Rows

In this post I’m going to show one of my favourite financial modeling tricks: how to use Aggregate to Count Visible Rows.

Background

Often, when I’m building models in Excel, I like to group key assumptions at the top of the worksheet in one area. This allows me to change them easily from a centralized location. The problem is that sometimes I need to collapse them to see more of the model.  Of course, you can use this trick to collapse any block of rows (or columns) in your worksheet, so it’s applicable to all kinds of uses.

Let’s take a look at the basic setup:

image

So it’s essentially a block of cells to capture key rates and stats.  No secret there.  And on the left I’ve added some outlining so that I can collapse it easily.  To do that we simply select rows 3:6 and go to Data –> Outline –> Group.

The Trick in Action

Now, check this out… I click the – on the left, and the rows collapse.

image

But check out the message in cell A7.  It wasn’t there before, but now we’ve got a nice message that not only tells you there is an area that is collapsed, it also leads the user as to how to show the rows again.

Using Aggregate to Count Visible Rows

The trick to this is using the AGGREGATE function (which works in Excel 2013 or higher).  So let’s check out how this works.

As AGGREGATE gives us back a count of rows, we will be able to test if the number of visible rows equals zero, and the react to it using an IF function.  So let’s get started.

AGGREGATE's first parameter: the Aggregation Type

=IF(AGGREGATE(

When we open the parenthesis, we are prompted for the first parameter.  There are a variety of options here, but the one I want is COUNTA(), which allows us to count the number of completed cells (either text or values):

image

Next up we put in the comma and we’re on to the second parameter.

AGGREGATE's second parameter: What to aggregate

image

Aha!  So using 5 will allow us to apply the COUNTA(), but ignore any hidden rows.  So it’s this parameter here that allows us to use AGGREGATE to count visible rows only.

AGGREGATE's third parameter: The data to aggregate

On to the next comma and now we need to select the range to count.  Now in this part we have two options.  Personally, I prefer to provide the range of the cells that will be hidden.  In truth though, you only really need to refer to a single cell in the range that will be collapsed.  Here’s what I went with:

=IF(AGGREGATE(3,5,A3:A6

Wrapping up the IF test

Perfect, and now we can just close the parenthesis and complete the test:

=IF(AGGREGATE(3,5,A3:A6)=0,

So, if the count of visible cells equals zero then… what do we want to do?

The IF test: If there are no visible rows...

This is the part that I think really makes this trick work.  I really like providing the arrow key to point to the + icon that shows up, and adding the additional wording as needed.  This allows my users to know not only that there is hidden data, but how to display it again.  So for me, that message might look like:

  • “<-- Show assumptions”
  • “<--Click to expand Revenue assumptions”

You get the idea.  For this example I’ve gone with the following:

=IF(AGGREGATE(3,5,A3:A6)=0,“<-- Show assumptions”,

The IF test: If there are visible rows...

And finally, we round it off with the messaging to provide if the count of visible rows is greater than zero (i.e. if the section is expanded).  Depending on what you want your model to do and how you want to display things for your end users, this could be something like:

  • “End of Assumptions”
  • “Total Revenue”
  • “Please insert new rows above this line”
  • “”

I think the first three are fairly self explanatory, but the last one is essentially two sets of double quotes.  Since everything between the quotes is returned to the cell as text, and there is nothing between the quotes, we get an blank cell.

The complete formula to use Aggregate to Count Visible Rows

Using that method, the finalized formula reads as follows:

=IF(AGGREGATE(3,5,A3:A6)=0,“<-- Show assumptions”,””)

Final Thoughts

My clients love this little trick. It’s fairly easy to set up, and is super useful for allowing people to hide/show the model sections that they want/need to review, without having them bogged down with all the info.

I also find it very useful when we’ve got multiple scenarios laid out on the worksheet. Say I need to look at… scenario 1 and 3 at the same time, I can compress 2 and just focus on the stuff I need to look at, avoiding scrolling up and down.

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.

Opportunity: Influence Excel Charting Features

Hi everyone, I’ve got a quick message/opportunity for you from the Excel team.  In their words:

This survey is being conducted by the Microsoft Excel team. In the future, new charting features will be added to Excel. We want to better understand how you expect charts, that have new features, to be displayed and behave in older versions of Excel that don't have these new features.

This survey will take approximately 10 minutes. Feedback from this survey will be used to improve the user experience in Excel. Responses to this survey will not be associated with any personal information.  Please see the Microsoft Privacy Statement for further privacy details on all Microsoft products.

Thank you for participating,

Excel Team

Click to Start Survey

Fix: Excel Formulas don’t update in Power Query tables

If you’re new to Power Query, chances are you’re more comfortable doing tricky mathematics using Excel formulas, rather that Power Query formulas.  No shame there, but you’ve probably run into a situation where you set up the formulas, refresh your query and the Excel formulas don’t update in Power Query 's output table.

I’ve worked with this issue for a long time, and it’s actually caused me to avoid using Excel formulas in tables generated via Power Query all together. Having said that, there is now an easy way to fix this which renders that avoidance obsolete.

The Issue:  Excel Formulas don't update in Power Query tables

Let’s take a quick look at this scenario.  We have a simple table called Animals as follows:

SNAGHTML2968fa38

And it gets landed in another table.  But in this table, we added a new column called “Est” to the end, which holds the following formula: =[@Price]*[@Quantity]

SNAGHTML296a33f0

So far so good, but what happens when we add a new line to our Animals table and refresh it?

SNAGHTML296c10ce

Plainly, this is not good at all!

The Fix:  Excel Formulas don't update in Power Query tables

The fix is remarkably simple, once you know what to do:

Step 1: Change the Table Design Properties

  • Select any cell in the OUTPUT table (the green one)
  • Go to Table Tools –> Design –> Properties (External Table Data group)

SNAGHTML2970d8de

  • Check the box next to Preserve column soft/filter/layout and click OK

image

Now, at this point, nothing appears to change.  In fact, even refreshing the table seems to make no difference.

Step 2: Ensure the Formulas are consistent

The reason the formulas didn’t fill correctly for us is different now.  It is entirely based on the fact the formula in the last column is no longer consistent.  Naturally, that means that Excel won’t auto-fill the formula, as it doesn’t know which is correct (the formulas or the blank cell.)  We need to fix that before this will work for us.

  • Copy from the first formula cell down the entire column (I've got reports that this DOES matter, and that copying from another cell may not fix it.)

Our data should now look something like this:

SNAGHTML297810e4

Step 3:  Test it

And now, when we add new data and refresh the Power Query…

SNAGHTML297934e2

Wrap-up Thoughts

On my Excel 2016 this behavior is now default.  I don’t know when it changed, to be honest.  And if your behavior is different, I’d love to know.  I’m running the Office Pro Plus subscription – first release.

On Excel 2010/2013, the old default of not updating the tables appears to prevail.  It’s actually for this reason that I covered this, as it came up as a question in my Power Query forum.

I’m not sure if this is good or bad, but this setting can/must be managed for each output table individually.  There doesn’t seem to be a way to set one behavior or other to apply to all tables.  To be honest, I think they’ve got it right in Excel 2016, so at least it’s fixed if you’re current.  (And for reference, my understanding is that this required a patch to Excel, not Power Query, which is why I suspect that we likely won’t see it fixed for Excel 2010/2013.)

Do you use (legacy) Get External Data features?

Hey everyone,

A contact at Microsoft is looking for people who DO NOT use Power Query extensively, but DO use “legacy” methods to get external data into Excel.  These “legacy” methods include using:

  • Any/all of the commands from Data (Tab) –> Get External Data (group)

SNAGHTML5cf2dc50

  • VBA to retrieve, clean up and/or land data in Excel

In his own (okay, slightly edited) words, my contact is looking for users:

…who are importing data using the classic Get-Data Excel capabilities and have very little (or zero) familiarity with Power Query. [I’d like to] get their feedback on new ribbon sketches.

UPDATE:  My contact at Microsoft now has sufficient users for his study, so no more users will be accepted at this point.  Thanks!

This is a cool opportunity to get in touch with Microsoft and give your impressions and feedback on some potential designs that they are thinking about for future product improvements.  If you are interested and are not a heavy Power Query user, just drop a comment below (please don’t include your email.  I can access that via the blog control panel.)

July 2016 Power Query Update

Hey folks,

I'm actually on vacation, so this post is going to be short.  I just wanted to make sure you all are aware that there is a new Power Query update available.

New features in the July 2016 Power Query update:

  • New SAP HANA connector.
  • New SharePoint Folder connector.
  • New Online Services connectors category.
  • Improved DB2 connector, now leveraging the Microsoft driver for IBM DB2.
  • Improved Text/CSV connector, now exposing editable settings in the preview dialog.
  • Improved relational database connectors, now including Schema information as a part of the Navigation hierarchy.
  • Data Source Settings enhancements, including “Change Source” capability.
  • Advanced Filter Rows dialog mode within the Query Editor.
  • Inline Input controls for Function invocation within the Query Editor.
  • Support for reordering Query Steps within the Query Editor by using drag and drop gestures.
  • Date picker support for input Date values in Filter Rows and Conditional Columns dialogs.
  • New context menu entry to create new queries from the Queries pane within the Query Editor.

My Thoughts (without actually using it yet)

Now you can get full pictures at the official blog from Microsoft, but I'll just call out a couple that I think are pretty darned important from a usability perspective.

  1. Continuing with last month's update where we got Drag and Drop for the query groups, we now get Drag and Drop for the query steps.  That is just plain AWESOME.
  2. The new Advanced Filter dialog looks pretty good.
  3. The Date Pikcer also looks pretty helpful.
  4. A context menu to create new queries is also SUPER helpful.  One thing I'd like to see added here, is the ability to set each new query to load to connection/table/data model from INSIDE the query editor.  (Currently, the choice you make is applied to ALL new queries - the main reason I have my defaults set to load to connection only.)

June 2016 Power Query Update

Yesterday, Microsoft released the June 2016 Power Query update.  Even though there are only four items on the list of new features, some of them are quite impactful.

What’s new in the June 2016 Power Query Update

The four new features are:

  • Conditional Columns
  • Column type indicator in Query Editor preview column headers
  • Reorder Queries and Query Groups inside Query Editor via drag and drop gestures
  • Query Management menu in Query Editor

Microsoft has a blog on this here, but let me hit these quickly in reverse order to give my comments as well:

Query Management Menu in Query Editor

Honestly, to me this is kind of a throw away waste “button for the sake of a button” kind of feature.

image

Does it make things more discoverable?  Maybe.  But we can get to all these features by right clicking the query in the Queries pane on the left of the editor.  Personally, I would have rather seen them give me a feature to “pin” the Queries pane open and set that as a default, as I find the navigation from that area much more useful:

image

 

Reorder Queries via Drag and Drop

This is great… so great in fact, that the only real question is why it hasn’t worked in the past.  Time & resources is the answer, but it’s now working the way you’d expect it to work.

image

PS, if you don’t know how to group your queries, right click on one, say “Move to Group” and select New Group.  Pretty handy for keeping things organized.

Column Type Indicator

This is BY FAR the most important of the upgrades.  The reason is that this has been a deadly area of weakness since day one.  If you’ve ever been burned by an “any” data type, you know why.  And if you haven’t… hopefully this will help ensure you don’t.

We can now plainly see which columns have been defined with each data type:

image

Notice how easy it is to tell that the “Client, Task and Notes” fields are text (as shown by the ABC icon in the column header.)  Hours is a decimal number, rate is a whole number, and Date… is undefined.  That one needs attention as indicated by the question mark.  Very visual, and very badly needed for  a long time.  This one feature is, in my opinion, worth the upgrade.

Conditional Columns

This is also a pretty cool feature, as it lets a non-coder build an if then else (if) statement.  Full caveat here: this is the image from the official Microsoft blog, not one of mine, but it shows you the general idea:

June-2016-updates-for-Get-Transform-in-Excel-2016-1.png (1282×809)

As cool as this is, there are some issues here:

  1. You can only feed out full columns as outputs, not formulas/equations.  So if I wanted to check a column and return [Hours]*[Rate] in one case and [Hours]*1.5*[Rate] in others, it won’t work.  (Instead I’ll get text.)  To do that you’ll still need to write your formulas manually.
  2. You can’t provide IFERROR style logic to check if something errors and react accordingly.  To do that you’ll still need to create your own custom column formula using the “try otherwise” formula.
  3. Assume you created a custom column using the “Add Custom Column” button, and manually wrote your “if then else” formula.  You then committed it and want to change the logic, so you click the gear icon in the applied steps window… and you’ll be taken to the Conditional Column interface shown above, not the original window where you can create more complex logic.  So if you want to modify that formula to be more complex than this new interface allows, you’re now going to have to go to the Advanced Editor window.  I have suggested to Microsoft that they need a button to return is to the previous interface for this scenario.

Despite the shortcomings, we should recognize that this is a great new feature.  You can test if one column compares (match, doesn’t match, greater than, etc) another column or specific value without having to manually write any M code formulas.  You also aren’t obligated to feed out a column’s value, but rather can feed out text or values too.  So as long as your logic needs are fairly simple, you can use this feature.

Download the June 2016 Power Query update

You can pick it up from Microsoft’s site here:  https://www.microsoft.com/en-ca/download/details.aspx?id=39379

Also, I’ve started holding on to the previously released installers should you ever need to regress to a prior version.  You can find the installers I have in my forum here:  http://www.excelguru.ca/forums/showthread.php?5745-Installing-Power-Query