WiseOwl Training - Established 1992 Wise Owl Training

Established May 1992
30 years in business
Wise Owl Training
30 years in business
See 519 reviews for our classroom and online training
Details of the Power BI Desktop Update for April 2021
Part three of a five-part series of blogs

The April 2021 update to Power BI Desktop makes it easier to load text, CSV, JSON and Excel files and includes a wider range of shapes, with more drawing effects

  1. Details of the Power BI Desktop Update for April 2021
  2. Loading Text and CSV files by example
  3. Suggested tables for Excel (and JSON improvements) (this blog)
  4. New and better shapes
  5. Inverting axes of charts

We've been creating our idiosyncratic monthly blogs on Power BI updates since November 2016, and also deliver online and classroom Power BI courses.

Posted by Andy Brown on 28 April 2021

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.

Suggested tables for Excel (and JSON improvements)

This feature shows suggested tables when you load data from an Excel workbook (as shown here) or from a JSON file.

When using the JSON connector Power Query will automatically apply transformation steps to flatten the JSON data into a table, as well as accommodating files where each line in the file is a JSON string.

The problem this feature is addressing

Consider this Excel worksheet (feel free to download it to try this out yourself):

Messy Excel worksheet

The worksheet contains 3 tables, but will Power BI find them?

Here's what Power BI detects by default:

Excel ranges detected

You're going to have your work cut out transforming this data in Query Editor to extract the 3 tables!

A digression: you could solve this with range names

Before I continue, I have to mention that I don't think the new facility is the best way to solve this problem.  It would be much better to assign each table a range name:

Assigning range names

Here I'm giving the shopping centres table a range name, for example.

You can then choose these when loading data into Power BI:

Range names for tables

You can choose the ranges of data you want to load.

Having got that off my chest, I can now show how the new Excel feature works!

Featured tables in Excel

If you try to load our original messy worksheet, you can choose at the bottom left one or more suggested tables:

Sugggested tables

However, notice that only two tables have been picked up (for some reason the table of regions hasn't been detected).  Power BI also has no way of naming the tables automatically.

If at all possible, it's far better to either include each Excel table on a separate worksheet or to give each table a range name (as shown above).

This blog has 0 threads Add post