Power BI – Import text using examples

Import text using examples was introduced in the 2020 August update for Power BI. It writes the Power Query steps to transform a non-tabular text file into a table.

YouTube Version

Enabling the Feature

At the point of writing this blog post this feature is still in preview. The feature is enabled in File – Options in Preview features. It will like most features move to being enabled for everyone at some point.

Example Import text using Examples

For this example I have a very simple text file as shown in first picture in this post. It contains the details of journeys on different days. So when I select Get Data – Text/CSV and select the file, the dialog that shows a preview of the data in the file has a new button.

When the button is clicked a new dialog opens a dialog box with a data preview showing and an empty table. This means we need to enter in some answers into the table so a pattern can be calculated how to transform the data. When you start to type in a value it will suggest possible values.

A column is renamed by double clicking on the column title and entering a new name.

With only one value it cannot calculate the pattern required. When I have added more values it can determine the pattern. It often needs less values in future columns. In this example 2 dates were needed for the first column and then only one value for the remaining columns.

In the above example the Distance column was only part of the row of data. It calculates the ways to split the data. After this and all the columns have been added, we click Transform Data to see the Power Query steps that the import feature have been written.

Looking at the Power Query

So the magic behind this new feature is it writes a 10+ step query for you with a pretty good pattern for doing this. So the 5 steps after the source are working out how many rows make a block of data and grouping that data into one block. The next chunk of steps are splitting that block into separate columns.

How does import text by example decide the block length?

This for me is the clever part. Step three where it adds the conditional column is the crucial step. So I looked at three slightly different versions of the text file to see how it changes.

Example 1

In this example each of the blocks start with a date, which means the line starts with a number. Step 3 uses a test to see if in position 0 there is a number. So for this example, we could have as many rows as we liked in a block as long as the first row is the only row starting with a number.

Example 2

In this example the blocks start with the word “Date”. Step 3 looks for the line starting with “Date” as the first line. This is pattern is a great one as it works fine for different length blocks as long as the only lines starting with “Date”.

Example 3

In this example there is no easy way to identify the top row of the data. This means the only pattern it can determine is by length of the block so it guesses 4. This works if the data is in same length blocks.

Conclusion

I like the new feature and I can see places where it will get used. I hope it gets progressed to recognise more patterns and be more flexible. When it works it stops the report writer needing to put together the Power Query steps to do the transform.