Power BI Desktop is a Business Intelligence analytic tool, belonging to the range of self-analytics tools available in the market, like Tableau or Qlik Sense. With a straight forward install process, you can easily start building eye catching dashboards, which can give you good data insights without the need to be connected to the internet, or even additional software stacks.
Today we will take a look at Power BI Desktop, a set of business intelligence tools to analyze the data. To learn well we will use hands on approach and create an example of a report to showcase functionality in a brief manner. In this Power BI tutorial, you will be building your first dashboard with Power BI Desktop, covering some of the most important tasks, like importing data, transforming and modelizing it and visualize data as a dashboard.
This Power BI tutorial is dedicated to all of you interested to start getting a foot inside the world of analytics and business intelligence tools, which enable anyone to create a new way to understand data. Discovering a new sense to bundles of figures will return interesting insights about your business, customers, visitors, events, etc.
Whether you are a researcher, a developer, a student or simply curious about new tools, this Power BI tutorial is perfect to get familiar with one of the most popular standalone BI tools available in the market.
In order to accomplish the objective of the Power BI tutorial you will need:
- Power BI Desktop
- If you have some knowledge of Excel, will be easier to understand how tables/queries work.
Power BI Desktop Installation and configuration
Download Power BI Desktop from the official website. After that, run downloaded file and follow the installation steps. The installation processes are accompanied with thorough instructions and thus is self-explanatory and straightforward.
To access all features of the program, you should register. Firstly, you need to create an account on the website. If you already have an account you can skip this section and go on to the rest of the tutorial. Once you click “Download” dialog box will appear. Fill in all required fields and click “Get started”. Important note: you can not use email accounts of public email service providers, such as Gmail or Yahoo, you should use corporate or school email.
After that, an email with the confirmation link will arrive in your mailbox. Click on it and go to the site as in the figure below suggests.
Enter the same email, fill in all the fields and click “Get started”. Through with this, the account is setup and ready – you may proceed to sign in to Power BI desktop app.
After both installation and registration processes are finished, we can proceed to work with main functionality.
Work with your data
As mentioned before, Power BI Desktop is a tool for working with data. Everything in it begins with data. Thus, in order to do something we primarily need to connect to some source of data. In our case it will be excel file, but the options are not restricted to it – the data may be in form of CSV, JSON, XML, plain text, folder of files, relational databases of any kind, and many other cloud solutions, as well as service that may provide or generate data (anything from Facebook to Github).
To import data from excel file find and click “Get Data” button at the top of the menu bar, then select “Excel” and click “Connect”. In the popup window navigate to the location of your file on your computer, select it and click open. When a file is selected and opened, select the first sheet – “1 digit”, then click “Load”. After that, the right pane will appear, it will contain all the column names from the file.
Dragging them to a worksheet or turning on checkboxes, we can display its data. You can also add more than one sheet or add sheets from other files.
Before we can proceed with visualizing and building report of our data, we need to prepare a little bit.
First of all, we want to add some computed metrics. We can begin computing new fields outright since we are sure our data does not have any missing values. Otherwise, we would have to deal with that first.
To do any manipulations with data, we go to “Edit Query” on the menu ribbon.
We can do all sorts of data manipulations in this window. First, remember to check types of each column (specified by the column title) as it is important for computation. We have all types matched correctly from the load. Then, we go to “Add Column” tab of the menu and click “Custom Column” button.
Now, in this window, we can type formula for computing the column. We will set the title of the column to Change and compute how some jobs changed from 2011 to 2014. For this, we will simply subtract the latter from the former. To build formula double click on the field and then add math signs or function calls. When the formula is ready click ok.
Now the column is appended at the end.
Next, we will add one more column, with the percentage of change from 2011. The percentage will be normalized, i.e. 1 is 100%.
To add another column, we again click on “Custom Column”, then add formula to compute it:
As we added computed metrics, the data became more complete, and we opened new possibilities for visualizations. However, our data is still not normalized and have some problems. In particular, those two columns corresponding to the jobs in 2011 and 2014 is not a good practice because the 2011 or 2014 are not really attributes. Instead, we want to have two other columns – Year, which will contain either 2011 or 2014 and Jobs, corresponding to the numbers in each of our current column.
Luckily, Power BI provides functionality for such transformations and reshaping of the data out of the box – Unpivot Columns. It basically does what we have just described above. To unpivot columns, we first rename columns “Jobs 2011” and “Jobs 2014” to “2011” and “2014” (because that is what will be in the values of columns), then we simply highlight columns we want to unpivot and click “un-pivot columns” in “Transform” tab.
Then, all that is left to do is to rename new column names from “attribute” and “value” to “Year” and “Jobs” respectively.
Now, just need to change type on generated columns and we are done.
Finally, we save the query, go to file tab of the menu, and click close and apply. Data transformation is complete – we can proceed to visualize.
Data Visualization with Power BI Desktop
Building the Dashboard
BI Dashboards, are the most known feature of a BI analytic tools for the general public. We will now cover how to handle some of the visual tools available for data visualization in Power BI Desktop.
Let’s first build a usual line graph. To do this, click or drag line graph type from visualizations to the report area and check mark values to visualize, we chose “% Change” and “SIC Code”. You may drag and resize the visual as you wish.
If you want to change the style, tweak colors, and customize formats of your visual, Power BI provides such opportunity. You can change the background color, line color, and pretty much anything else about the graph. To do this, click the icon on the right panel and change the background color to that you like by clicking on the “Background” and then “Color”, after that change the color of the line by clicking the “Data colors”. The options are innumerable. The most important – you may set up the title for the graph here.
You may play with other options and discover them.
Now, let’s build a different type of chart – “Map”. Power BI Desktop includes two Geographical Visualization types out of the box – “filled map” and “map” (for display on raw map). We will use the latter.
To create map type graph, we will follow the same approach as we did with the previous graph. Click on the map to spawn the type. Then, we can either check mark fields to visualize, drag and drop them onto the visual on the page or drag and drop them into attributes of the visual to choose what field will be used for each attribute. We will use the latter method.
With map we brought spatial information to the report, now let’s add industry standard Bar Chart in the form of “Clustered Column Chart”. The workflow is still the same as for previous two:
Both negative and positive values are displayed here. For the sake of example, let’s change it and filter out negatives, showing only positive values (do not neglect negatives, however). We can do that with filters in the visualization settings area. We will set a filter in the change field. You can add filters of three levels there: Visual Level, Page Level, Report Level. Note that you may add fields to filters without adding them to display attributes of visualization.
Let’s complete the picture with whole to part view. The Pie Chart, even though is arguable does this job. Let’s add Pie Chart to our report:
Note: each type of visual exhibits its own set of style settings, so do not forget to peek into.
Publish and share your dashboard
The report is finished at this point and we are ready to publish it and share it with our colleagues.
First, you can save it on the computer by clicking and giving it concise name.
Then, clicking you can send it to your web Power BI service account in the default workspace, where you can compose dashboard and colleagues, connected to the same workspace (by default only one workspace available for free trial, to create more workspaces the purchase of full version is required), will be able to view the dashboard.
After you publish, the report is available online at Power BI service and add it to dashboards.
Today you’ve got acquainted with the Power BI, a powerful tool and comfortable environment for building reports out of any data. We learned how to create simple visuals and modify them to suit our needs and tastes. However we only scratch the surface of this whale of a tool and there is yet a load of functionality, so keep learning and diligent in your research.
You can also try now a more complex Power BI tutorial, on building a weather dashboard with Power BI Desktop and customize it. Feel free to share your results with us.