In this article I want to provide a quick start guide for Microsoft Power BI, detailing the steps involved in getting up and running with some of your data in a very basic way .
At Sontai we use Power BI in our customer projects for a number of reasons. Not only is it a recognised market leader by Gartner and comes backed by the Microsoft cloud services but because it integrates with lots of different data sources and works perfectly with other Microsoft applications such as Excel, PowerPoint, SharePoint or Teams, many of which are used by small businesses everywhere every day.
So, how do you get started with Power BI?
Downloading Power BI Desktop
Getting Power on your machine is easy. You can download Power BI Desktop and get started for free. If you're on a Windows machine you will be able to install Power BI directly from the Microsoft Store.
If you have admin rights for your PC you should be able to install the program without any issues. However, depending on how your business is set up, you may need to ask your IT team to install this for you.
Power BI is also updated regularly with new features so be sure to keep your application up to date.
Getting to know the Power BI interface
One of the good things about Power BI Desktop is the familiarity of the interface. If you've ever used an Office product, the look of the Power BI interface will feel like something you can understand.
The interface is laid out with the menu bar at the top, with a File Menu and then the links to the visual ribbons of Home, Insert, Modelling, View and Help.
On the Home Ribbon you can add data to your reports as well as access the ability transform that data. Inserting visual elements and calculations (measures) are also available on this ribbon.
The ability to publish your report to the Power BI Service (online) is also available on this ribbon. When you publish your report is adds anything you have created to your online workspace which you can access securely without needing the desktop application.
As the name may indicate, the Insert Ribbon is where you can add elements to your reports, from new pages to specific visuals, buttons shapes and images.
The Modelling Ribbon is where you can manage your data. From managing the data relationships to adding in calculations, parameter queries (what if scenarios), manage user security and add Q&A (for natural language quieries on your reports).
Within the View Ribbon you can activate and deactivate the side panes on the right of the interface, change to mobile view and select different colour and style themes for your reports.
Importing You Data
Once you've installed Power BI Desktop and become slightly more familiar with the interface, the first thing you need to do is connect to your data.
If you click on 'Get Data' from the Home ribbon you will be presented with a pop up window listing all of the data sources that Power BI can connect to.
Simply search for the connection you want to make and click 'Connect'. You will then see a series of prompts depending on the data source that you are trying to connect to.
Let's consider an example where you are connecting to an Excel file which is located on your desktop. Once you have clicked on 'Connect' you will see a window appear asking you to select your Excel file. Simply navigate to your desktop and select the file you want to connect to and press 'Open'. Power BI will then connect to your Excel file.
Once Power BI has established the connection to your data, you will see a window that shows you the contents of your Excel file. From here you can select the as many of the sheets that you want to load into Power BI.
My example file only has one active sheet which I have selected as below. If you would like to download this file for yourself, it is available at the bottom of this article.
Once you have selected the sheets you wish to load into Power BI, you can either load it straight away, using the yellow 'Load' button, or opt to transform the data. If you don't make changes to the layout of the data now, don't worry you can always go back and transform the data later.
As I want to do some basic data modelling, I will select 'Transform Data'.
Basic Data Modelling in Power BI
For the purpose of this blog, I want to focus only on basic data modelling, as data modelling in Power BI can take up several blog posts to explain all of the tricks and tips that can be done to get your data for visualising.
I want do want to touch on a couple of things that can be done to your data without getting too in depth. There are further steps that can be applied to the data for efficiency and ease in creating a proper schema, but this isn't going to be covered here.
What is Data Modelling?
Data modelling is the process of manipulating your data to get it into the right format for visualising on a reporting. This could be removing of duplicate values, adding new calculated columns, merging or deleting columns or rows along with many other steps.
It is highly possible that if you have been working with any data in Excel you have done some data modelling. You just might not have put a name to it.
Once you have pressed 'Transform Data' directly from the Get Data window then you will be taken to Power Query, where you can start to model your data. If you have already loaded your data and want to go back and make changes you can use the 'Transform Data' button from the Home Ribbon of the main report page.
Once the Power Query Editor window is open you will see a preview of your data table, with a familiar ribbon based layout. On the left you will see all of the queries that are present in your Power BI report. So, if you have added more than one sheet to the report, this is where you will see it. You can also see the query settings on the right, showing the series of steps that have been applied to your data as part of the data modelling process, some of which have been applied automatically based on the data that has been loaded in.
The list of applied steps is very useful as it gives you the ability to not only see the steps that have been applied to your data, as the anme would suggest, but you can also click back to review what the data looked like before the change was made. If you aren't happy with any of the steps you can also remove them by clicking the cross the left of the steps description. Be aware though, that removing a step in the middle may have a knock on effect to any subsequent steps.
From the Power Query Editor you can begin to add steps to manipulate your data.
1) Column Formatting
Applying the format of any column will tell Power BI what the column type is and therefore how to treat it. For example, you may set a column for sales value to be a currency because it realtes to money.
To change the format of a specific column simply click on the icon on the left of the column header and select the appropriate option from the list.
In this example, changing from a decimal number to currency, I would click on the '1.2' icon to the left of the 'Sales Revenue' column header and select 'fixed decimal number' from the list of options.
Once this has been selected you will be prompted to see how to deal with this request. The pop up box that appears will ask whether you want to replace a current step in the process or to add a new step.
This happens because as part of the automatic processing of the data, Power Query predetermined what the data column format should be. In this case it decided that it should be a decimal number. In changing this we have corrected the formatting that the system automatically assumed was correct.
For best practice, I would recommend replacing the current step as then there is only one step in formatting the data rather than two.
Once the change has been confirmed the format of the column in the data preview will update.
2) Removing Columns
It is quite possible that the data that is being imported has columns that aren't needed for analysis because they don't add value. Removing these can speed up the overall dataset as there is no need for Power BI to process the data.
There are two ways in which you can directly remove columns from the table, either right clicking on the column header and selecting 'Remove', or using the 'Remove Columns' button from the home ribbon. Doing either of these will add an applied step to the right hand side as you do this.
Whist there is nothing wrong with simply removing the columns individually using either of these methods, I prefer to have more control, which is a third way of removing columns.
For the third method, use the 'Choose Columns' button from the home ribbon, This will bring up a window for choosing the columns, with all of the columns displayed. Simply uncheck the ones that you don't want to appear and press OK.