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.
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
Power BI Advanced Training: My 5-Year Journey
In today's fast-paced business world, making sense of vast amounts of data can be challenging. That's where data visualisation comes in - a world where numbers don't just sit there but tell compelling stories. [...]
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 [...]
Why Digital Skills Need to be Part of Every Talent Development Strategy
According to Gartner there is a huge skills shortage in IT. As the COVID-19 response accelerates the speed and scale of digital transformation, a lack of digital skills could jeopardize companies with misaligned talent [...]
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 [...]
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 [...]
Using Advanced Formulas and Functions in Excel
With everyone using Excel more and more in business, the level of skill and knowledge required has increased hugely. Just being able to create a simple spreadsheet and input data is often no longer [...]