Understanding the Desktop application

1. Our First Project

2. Column from Examples

3. What is Power BI Desktop?

4. Group By, Big Data and DAX Studio

5. Preview Features and Weekday Sorting

The market offers a broad range of Business Intelligence (BI) tools with different features, pricing models and application areas. In case you decided to work with one of these BI tools, you might soon hit the point where you start asking yourself: Which tool should I choose?

This article takes a closer look at Power BI, the BI tool of Microsoft.

I will give you an overview of Power BI in general, the different tools it includes and how these tools are connected. Additionally, we will take a look at selected advanced features Power BI offers and compare the different versions - Free, Pro and Premium - with each other.

Starting with the most important concept it is crucial to understand that Power BI is not one single tool but a suite of connected business analysis tools. The core tools of Power BI are:

• Power BI Desktop

• Power BI Service

• Power BI Mobile

The goal of Power BI is to provide tools that allow you to work on your data, create visualizations and share your results with other people. Before we dive deeper into these single tools, let’s first understand the basic concept and by that the general idea behind Power BI.

Assuming to have multiple source files and wanting to create an own dataset based on these files, Power BI Desktop is the starting point. In this local application, which you can simply download right here, you can connect Power BI to a broad range of different types of source files. The only prerequisite is Windows as operating system. After you finished the cleaning and modelling of this data and by that also finishing the work on the actual dataset, you can also create visualizations, so mainly charts or tables to visualize the information in your data model. In Power BI Desktop, these visualizations are created on different pages in a so-called report. Up to this point we worked locally on our computer, so in case your goal now is to also share the dataset and the report with other people or even more to collaborate in your project with your colleagues, you need the second application – Power BI Service. This is a cloud solution and by that not limited to Windows only. Just note that a work or school E-Mail domain is required to be able to use the cloud service, so a private E-Mail address from Googlemail for example won’t suffice to create an account. In addition to the sharing and collaboration features offered by this tool, you can also create dashboards, a “cover page” which gives the user an overview of the most important visualizations of your report, so of the key facts basically. Depending on the amount of sharing features required, the capabilities of Power BI Free might not be sufficient, so we will also take a look at the differences between Power BI Free and Pro and additionally the new Premium version later in this article. After finishing the work in the cloud, another tool comes into play: Power BI Mobile. As the name already indicates, this is a mobile app which allows you and other people to access projects, so dashboards and reports created in Power BI Desktop and published to Power BI Service, or projects initially created in Power BI Service, anywhere from a mobile device. This is just a quick summary of the general idea behind the core tools of Power BI. With that basic understanding in mind, we are now ready to take a closer look at the individual tools and to see what specific features these may offer.

Before creating beautiful Visualizations and thinking about ways to publish our data to Power BI Service, we need to build the base first. In the world of data analysis this is of course the creation of a dataset. In Power BI, the Desktop application is the tool to use when it comes to connecting Power BI to different data sources and to create individual datasets based on the information in these source files. In addition to that, the application can also be connected to predefined datasets.

But it can do more: As soon as your data is ready and loaded into the data model where you can work on your data in two views, the data view and the relationship view (we will take a look at these different views in the next sections of this article) you can finally create visualizations which are built and displayed in another view - the report view.

This sounds like a lot of features and thus might be confusing for somebody who never used this tool before. For a better understanding of these functions, taking a closer look at the workflow of Power BI Desktop might be a big help to bring some structure into it.

Data Preparation

The first step in Power BI Desktop is performed in a separate tool which is directly integrated into the Desktop application – The Query Editor. As the name says, with this tool Power BI can be connected to a broad range of data sources using queries. Queries are not limited to a single source type, so it is possible to combine multiple data from different sources, for example from a .csv file and a SQL-Database for example.

The connection to the source files is the first step, but typically these sources never include equally structured or formatted data that would allow you to immediately work with this information. For this purpose the Query Editor offers multiple functions to adjust the queries the way you need them to be. From a formula perspective, everything happens in the **M-Language **.

