BLOGS BY TOPIC▼
BLOGS BY AUTHOR▼
BLOGS BY YEAR▼
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
- Details of the Power BI Desktop Update for April 2021
- Loading Text and CSV files by example
- Suggested tables for Excel (and JSON improvements) (this blog)
- New and better shapes
- 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):

The worksheet contains 3 tables, but will Power BI find them?
Here's what Power BI detects by default:

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:

Here I'm giving the shopping centres table a range name, for example.
You can then choose these when loading data into Power BI:

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:

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).
- Details of the Power BI Desktop Update for April 2021
- Loading Text and CSV files by example
- Suggested tables for Excel (and JSON improvements) (this blog)
- New and better shapes
- Inverting axes of charts