In this guide I will briefly describe the basics principle of the Power BI tool. The guide is aimed for Data analysts who want to start working with power BI. Before explaining how to work with the tool, I will explain why in my opinion an analyst needs a BI tool and how to choose the right tool for the job.
Using the Analyst Data in the BI Tool In the BI
Data analysts have two main uses BI tools: Analyse data for insights and create KPI reports. In this guide I will focus mainly on Analyse data using BI tools.
Why do you need a BI tool to analyze data ?
Data are numbers and in order to understand its meanings the analyst needs see them in a larger context of other numbers. For example, we will not be able to know whether the number 61 is considered a lot or a little unless we use it with a context of other numbers.
Cognitively it is easier for people to estimate the ratio of two numbers when they are displayed on a charts than when they are displayed in a table (as long as it is not a pie graph which is better to keep it for dessert). Therefore, in my opinion, BI tool are important for data analysis.
Until a few years ago, the common way for an analyst to create graphs was through the output of statistical software or using a datasheet such as Excel. In recent years, the options of the Self Service BI emerged and allowed analysts to create charts and interactive analyzes very easily without the help of the IT department.
Why did I choose to focus on Power BI?
There are quite a few BI tools on the market today, so it is difficult to choose which tool is most suitable for performing analyzes. In my opinion when choosing a BI tool the analyst should pay attention to two main parameters: analytical capabilities and ease of use.
Fortunately, most tools offer most of the analytical capabilities that the analyst needs, but unfortunately not all tools are easy to use and some tools require a significant improvement in the user experience (UX\UI). In order for a tool to be easy to use, it needs to be intuitive, work smoothly and fluently, and make the life of the analyst as easy as possible while creating the analysis.
If an analyst does not want to fight with the BI tool all day the analyst should choose a tool whose user experience is fun and pleasant so she can focus on her brilliant analysis and not handling the BI tool.
I am writing this article in 2021 and for now, from all the tools I have worked with, I found that Power BI is the most convenient tool and that is why I decided to write the guide about this tool.
Remarks:
- For the purpose of the discussion I have neglected the issue of pricing of tools, but it is certainly important when a company has to choose its tools.
- I am writing this article in 2021. It is possible that over time other tools will improve their user experience and will be better than Power BI in these aspects. I sincerely hope that all BI tool will be analytically good and easy to use.
How to install Power BI
On the Power BI website you can download the free version of power bi desktop. As of 2021, the free version allows analysis of a huge amount of data and allows you to connect to many databases and information sources. I hope Microsoft will remain this version free.
Creating Analyzes with Power BI
This guide will be brief and will explain the basics of the Power BI that will allow you to get started with the tool quickly. To create more complex analyzes you will have to learn more about the tool. There are great videos on YouTube that you can learn a lot from.
Input data
In the top menu (Ribon) there is a button called Get Data which is used to import tables for the analysis.
Import vs DirectQuery
Import and DirectQuery are methodes to access data from DataBase.
With the “Import” method, the data tables are captured into the local PowerBI memory and later when the analyst saves it’s work, the data is stored in the local hard drive. However, in the DirectQuery method the data is not captured locally, it remains in the database and all of the calculations are done at the database engine.
If you have a small amount of data, using the “Import” method is a better choice because The working speed is higher since the calculations are made in the memory of the local machine (computer) on which we run the tool. The disadvantage of the “Import” method appear when you have lots of data that cannot be handled on the local machine memory. In that case you should use the DirectQuery method.
The big advantage of using the DirectQuery method is it’s ability to handlr huge amounts of data. In this method the database performs the calculations and not the tool so there is no limit of the local machine memory. Another advantage of this method is that the Power BI work file is very small and does not need much storage space.
Power BI worksheets
Power BI works with worksheets like in Excel. Worksheets are like a canvas on which an analyst can drag and drop visualizations (which are the graphs and tables that appear in the menu on the right) or graphical elements like tech boxes,buttons and shapes. A single Power BI report can have many sheets and they can be divided into topics.
A sheet design in Power BI is like designing a slide in PowerPoint and hence the ease of use of the tool. To work with an element all you have to do is drag it to the canvas and configure it.
Like in PowerPoint it is possible to duplicate elements with copy and paste commands. This way the analyst won’t have to create similar elements from scratch. It is also possible to copy the design using the famous brush of Microsoft office products – “Format painter”.
Apart from the graphs, another important Visualisation is the Slicer visualization. It looks like a table with a small funnel. This visualization is important because it allows the report user to filter all the data that appears in the sheet’s visualizations.
There is an option to set Slicer to filter only specific visualizations instead of the while sheet, this option is called “Edit interaction” but I won’t explain about it in this guide.
Visualization design
When you choose the visualization in the worksheet with the mouse, three icons appear on the right side of the Visualizations menu that allow you to customise the visualization:
- Fields selection (table-like icon) – At this area the analyst can define the fields that the visualization will display. Drag the visualization fields from the Fields menu to the right of the Visualization menu.
- Visualization design (graph and brush icon) – This area controls the appearance and colors of the visualization.
- Advanced analyzes (icon of magnifying glass with graph inside) – There are some visualizations that allow you to add additional analytical capabilities. These options appear under Advanced Analysis.
Note – Microsoft occasionally updates the product and the icons may change over time.
The filters menu
Between the canvas of the sheet and the visualizations menu, there is the “Filters” menu. This menu allows you to set specific filters for: the visualization, the whole sheet or the entire report. The difference between this menu and the Slicer is that only the report planner can access this menu and not the end users of the report. In addition, Slicer works on sheet level and the filters menu can work on one visualization, sheet level or the entire report.
New Columns and Measures
To create new columns or measures go the “Modeling” sub menu on the top ribbon.
New Columns
Sometimes we want to create new fields for the analysis (for example, create a variable that has only the year of a date field). To create a new field press the “New Column” button and code line will be displayed. In the code line the analyst can write a new formula for the new field. The formula’s functions are called Dax and they are very similar to the functions in Excel.
Any new field we create will appear in the Fields menu.
Measures in Power BI The
In the “Modeling” menu there is also an option to create Measures.
Measures are not columns, they are declarations of aggregate functions that can be added to a report. For example, the analyst can declare the measure called “Clients” which counts the number of clients (like Count or Count distinct in SQL language). Measures also works with DAX functions.
After creating the Measure, it will also appear at the fields menu.
It is highly recommended to create the important Measures at the beginning of the analysis and apply them to the visualisations. This way all visualization wiill be synchronise with the same Measures.
The Difference Between a Field and Measures
Measures are not new fields. Measures are definitions of they are declarations of aggregate functions on the data. We can not see or display measures as in normal fields, and they can only be seen within the visualizations. For example, if we have created a measure that counts the amount of customers (which is actually an aggregate function) we can only see it when we apply it to the graphs or within other visualization that shows the results of the aggregate data (e.g. card visualization).
The difference between Field and Measures is one of the most confusing issues when starting to work with Power BI (and with BI tools in general) so in order to know the right choice between the two options, the analyst must pay attention to whether working with aggregate data or with granular data (open).
Summary
In this article I described why data Analysts should work with BI tools and the basics of Power BI. For more articles on Power BI you can also read my article on the pros and cons of Power BI.
This article was written by Yuval Marnin.
If you have a need to hire a freelancer data analyst you may contact me at: [email protected]
You may also hire me through upwork platform on that link:
https://www.upwork.com/freelancers/~018940225ce48244f0\
Further reading
The advantage of hiring a freelance data analyst.
What does a data analyst is doing and how it can help your company.