No matter if you ever heard of this language before or not, the overall interface of the Query Editor is designed in a way that also guides unexperienced users smoothly through the multiple functions available and by that allows people to gain first results within a small amount of time. The functionality covers a broad range of data cleaning and shaping options and includes various editing functions for rows and columns, the combination of queries or of two or more columns and a lot more.

For a business user, the Query Editor is a real powerful tool that even allows you to create your own data model including a specific data model schema. Additional load options further help to specify what information should be loaded into the data model after finishing the data preparation procedures and what part of the information is supposed to remain in the Query Editor to keep the model well structured and to increase overall performance.

Data Modelling

The actual **data model is different in many ways **when comparing it to the Query Editor. Starting with the actual purpose which is now the work on the real data model over the interface which now consists of 3 different views (the report view, the data view and the relationship view) up to the formula language. In contrast to the Query Editor, the data model uses the DAX language, an expression language that might remind you of Excel in certain aspects. But before we start taking a closer look at how we can apply formulas to our data model, the primary focus should be on another topic first.

Relationships

The quality of a data model is highly dependent on a well-thought structure. This structure can include multiple tables, which might be required to “communicate” with each other. In the world of data, this communication can be generated via relationships. In Power BI you can create all relevant types of relationships, so One-to-One, One-to-Many and Many-to-Many in the relationship view.

The general concept simply requires two tables with each table containing one column with identical information – a foreign key and a primary key. You can easily imagine the importance of such a relationship by thinking about it in one basic example: We have two tables, one table named CUSTOMER with a list of customers, including one column with the customer ID - a unique value for each customer - and other columns with information like first and second name, address and so on.

And a second table named ORDERS with orders in an online store for example. This second table also has one column with the customer ID to allocate the order to a specific customer and other columns which include the order ID and the products in the corresponding orders for example. Additional information about the customer is not included in that table.

To allocate an order to a specific customer with the required information like name, address, etc. the ID columns of both tables must be connected. As soon as an order is placed, this order becomes part of the ORDER table and is allocated in this table to a customer ID. But additional information about this customer is required, and this information is stored in the CUSTOMER table. In this case, the creation of a many-to-one relationship (one customer can have multiple orders, but exists only once with a unique ID in the CUSTOMER table) between the ID columns of the two tables establishes this communication path and by that increases the capabilities of our model. This is one application area of relationships, which can be easily created in Power BI Desktop.

Calculated Columns

A well prepared data model of course does not solely rely on different tables which are connected via relationships to each other. The analysis of complex data often requires tailored calculations and formulas to be added to the different tables to extract important information out of the data. For this purpose, Power BI Desktop offers two different functions: Calculated Columns and Measures, both being based on the DAX language.

Let’s understand Calculated Columns first. Referring to the name, a Calculated Column creates a formula that is applied to all rows of a specific column. An easy to understand example could be an IF formula. Assuming a column containing different sales for various periods in its different rows, an IF condition could specify that IF sales are higher than 100, the text “On target” should be displayed in a new column in all corresponding rows where this is true. In case sales are equal or below 100, the rows in the newly created column should be named “Below target”.

Based on this formula, a new (calculated) column would be created. Of course there are multiple formulas to create Calculated Columns available in Power BI Desktop, the important thing to note is that Calculated Columns always refer to each row of a specific column and thus are always calculated – and by that consume additional memory.

Measures

In contrast to Calculated Columns, we can also create formulas that only result in one single or an aggregated value and therefore are not calculated for each single row of a specific column. These formulas are called Measures in Power BI Desktop. An example for a measure would be the average of all values in one column in a table. As this formula only calculates one single number as a result, it is not calculated and displayed for each single row of a column and further only executed when used in a visualization, so in a specific chart for example.

The combination of Calculated Columns and Measures allows us to create a data model exactly specified to our needs. It you want to get a general overview of the syntax and the number of DAX functions available, make sure to have a look at the official documentation official documentation which gives you a great idea of the various DAX function references available.

Reports

