Introduction to Microsoft Power BI
Microsoft Power BI is a data visualization tool that allows its users to create visualizations, reports, and dashboards. It is one of, if not the most commonly used data visualization tool for business intelligence and possesses a large market share among Fortune 500 companies. In preparation for an upcoming job opportunity, I completed Datacamp’s “Introduction to Power BI” course to get more familiar with how it works.
Introduction:
Power BI is available for use in two ways: Desktop and Service. Desktop is the version that is accessible through your local computer. It is free to use and includes many features such as the Power Query Editor. Service is the cloud-based version of Power BI. It is used primarily for sharing and distributing reports, since it does not have as many features as the Desktop version. There is also Power BI Pro, which is a paid version that has certain capabilities, such as the ability to collaborate with other Power BI users and to publish work to the cloud platform. The version used in the course was Power BI Desktop.
Upon opening Power BI, you have three views on the interface for your datasets: Report, Data, and Model. On the Report view, you are able to create visualizations and reports. On the Data view, you see the dataset used for your reports. On the Model view, you are able to see the relationships between different tables for the data model.
The Report view is the default view when you start. The large white space is the Canvas where all of your visualizations are created. To the right of the canvas, we have multiple sections for Filters, Visualizations, and Fields. Filters allow you to filter the visualizations. Visualizations are where you specify what type of visualization to make, such as bar graphs or donut charts. Fields are the available fields that can be used for our visualizations. We can use columns from our tables or calculated fields from our columns. It is also possible to load pre-existing reports along with the data.
A key feature for Power BI’s is its interactivity for visualizations. For example, suppose we want to know our total profits for only 2016. By clicking on the 2016 bar on our chart, all other metrics and data on the canvas will change accordingly from this filter. We are able to create additional slicer filters, such as specific employees or locations, to further filter our data to our needs.
Power BI is able to connect data from a variety of sources, from CSV files to webpages to SQL server databases. We load in our data and we can view the dataset in the Data view. If we have tables that are related or share a key, we are able to connect them in the Model view.
There may be times in which the data we are trying to import requires additional attention. For example, we may not need every column from the dataset or there may be formatting errors in multiple rows. In order to clean our data, we will transform our data prior to loading using the Power Query Editor.
The Power Query Editor is what allows us to properly clean our data. We have a number of data cleaning actions available to us, depending on our situation. We can delete rows or columns, change data types for columns, and replace values. Anyone familiar with Excel can immediately recognize the ways we can change our data for formats like currency and date. If we have geographic data like cities or countries, we can specify it as such, allowing us to create map visualizations. In addition, there is a history of changes made to the data tracked in the Applied Steps pane, allowing us to revert to a prior step if we wish to. To finalize our changes, we select ‘Close & Apply’.
Visualizations:
Power BI provides a number of chart types and visualizations to fit your needs. These visualizations are interactive and allow for you to hover over for more details. As expected, you have “standard” chart types like bar charts, line charts, and pie charts available.
There are also visuals that are ideal for displaying metrics for performance. Cards and multi-row cards can be used to show one or multiple values at a time. Gauge and KPI charts are best for displaying performance relative to a certain threshold or goal. We also have the ability to use Tables and Matrices to show relevant data from our datasets.
When it comes to filtering or drilling down our data for visualizations, Power BI provides tools that allow us to do so easily. What is meant by “drilling down” is the ability to have a high level view of your data and the option to look at more specific aspects. These are defined as hierarchies.
Essentially, you are starting out at the top of a pyramid but you have the ability to go down or up levels as you please. Common hierarchies are aspects of dates, going from years to quarters to months, or locations, going from regions to countries to cities. We can even create our own hierarchies and rearrange the fields accordingly to match the hierarchical structure we need.
You can choose to drill down all fields on a visualization at once or specify one field at a time. Suppose we have a bar chart showing sales for 2018–2020. By drilling down all fields at once, we will end up with four bars for each quarter. These four bars represent the sum of total sales for each quarter, such as quarter one for 2018, 2019, and 2020 sales combined. If we were to select only 2019 and then drill down, we would have four bars for each quarter for 2019.
Power BI also give us the ability to filter our visualizations. If we wanted to show only top N, we can easily do that by applying a filter. This is accessible through the Filters pane next to our visualizations. Any filter we create can be applied to either all pages or one specific page.
Data Analysis Expressions (DAX):
Data Analysis Expressions or DAX are used to perform calculations and queries on our data. We pass in arguments into formulas in order to generate calculated columns, measures, or tables. For example, if we wanted to find out the sum of all values within a column, we call the SUM() function and pass in the column we wish to apply the function on. Anyone familiar with Microsoft Excel’s formulas will recognize the syntax used for DAX.
Suppose we wanted to generate a new column in our DimCustomer table to represent a temporary password. Our temporary password is going to be created using the last four digits of a customer’s phone number and the customer’s year of birth. To do this, we hit the New column button and write the appropriate DAX for this. We first state the name of our new column, TempPassword. Using the RIGHT() formula, we can extract the last digits from both the Phone and BirthDate columns.
We can create calculated measures for use in our visualizations. To do so, we hit the New measure button and enter the appropriate DAX formula for what we want. This measure will now appear in the Fields pane and we can now use it as part of a visualization. It can be useful to create measures using other measures to prevent redundancies. There is also a Quick Measures button that provides commonly used measures, like year-to-date total and average per category. Quick measures can be handy as they provide a way to do complex calculations without having to write any DAX.
One of the most frequently used DAX functions is the CALCULATE() function. It takes in an aggregation function as well as multiple filters. This is handy for instances for which we wish to calculate a measure for a particular segment of data, such as the total sales amount of products that are colored Blue.
Using CALCULATE() is easier to rewrite and reuse than it would be if we were using simple slicers and filters. CALCULATE() is also very useful when performing time-based calculations and time analysis. If we wanted to compare our sales number for this with the numbers from last year, we can do so by using as SAMEPERIODLASTYEAR as a filter.
By the end of the course, I came away feeling confident in how Power BI works and some of its key features and capabilities. DAX, in particular, is a powerful feature that allows its users to perform whatever calculations they require. Power BI comes across as a tool that is accessible and powerful, both important aspects when developing business intelligence tools. I completely understand now why it is so prevalent in the business world. Hopefully, I will have the opportunity to use Power BI and learn even more in the future. Thank you for reading!
