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.
Power BI Desktop:
Download Power BI Desktop from this link.
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
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:
- you can watch some Power BI Desktop tutorial videos.
- 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.
- 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.
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);
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.
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.
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.
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.
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;
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.
19 thoughts on “Power BI Desktop; Getting Started – Part 1”
Thanks for the tutorials – nice job so far.
But I do see something with ‘The First Experience’ tutorial that needs attention. I think your corrections could also make a nice addendum to the lesson.
Here’s the problem. On the Map it looks like ‘City’ information by itself is inadequate for Bing to properly find the store locations. The ‘stores’ database shows locations in the States of Washington, California and Oregon yet the map shows them all over the place from Alaska to the Eastern United States.
Could you please show us how to fix this so the Map visualization correctly reflects the actual store locations?
Very good point. and thanks for your great feedback.
In this example I tried to keep things simple, but you are absolutely right, the city itself is not enough for Bing to point to the right location. The main point to work with map is always to point in the most specific way. for this example city and state can be combined together to create a new column (you can do this in edit queries section, or in the data tab) the new column can be named something like CityLongPath, the CityLongPath would have combination of state and city such as : Portland, Oregon . Then when you use this new column in map Bing will identify that better.
If sometimes you have data sets that have other geo columns such as country, address line, suburb… it is always better to create a full address path with concatenation of them.
Thanks again for your correction Doug. really appreciate it. I will add this tip as Addendum to this part soon.
I’cant get pues dB installed. I get an error in sqlsever. could YOU send me a pubs database simple in .bak file format? Thank YOU in Advanced.
Note. I found northwith in .bak AND whole was ok.
Thanks for trying the sample.
I’ve sent .BAK file to your email address.
Hi, you say in the article “you might get a message about encryption support, just click OK on that, we will cover that later.” But I don’t see that you covered it… did I just look over the top of it? I’ve been asked to research this issue and you’re the closest to an answer I’ve come. Thanks for any help you can offer!
Thanks for your feedback. by that “later” I meant later posts, which should be published soon. as this blog post is part of the online book each post represent a section, but unfortunately I had no time to get to that part yet.
I can help you more in details if you let me know what exactly is the issue you are facing? You can also email that to me; reza at radacad
I am unable to get pubs database from the sql database. Could u please send me one
I’ve added a link to download it directly from this post (Excel version of it)
please i can’t find a post describing the NEXT Steps part for creating the map and publishing the report
This is the next blog article in this series.
I found the explanation great. I am a new user (a teacher in higher education) and just installed Power BI can you help me out to understand it as a beginner please? I want to learn to teach my students.
Help from anyone is appreciated.
I’m glad to see it helped you 🙂