After finishing our data-related work in the relationship view and in the data view, it’s time to bring some live into our data using the third and last view: The report view. In this view, you can create reports in Power BI. A report simply comprises all the visualizations created in your project. Such a report can consist of a single single or of multiple pages, with each page having the same predefined space where you can place your visualizations just as you prefer.

Power BI Desktop offers multiple pre-defined chart types including column and bar charts, line charts, waterfalls, treemaps, tables, cards and even world maps which allow you to present your data in an interactive map.

To bring the data to live, you can simply drag-and-drop the corresponding values, so basically the columns of your data model, into the specific fields of a visualization. Additionally, previously created Measures can also be dragged into a chart to present an aggregated result of a specific calculation in that visualization.

Although the visualizations are pre-defined, they include multiple formatting options, which allow the user to customize the way the charts and tables should be displayed. Additionally, different filter types can be applied, for example to limit the number of years being displayed in a single visualization, on one single page of the report or within the entire report.

Finally, all charts have a click-in feature which also affects the data displayed in other visualizations depending on the settings made for these interactions. Depending on that settings, selecting one specific year in one visualization, would also change the information displayed on other visualizations, which then also only display the values for this year.

With the report, the local work in Power BI Desktop is finished and thus we could stop right here. But as Power BI consist of multiple tools, now it’s time to understand why we might need additional tools at this stage. The following illustration presents an overview of additional ways how to go on right now, assuming that keeping our data local is not our goal:

As can be obtained, in all cases where we decide to share our data, we need Power BI Service, the cloud application which we will take a closer look at right now.

Load & play YouTube video

The general idea behind Power BI Service is to share your projects, to collaborate in them and to add one new element to your project: Dashboards. Before we take a look at these single features, it is important to understand that Power BI Service is not limited to Windows only. The only thing required to create an account is a work or school E-Mail address.

After creating the Power BI account you are ready to start with Power BI Free and start the work in your workspace, the area in Power BI Service that is linked to your account and where all information is stored. As the name indicates, the Free version of Power BI does not cost you a single cent. In addition to that, two additional Power BI versions are available which are not for free. We’ll take a quick look at the differences between these three version later in this article.

As it is true for Power BI Desktop, Power BI Service can also be connected to different data sources. But there is one important difference: Power BI Service is not the tool to create and to structure new datasets, it’s the tool that you connect to existing datasets. These predefined datasets are either already implemented into Power BI Service (as so-called content packs) and already include the report and the dashboard or are created by us in Power BI Desktop as previously explained.

Let’s assume we want to import our locally created dataset and the corresponding report into Power BI Service. One way would be to use the publish function, which is available in Power BI Desktop and which simply exports the data, so the dataset and the report, to Power BI Service. Alternatively we could use a function named “Get Data” in Power BI Service which allows us to import data from Power BI Desktop. No matter what we decide to do – The result is the same, with both functions we are able to load our data into Power BI Service.

But if we change the information in Power BI Desktop afterwards, these changes won’t be applied to the data in Power BI Service, as the data is only imported but not dynamically connected to Power BI Desktop. This requires another feature to be implemented.

Gateways

A dynamic link and by that a so-called **scheduled refresh **can be established using gateways. In case the Power BI Desktop file is located on a personal computer, a personal gateway must be installed, for server based solutions, an on-premises gateway is required. No matter which gateway we select, both solutions create a dynamic connection between the source data and Power BI Service and by that can ensure that our data is always up to date, depending on the scheduled refresh settings defined.

Dashboards

We ensured that our data is always up-to-date, now one further element can be added to our projects: Dashboards. These are only available in Power BI Service and represent one single page including all relevant visualizations of one single or of multiple reports. These visualizations are all based on the reports that you loaded into or created in Power BI Service in your workspace. By clicking into a visualization in the dashboard, the user is redirected to the underlying report and therefore can see the additional information and the underlying dataset.

App Workspace

Each Power BI account has its own workspace, but if our goal is to collaborate with colleagues in our project, then we can create an app workspace. This workspace has generally the same functionalities as the user’s home workspace, but additionally allows all people that have the corresponding rights, which can of course be specified in this app workspace, to simultaneously work on the projects that are loaded into this app workspace. It is possible to create multiple app workspaces for the same or for different groups, which can be a big help in terms of project organization.

