We’re here for more data documentation fun in this post. While we have a post about documenting our data tables and columns in Power BI Desktop proper, there’s also some unsung (but awesome) data *transformation* documenting options in Power Query Editor.

Just like documenting where your data is coming from and what it means (and what your DAX measures do and why they exist), what we do in Power Query when we are cleaning and transforming data is just as important to document.

This allows your future self (or anyone else who may inherit your report) to easily understand the changes you’ve made in Power Query.

We’re going to use a very simple dataset to demonstrate how this works, and if you’d like a copy of the Power BI file I use in the below video and written instructions, you can download it here:

As well, since we’ll be using Power Query Editor in this tutorial, you’ll want to link up your own copy of the spreadsheet we are using for our data source. You can get your own copy of it right here:

Cool. You’re good to go. Let’s do the video and written tutorials now.

Video:

Written Post:

We’re starting with a Power BI report with just a small simple dataset connected…

Remember, we’re going to be going into Power Query Editor in this tutorial, so make sure you’ve switched the data source in the Power BI file to the Excel file you downloaded above.

Our report, if we go to the Table View, just has a small simple dataset in it that looks like this:

However, we want to be able to build a report with a State slicer, and our State information is mushed up in the same column as City information, and really, we need it in a column by itself.

We’ll use Power Query to split this column into two.

Let’s go back to our Report View and click Transform Data to open up Power Query Editor.

Once it opens in a new window, we’ll split our Location column by right-clicking on it, selecting Split Column, then by Delimiter, because we need to split it at those commas between City and State.

If you want to learn more about Splitting data columns like this check out the tutorial on that:

We now have our column split into two (and renamed) here in Power Query Editor, and you can see we have a “Split Column by Delimiter” Applied Step over on the right hand side of our screen.

This is a simple example, but honestly, some data sources need a LOT of transformations to get into the right kind of shape to visualize with… and when you do a lot of transformations, you get a LOT of Applied Steps… and it can be hard to remember exactly what you did (or figure out what someone else did) by the names of the Applied Steps.

This is why it’s good to document our transformations right HERE in Power Query editor.

There are 2 ways to do this.

1. Descriptive Step Titles

The first way is to rename your Applied Steps so their names make sense, and give us detail.

For the Split we just did above, we’ll want to add some more detail to the name.

We can do this easily by right-clicking on the step and selecting Rename:

You can then give some more information right there in the step name, like calling it “Split Location column into City and State columns”.

Cool, right?

2. Describe the step in more detail in Properties > Description

The 2nd way to document how you transform data is by filling in the Description field for a transformation step. This is a bit hidden, but all you need to do is right-click on a step, and select Properties:

This gives you an entire Properties window with a Description field, where you can write basically as much as you want about that step!

Awesome, right? Now we can document EVERYTHING about every transformation we do so that 1) we can remember everything we did if we need to in the future, and 2) someone else can figure out how we transformed the data.

Highly recommended!

Take care everyone,

Joe.

Like these posts but need more formal (but still engaging and fun) training in Power BI? Contact me, Joe Travers or at joe@traversdata.com. I got you.