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.
Home
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.
Insert
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.
Modelling
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).
View
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.
Using this method I have can easily see all of the columns, and I can search and sort the names without changing the table itself. More importantly, once I have pressed OK and the step for 'Removing other columns' is added to the Applied Steps on the right hand side, I can go back and amend my choice much easier than I could have if I simply removed the columns.
Use the cog icon to the right of the step to bring the 'Choose Columns' window back up and amend your choices. If you had simply removed the columns there would be no cog to make any changes and if you would have to delete the whole step, or steps, and start again.
3. Extracting Column Values
Whenever you are modelling data, its possible that there is a column, or columns, that have data in that you want to be separate.
For example, in my dataset there is a column that is made up of a product type and a model number in a column called 'Model'. But in reality all I want to see is the product type.
To separate this out it is necessary to remove the references to the model number, which is after a hyphen in the column. To get started I would recommend duplicating the original column so that there are two columns showing the same values, this way you always have the original column should you need it again in the future. To duplicate the column select the desired column, right click and select 'Duplicate Column'. A copy of this column will be added to the end of the table and a step added to the list.
Once you have the duplicated column you can split the column into two. Select the duplicated column and press 'Split Column' from the home ribbon. This will present you with a series of options which can be used depending on how you want to split the data. As my column is using a hyphen to separate the values I am going to use the by delimiter option, but depending on your data and how you want to split the column there are other options.
After setting the delimiter option to a hyphen and letting the editor process the change, the column is split into two and can be processed further.
From here you may want to remove any unwanted columns or rename the columns to reflect their contents. In my example, I have removed the original 'Model' column and the second column from the split and kept only the first part of the original column, which I have renamed 'Type'.
Don't forget, if at any point during the modelling process you need to go back and change a step or remove a step, it can be done using the 'Applied Steps' pane on the right.
Once you are happy with the data in your table, press 'Close and Apply' from the home ribbon to load this into Power BI. This will close the Power Query Editor and apply the changes to your data model.
Calculations & Measures
When you load your data into Power BI, the system will automatically create some measures for you. These calculations are based on the format of the data and whether they can be summed together. This is designed to get you started quickly, but more often than not there are additional calculations you want to apply.
Power BI uses a language called DAX (Data Analysis Expressions), which is different from the standard Excel function language, and has a lot more power and functionality. If you have used Power Pivot in Excel previously the function language is the same. Microsoft also provide a comprehensive reference guide to the functions which is useful.
In this article I am not going to look at DAX and will use only the automatically created measures in order to keep this simple for those that haven't seen or used DAX function previously.
Visualising your data
To add a new visualisation to your report you can simply press on the icon for it in the 'Visualizations' pane. This will add a placeholder to the report canvas for you to add your contents.
Once you have the placeholder on the report canvas you can add data elements to the visualisation in the 'Visualization' pane. The visual will update as you add them. The pane will show the contents of any visual that is selected on the report canvas.
You can resize the visual using the darker sections on the visual border and change any formatting you want using the paint roller icon within the 'Visualizations' pane. You can also change the visual type by selecting another type from the top of the 'Visualization' pane.
To add further visuals you can repeat this process and arrange the report as you need to. You can also add text boxes or images to improve the overall appearance of the report.
For my example, I have added a line chart with 'Month' in the 'Axis' box and 'Sales Revenue' in the 'Values' box. This is followed by a donut chart of 'Type' in the 'Legend' box and 'Sales Revenue' in the 'Values' box.
At the moment the charts are showing a sum of all time periods that were added from the original excel spreadsheet. This means that the charts are currently summing all months together, regardless of year. This may or may not be an issue, but to avoid this problem a filter must be added for time.
To add a filter, drag a data element into the filter pane, or add it to a report as a slicer. I have opted to keep the filter separate to the report canvas and added it to the filter, but both are acceptable. I have also changed the 'Filter Type' to advanced to just select dates that are in 2019.
Publishing your report to the Power BI Service.
Once you have finished updating your report in the desktop, you can upload it to the Power BI service where you can then access it from any device via the Power BI Mobile apps.
What is the Power BI Service?
The Power BI Service is the cloud based portal for Power BI, accessible via app.powerbi.com, where you can securely access your Power BI reports and data. From the service you can still edit your report or share it with others.
To publish your report simply press 'Publish' from the Home ribbon. You will be asked to save your report and to which workspace you would like to upload it to. If this is your first time uploading you will only have 'My workspace' available.
Once complete you can open the report from the success box and view it in the cloud. You can also then access it from any mobile device via the Power BI mobile application.
And finally...
There is plenty more that can be done to a report than mentioned here, but hopefully this gives some basic foundations for getting data into Power BI and starting to visualise it.
If you would like to access the data behind this report and follow the example for yourself please download the data file below.