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 PowerBI 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 book here.
Power BI Desktop
Power BI Desktop is the new development editor released 24th of July 2015 as general availability 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 Power BI website where users can see the report and interact with it from 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 mash 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 visualize the data with different charts and visualization elements.
Power BI Desktop has everything in one editor, and this makes it an easy to use tool, but don’t be 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 it’s 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. You can also install 64 or 32 bit options if you go to advanced download options.
For this example I used the old Pubs database (just to create something different than AdventureWorks samples). you can download it here and install Pubs database.
you can also download the Excel version of Pubs data from here:
Power BI Account:
If you don’t have a Power BI account, simply go to 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 latest news about Power BI or some tutorials of Power BI, or you can go to Power BI forums to ask 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 list of 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 and 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 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 Pubs database, and you’ll apply 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 left options choose Database, and in the main section select SQL Server Database. (Remember we are connecting to SQL Server instance that has Pubs database installed)
Next dialog box asks for server name and database name. Server name is necessary, but you can leave database name blank (blank will load all databases to choose from). You can also write a sql statement here if you want to. because my server for database is my local computer, so I just enter my computer name as below
You can set up credential in the next window. I use my current credentials, but note that there are other options such as entering an alternate credential or using database (SQL Server authorization)
you might get a message about encryption support, just click OK on that, we will cover that later. Because we’ve entered blank in the database name, now we see all databases, expand Pubs, and then select these tables: titles, stores, and sales.
As you see in above screenshot when you click on tables you’ll see a sample data rows of that appearing in the main section. this helps you to have an understanding of what you are looking at. Click on Edit. The Query Editor window will open. This is the main Power Query Window that has all the tables we’ve selected.
In Query Editor window in the left pane you can see three tables that we’ve loaded (numbered as 1). Main pane (numbered as 2) shows selected the data set from table in the left hand side. Right hand side shows the steps applied on the data set. the menu on top contains some transformations that you can apply on the data set.
Select sales table in left pane, now in the main pane you’ll see data columns of that table plus two hyperlinked columns (stores, and titles). Power Query is intelligent enough to understand relationship between tables and load them together. That is why stores and titles are here as hyperlinks, because Power Query joined them based on the FK-PK relationship in the database. Let’s don’t use that intelligence for now, so I’ll remove these two columns by right click on them and then selecting Remove.
Do the same in “titles” table, and remove columns: publishers, roysched, sales, titleauthor. Also from “stores” table remove discounts and sales columns. Now let’s make a very small change. I want to keep this example very simple, so regardless of best practices of having date dimension that have columns for year, months, week and etc, I just want to add a year column to the sales table. the year column that I want to add is the Order Year, and I can simply calculate that from the ord_date column which is a datetime value.
select sales query, then click on ord_date column, and then from the main menu click on Add Column. in the Add Column section under Date, choose Year.
A new column with name of Year will be added to the table, right click on the column and rename it to Order Year. Well done, you’ve done your first transformation with Power Query. It was so simple, wasn’t it?
Now let’s load data into a model, in Query Editor window under Home tab, click on Close & Load button. This will load all the queries with their data into the model.
You can see in the screenshot below that data is loading into the model
Now you will see the blank Power BI Desktop editor. From the left tabs, click on Relationship (last tab) as below to see the relationship diagram of your model
Now you see the diagram. Power Pivot intelligently created relationship between tables based on their relationship in the pubs database. if you double click on any of relationships you can modify it if you want to. Let’s just leave it as is, because relationship is just fine. The change that we want to do in the modelling is just adding a sales amount to sales table. (Note that sales table has the quantity only, and price is in titles table). Sales amount would be quantity sold multiplied by price of the title. Let’s do it in the model.
Click on Data Tab to see structure of data in each table. you can see that qty exists in sales table. If you look at titles, you’ll see price column there.
Go to sales table (from right pane choose sales), and then click on New Column. In the formula bar type in the formula below
Column = sales[qty]*RELATED(titles[price])
then press enter (you’ll see that intellisense also helps you to write down the formula). Now you’ll see the result in the new column. right click on it, and rename it to sales amount. Congratulations! you’ve written your first DAX expression here. Related function is a function that brings the relative row to this record based on the relationship of tables. so related(titles[price]) means from table “titles” bring column “price” but only price of that title that is related to this record (based on the relationship of title_id column.
Our model is ready now to be visualized in a report. Go to Report Tab (the default tab in Power BI Desktop). and click on stacked column chart from Visualizations pane. You’ll see an empty chart in the report window.
From the fields pane drag and drop sales amount into the Value section of the chart. then drag and drop city (from stores table) into Legend, and then drag and drop title (from titles table) into Axis. Done! you have a clustered column chart already. you can change height and width of the chart simply (I bet you’ll figure it out how to).
Let’s create a Map now. Click on an empty section of the report, and then click on Map. set Location as stor_address (from stores table), Values as qty (from sales table), and Legend as city (from stores table). Your map is now ready.
Let’s add a treemap to the experience as well. Set the treemap with Group as type (from titles table), Details as title (from titles table), and Values as qty (from sales table).
Let’s add a total quantity sold as a text box in the report as well. Add a Card from visualization, and choose qty to be displayed there.
Now add an slicer as the last item for your report. Slicer now can slice and dice the data by Order Year (remember we’ve added this column in modelling part)
Save the Power BI Desktop file as Pubs Analysis. In the Report tab of Power BI Desktop click on Publish (you’ll need to enter your account details here for the first time). You’ll get a success message when publish is completed.
Now open the Pubs Analysis in Power BI (just click on the link in publish window), and you’ll see your report online in web browser.
Your report is also fully interactive as well, click on charts and columns and maps to see how highlighting features works interactively.
Well done! you’ve made your first report with Power BI Desktop, and I know that you’ll say it was easy, fun, and interesting. In this section you’ve learned what is Power BI Desktop. You’ve connected to a SQL Server as data source and transformed the extracted data. You’ve loaded data into a model and added a calculated column to it. You’ve created a report with charts and visualization elements, and finally you published your report in Power BI website. In next section we will go to Power BI website and Apps to see how they works.