When using Power BI, the first thing to we need to do is ensure we have good data to work with. We need to ensure we are working with transactional data organised into records. For this we use a process called ETL, Extract, Transform and Load. So, for the first stage, Extract, we need to choose the file format to import and as I mentioned in my last blog you have lots to choose from, on my last count I made it over 80! That includes things such as Excel, CSV, Sharepoint, Oracle databases, SAP, Dynamics and Google Analytics. So quite a choice there.

The next step is Transform and this is where Power BI has lots of tools to help you.

Removing unwanted columns and rows

These tools allow you to strip out null information, get rid of rows of data with perhaps summary lines or other information you don’t want and columns you just don’t need. You can remove top and bottom rows/columns or use the filtering options to filter out rows of data you do not need. You can also remove duplicated rows of data quickly and easily.

Setting header rows

Power BI gives you the option to promote rows to become headers, so if for example you have removed unwanted top rows, instead of Power BI guessing what your column headings are you can choose which row to use for headings.

Splitting columns

Like in Excel if you have columns of data all grouped together you can choose to split these into several columns, e.g. name and surname, part numbers, etc. You can either choose to split with delimiting characters or by a fixed number of characters.

Replace values

If you have data that is incorrect and you want to change it you can use the Replace Values option to do a global find and replace throughout your data.

As you make these adjustments Power BI records all your steps in a panel on the right-hand side of the query window. This means you can make adjustments to any step you have made, delete steps if you no longer want to do them and most importantly, every time your data changes in the source file and you refresh it, Power BI will automatically work through your steps making the changes to your data to transform it ready for use.

BI box

So, you only have to build the steps once and then they are automatically executed on refresh every time after that.

To help you master Power BI, we run a two day Power BI course, to find out more about this please click the following link Power BI – Introduction. See when the next training course is running, by viewing our public programme dates.

Alternatively if you have a group requiring training we can organise dates to suit you.

Please feel free to get in touch if you have any questions on 0203 9503730 or by email on training@underscore-group.com.

Read Next

Excel Power Query and Power Pivot

As we all know Excel is a very powerful tool, however sometimes where your data is spread across various spreadsheets and other files trying to summarize it all can be tricky.  You can add [...]

What Are Macros and Why Should You Use Them?

What Is a macro? A macro is shorthand for a programming term: macroinstruction. In the most basic terms, a macro is a way to automate simple tasks. They can be used in a variety [...]

  • woman doing virtual learning on laptop

Making the Most of Technology in the Workplace

While almost everyone these days uses some form of modern tech to benefit their lives, there remain a surprising number of people who continue to resist the influx of technology, and only use it [...]