A summary of the new features of Power BI Desktop introduced in November 2017
Part two of a five-part series of blogs

There's not too much to whet the appetite here: most of the changes are extensions to existing features, such as conditional formatting and column-by-example.

  1. Power BI Desktop - November 2017 Update Highlights
  2. Adding Columns from Examples - Further Improvements (this blog)
  3. Rules-based conditional formatting for tables and matrices
  4. Controlling Table and Matrix Cell Alignment
  5. Locking objects to prevent moving/resizing them

For a cumulative list of all of the updates to Power BI Desktop in the last few months, see this blog.  Our two-day Power BI Desktop course always uses the latest version of the software.

Posted by Andy Brown on 15 November 2017

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Adding Columns from Examples - Further Improvements

This excellent feature has now got even better! 

A reminder of the basic feature

For some months now Query Editor has allowed you to add columns by giving examples to Power BI Desktop:

Query by example

You can choose to add a column then choose Column From Examples to base it on your currently selected columns.

 

Here's the column selected for this example:

Selected column

The first few rows are shown - notice that they were all for purchases made in December.

 

You can type in anything into the first cell or so of the new column generated (often one value is enough to give Power BI Desktop the idea):

Month name

Here we've typed in the month name corresponding to the first listed date, which is enough for Power BI Desktop to guess our intentions.

This will create a new column (with, impressively, a sensible name) which you can keep by choosing OK:

Keeping a new column

Once you're happy Power BI Desktop has got it right, you can choose to keep or discard your new column.

 

So that's how this feature works.  What's new?

Conditional columns (text)

You can now map column values - here's an example.  Suppose that you want to create abbreviations for our four types of shopping centre.  You can type in mapping values for each as below:

Mapping column values

Note that there is no rhyme or reason to the abbreviations we've chosen, so we have to type in each one.

The rule created is more sophisticated than you might think.  Here's how you can view it:

Editing a conditional column

You can double-click on the column you've created to see the underlying formula.

 

Here's what you'll see for this example:

Conditional rule created

The column specifies that if the value of the centre type name is Factory Outlet, it will display FO; if it's Retail Park, it will display R Park; and so on for the other centre names.

Bucketing

You can perform a similar trick with numbers, to group them into buckets:

Bucketing - first go

When you type 10000 to 50000 for the first shopping centre's square metres area, Power BI Desktop assigns it to a band and guesses all of the other ones.

However, this feature isn't quite as clever as you might like:

Different band

If you type in another band, Power BI Desktop gets lost and reassigns all of the other numbers to null.

There are a few more new things you can do with column by example, but the above should be enough to let you decide for yourself whether this will be useful in your work.

This blog has 0 threads Add post