Home  >  Power BI / Power Query

Power BI – Import Text Using Examples

Published date

Last modified date

Author

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.

from import to table

YouTube Version

youtube video
https://youtu.be/6h7g-CwkeVg

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.

Enable import text using examples feature

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.

Showing Extract Table Using Examples 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.

entering first column into import text file using example

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.

showing stages of adding example values in

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.

look at the power query created by import text by using examples

How does import text using 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

step 3 when starting with a date

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

example 2 starting with the word date.

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

example 3 when there is no obvious pattern

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 import text using examples 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.

More Power BI Posts

Power BI Series

Categories:Power BI​Power Query

Laura GB

Over 20 year experience at being passionate about training, solving problems and loving a new challenge especially in the Microsoft's Power Platform suite.

Related Posts

1 Response