Power BI Desktop; Getting Started – Part 1

There are some reasons that I chose Power BI Desktop for the very first demo of this book. Power BI Desktop has three components all in one (Power Query, Power Pivot, and Power View), it can upload directly to Power BI website, and it is a recently released product with many shining features. In this section I want to get you to start working with Power BI Desktop, you will get familiar with Power BI Desktop editor, and you'll see main panes of that. This would be the first experience with Power BI Desktop. If you want to know What is Power BI read the first section of the book here.

What is Power BI Desktop?

Power BI Desktop is the development editor released 24th of July 2015 for Power BI. Power BI Desktop can connect to many data sources, transform the data, load it into a data model with relationship design, and finally visualize it in a report style. Power BI Desktop apply all of these actions on a *.pbix file, and it can publish the file directly to the Power BI website where users can see the report and interact with it from a web browser.

Power BI Desktop as mentioned above is an editor for three components; Power Query, Power Pivot, and Power View. Power Query connects to data sources and mashes up the data with a formula language, the result set of Power Query will be loaded into a tabular model which is Power Pivot. Power Pivot can set the relationship and allow you to create measures and calculated columns and set the data model as you want. Then Power View connects to the model and visualizes the data with different charts and visualization elements.

Power BI Desktop has everything in one editor, and this makes it an easy to use the tool, but don't be a fool of this easy interface. Power BI Desktop uses three powerful components, and two of those components give you extensive features (I mean Power Query and Power Pivot). You can solve very complex challenges with Power BI Desktop only because of its underlying components. To be a professional and expert in Power BI, you have to be an expert in Power Query (M language), and Power Pivot (DAX expressions). I'll cover deep, detailed discussion about these products in future chapters, but for now, let's look at Power BI Desktop with a very easy example.

Prerequisites

Power BI Desktop:

Download Power BI Desktop from this link.

Sample Database:

For this example, I used the old Pubs database (to create something different than AdventureWorks samples). You can download the SQL Server database from here and install the Pubs database.  However, this example is done with the Excel version of Pubs to make it more convenient even for those of you who don't have SQL Server installed to be able to work with it. You can also download the Excel version of the Pubs data from here:

Power BI Account:

If you don't have a Power BI account, simply go to the Power BI website, and enter your company mail address to get a free account. Note that you cannot use public email accounts such as Gmail and yahoo here

Startup Screen

Power BI Desktop like many other Microsoft editor applications has a startup screen. In the startup screen you have some options to go through as below:

1. you can watch some Power BI Desktop tutorial videos.
2. You can also read the latest news about Power BI or some tutorials of Power BI, or you can go to Power BI forums to ask a question or search for some answers there.
3. You can open an existing report with Power BI, or start building a new report by getting data. You can see a list of the latest files that you've worked on as well.

Get Data

Let's start the work with getting data. Pubs is a database of some books published and sold in stores, information about titles, authors, stores, etc are available in this database. I want to keep this example easy so I create a very simple model. For every experience with Power BI Desktop, you have to first get data from somewhere. Wide range of data sources is supported from files to the database, web search, and many other data sources. Once you've connected to the data, you can transform it (re-shape it) as you want. So in this part of the example, you get data from the Pubs database, and you'll apply a very simple transformation to it.

Open Power BI Desktop. In the startup screen choose Get Data.

Get Data window will open. This window is the graphical interface to connect to many data sources. from the list of data sources select Excel (either from the All tab or under File tab)

Then select Pubs.xlsx file (you have to download it from the link at the beginning of this post), and you will see a Navigator window as below

Navigator window is a place that you can see the preview of the data from all data entities in the data source. The Excel file that we are connecting to it has five sheets, that is why we have five names (Authors, Sales, Stores, TitleAuthor, and Titles). With choosing any of these names (or let's say tables), you will see a preview of data for that table in the right-hand side. As it showed in the screenshot above.

For this example, select all five table, and then click on Load.

When you load data into Power BI, Power BI Desktop loads it into the memory of the machine, and then try to find some relationships between tables.

After successfully loading the data into the model, you'll see a list of tables in the Fields panel of Power BI Desktop (right-hand side in the Power BI Desktop);

Visualization

Learning Power BI Desktop is great especially by starting from Visualization. Let's start creating our first chart. From the list of fields. Drag and drop Qty from Sales table into a blank area in the Power BI Desktop (not in Filter pane, just in the main big blank canvas which is your report canvas);

This action will create a visual. The default visual is a Clustered Column Chart, and in this case, it will have only one column: the total quantity of books sold. This is a small dataset, and the total quantity is only 493.

Now let's slice and dice this data by the title of books. The title is a field in the Titles table, after expanding the Titles table, drag and drop the Title field onto the same visual.

Now you can see that the chart is showing Title of books in the X-Axis, and their quantity in the Y-Axis

How does this work?

You may ask yourself that; how this works? The title is from the Titles table, and Qty is from the Sales table, how we can slice and dice data across multiple tables?

The secret is in the Relationships between tables. Power BI by default detects the relationship between tables. You can see the relationships in the Relationship tab in Power BI Desktop (in the left-hand side, the third tab);

You can also fit the relationship diagram into the screen by the option showed in the screenshot above. As you can see, there are relationships between all tables. That is because Power BI detected those relationships automatically. Power BI detects the relationships based on the field name matching between two tables, and a few other criteria. You can, of course, edit a relationship or delete it if you want. For this example, however, relationships are all fine and we don't need to change it.

A relationship between two tables act as a lookup table scenario in Excel; it helps to slice and dice data between those tables.

Let's now go back to the report tab to do some more visualization. To go back to the report tab, in the left-hand side tabs, select the first one which is for Report.

Formatting visuals

In this step, let's customize some formatting options of the visual that we have created in the previous step. To format a visual, you have to select it first. When a visual is selected, you will see a border around it like the screenshot below;

Select the visual (you will see the border around it, as shown in the above screenshot), then go to the Format tab under the list of visuals.

Data Color

Format tab is the place that you can change everything regarding the look and feel of your chart. For example, to change the color of the columns in this chart, you can go to the Data Colors section and change it to Red.

Data Label

If you want to see the number of books sold for each book, you have to hover with your mouse on that column, and a little black box (tooltip) appears with that details. If you want to have that data value visible always, you can turn on the Data Labels.

Title

Another customization option to try now is the title of the visual. By default, the title of the visual will be automatically created by the fields that you add to the visual; in this case, it is qty and title. You can see the default title showed in the top-left corner of the visual.

You can change this title, and also apply different formatting on it in the Format tab, under Title section. Here is an example formatting for the title;

Next Steps

We still have lots of steps to add to this report and complete it to look like the screenshot below. In the next blog post, I’ll explain how to do the remaining steps.

Video

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.