In addition to that collaboration, app workspaces offer one additional feature after the actual work in the project is completed.

Apps

The content created in an app workspace can be shared with other people that are not part of that specific workspace by creating and publishing an app. This app, simply contains all information of the app workspace, so all datasets, reports and dashboards. When publishing the app, it can be specified which people in your organization are allowed to view the information in that app. And that’s the important difference: Users of an app workspace can edit the content (depending on the settings of course) but people who just have access to the app, can only view the information in it but are not allowed to change the included data, reports or dashboards.

With Power BI Service, our work, so the creation of dashboards and reports is generally finished. We know that we can share these results using apps directly in Power BI Service with other Power BI Service users in our organization. But this only refers to people who are able to log-in to Power BI Service from their computer. But sometimes it might be required to be able to access data from mobile devices. For this purpose, Power BI Mobile was created, an app that can be downloaded for free for Apple, Android and Windows devices in the corresponding app stores.

After downloading and connecting the app to your Power BI account, you can access your workspaces and your shared apps in it. The data that can be accessed include all dashboards and reports, which can also be shared with other people in the app, but does not include the data in the initial dataset.

Nevertheless, with the mobile apps, the core tools of Power BI are now complete and allow us to create datasets, analyze and visualize data and finally share and access the information from anywhere with any device. In addition to these core tools, Power BI offers some advanced tools to tailor the features depending on user specific needs.

For developers or generally people with an increased focus on customization and some programming knowledge, Power BI Developer is an additional tool of Power BI universe. Two features to be named are the option to create customized visuals and to embed your Power BI projects into your own apps using Power BI Embedded.

For the customized visualizations, TypeScript, a programming language, is required so this might not be the core function to use if you work with Power BI Desktop for the first time. This is probably also true for Power BI Embedded, as this requires your own app to display the information created in Power BI, which again might not be available for the normal business user.

But the pure existence of such features, gives a first impression of the broad range of user interest Power BI tries to cover and additionally allows users to further tailor their Power BI experience if they want to.

If you want to try out Power BI on your own, you can perfectly do that using the Free version which generally comes with all relevant features. As soon as you plan to put your emphasis on dynamic data updates using gateways with scheduled refresh times or if you want to create app workspaces, then Power BI Pro is required as those features are only available to full extent in this Pro version. An overview of the pricing can be found right here. In case you want to try the features Power BI Pro ships with, a 60-day trial version with full functionality is available. Just follow the instruction on this page to activate the trial version.

In addition to Power BI Free and Pro, Power BI Premium was launched in 2017. The target group are companies with the goal to implement large scale BI applications, making this version not the relevant one if you just start using Power BI. However, in case you plan to integrate Power BI in such a large scale environment, this version might me the one to consider in case Power BI Pro hits reaches its limits.

Being available to the public since July 2015, Power BI still receives constant updates. Microsoft provides detailed information about the latest updates and changes in this blog. Power BI Desktop also includes the option to activate different Preview features which are not final yet but can be tried out already in the application. So in case you currently miss certain functionalities in Power BI, chances are given that these features might be added in the near future.

The honest answer is: It depends. As said in the beginning of this article, the market offers a huge amount of different BI tools so in the end it’s up to you to decide which tool is the right one. My personal point of view is that Power BI is still a young tool, but already allows you to do great things with your data. The different tools might be deterrent when first using Power BI, but as soon as you understand the general workflow, the majority of the functions is relatively self-explaining. This does of course not include the advanced topics: Becoming a real expert in DAX or in the features offered by Power BI Embedded, increases the complexity and amount of work required. But these features are not necessarily the ones used on expert level for business users and thus do not change my overall judgment of Power BI being a great Business Intelligence tool definitely worth taking a closer look at.

I hope this article helped you to get a more detailed view into the Power BI world, into the different tools, their features and the way they interact.