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.
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.
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.
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. Our next training date is 3-4 April running at our Gatwick training venue, or view a full list of 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 firstname.lastname@example.org.