Browse 554 attributed reviews, viewable separately for our classroom and online training
Details of the Power BI Desktop Update for April 2021
Part two 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 (this blog)
  3. Suggested tables for Excel (and JSON improvements)
  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.

Loading Text and CSV files by example

Occasionally Microsoft introduce a new feature into Power BI which it's hard not to rave about - this is one such feature!

The problem

Not all CSV or text files are neatly formatted.  Here is one which definitely isn't:

A messy text file

A messy text file, containing the names, roles and dates of birth of Wise Owl employees.

Normally when you load this you would then have to transform the data yourself:

Transforming data

When you try to load the data, this is what you initially see.

 

Extracting tables using examples

As from this month's update to Power BI, you can now click on the Extract Table Using Examples button shown boxed above to train Power BI in what to import.  Here's what you initially see:

Choosing the first column

Double-click at the top of the first column to choose what it should display (it's not at all obvious that this is what you should do).

Choose the first column's value:

Choose the person name

Choose Wendy Wise, the first person's name.

 

Rename this column, then double-click at the top of the second column and choose the next value:

Second column

The second column should show Wendy's role: CEO.

Now do the third column:

Date of birth

You've told Power BI the three columns it should import now, but crucially it doesn't know where each record ends.

You need now to give Power BI one more bit of information - the start of the next record of data, so that it will know where to break:

Next row

Choose the start of the next record.

Once you've done this, Power BI can extrapolate your choices!

The final set of rows

The final set of rows.

Reviewing the transformation steps

When you choose to go into Query Editor to see how Power BI has transformed your data, you can see each step:

Steps created

The steps created - you can click on each one to see its effect.

 

The main transforms are as follows:

Transform What it does
Added Index Numbers the rows
Added Conditional Column Uses the M Mod function to divide the rows into groups, each having 6 rows in.
Grouped rows Concatenates the values for each of the 6 lines in each group into a single item.

It's all very clever - you can try it yourself with this text file.

This blog has 0 threads Add post