Power BI Desktop Tutorial

Welcome to the getting started guide for Power BI Desktop. This tour shows you how Power BI Desktop works, what it can do, and how to build robust data models and amazing reports to amplify your business intelligence.

For a quick overview of how Power BI Desktop works and how to use it, you can scan the screens in this guide in just a few minutes. For a more thorough understanding, you can read through each section, perform the steps, and create your own Power BI Desktop file to post on the Power BI service and share with others.

You can also watch the Getting Started with the Power BI Desktop video, and download the Financial Sample Excel workbook to follow along with the video.Important

Power BI Desktop is updated and released on a monthly basis, incorporating customer feedback and new features. Only the most recent version of Power BI Desktop is supported; customers who contact support for Power BI Desktop will be asked to upgrade to the most recent version. You can get the most recent version of Power BI Desktop from the Windows Store, or as a single executable containing all supported languages that you download and install on your computer.

How Power BI Desktop works :

1. 1.Connect to data, including multiple data sources.
2. 2.Shape the data with queries that build insightful, compelling data models.
3. 3.Use the data models to create visualizations and reports.
4. 4.Share your report files for others to leverage, build upon, and share. You can share Power BI Desktop .pbix files like any other files, but the most compelling method is to upload them to the Power BI service.

Power BI Desktop integrates proven Microsoft query engine, data modeling, and visualization technologies. Data analysts and others can create collections of queries, data connections, models, and reports, and easily share them with others. Through the combination of Power BI Desktop and the Power BI service, new insights from the world of data are easier to model, build, share, and extend.

Power BI Desktop centralizes, simplifies, and streamlines what can otherwise be a scattered, disconnected, and arduous process of designing and creating business intelligence repositories and reports. Ready to give it a try? Let’s get started.

Note

For data and reporting that must remain on-premises, there’s a separate and specialized version of Power BI called Power BI Report Server. Power BI Report Server uses a separate and specialized version of Power BI Desktop called Power BI Desktop for Power BI Report Server, which works only with the Report Server version of Power BI. This article describes standard Power BI Desktop.

Power BI Desktop :

On the Microsoft Store page, select Get, and follow the prompts to install Power BI Desktop on your computer. Start Power BI Desktop from the Windows Start menu or from the icon in the Windows taskbar.

The first time Power BI Desktop starts, it displays the Welcome screen.

From the Welcome screen, you can Get data, see Recent sources, open recent reports, Open other reports, or select other links. You can also choose whether to always show the Welcome screen at startup. Select the close icon to close the Welcome screen.

Along the left side of Power BI Desktop are icons for the three Power BI Desktop views: Report, Data, and Relationships, from top to bottom. The current view is indicated by the yellow bar along the left, and you can change views by selecting any of the icons.

Power BI Desktop also includes the Power Query Editor, which opens in a separate window. In Power Query Editor, you can build queries and transform data, then load the refined data model into Power BI Desktop to create reports.

Connect to data :

With Power BI Desktop installed, you’re ready to connect to the ever-expanding world of data. To see the many types of data sources available, select Get Data > More in the Power BI Desktop Home tab, and in the Get Data window, scroll through the list of All data sources. In this quick tour, you connect to a couple of different Web data sources.

Imagine you’re a data analyst working for a sunglasses retailer. You want to help your client target sunglasses sales where the sun shines most frequently. The Bankrate.com Best and worst states for retirement page has interesting data on this subject.

On the Power BI Desktop Home tab, select Get Data > Web to connect to a web data source.If prompted, on the Access Web Content screen, select Connect to use anonymous access.

The query functionality of Power BI Desktop goes to work and contacts the web resource. The Navigator window returns what it found on the web page, in this case a table called Ranking of best and worst states for retirement, and a document. You’re interested in the table, so select it to see a preview.

At this point you can select Load to load the table, or Transform data to make changes in the table before you load it.

When you select Transform data, Power Query Editor launches, with a representative view of the table. The Query Settings pane is on the right, or you can always show it by selecting Query Settings on the View tab of Power Query Editor.

Shape data :

Now that you’re connected to a data source, you can adjust the data to meet your needs. To shape data, you provide Power Query Editor with step-by-step instructions for adjusting the data while loading and presenting it. Shaping doesn’t affect the original data source, only this particular view of the data.

Note :

The table data used in this guide might change over time. As such, the steps you need to follow might vary, requiring you to be creative about how you adjust steps or outcomes, which is all part of the fun of learning.

Shaping can mean transforming the data, such as renaming columns or tables, removing rows or columns, or changing data types. Power Query Editor captures these steps sequentially under Applied Steps in the Query Settings pane. Each time this query connects to the data source, those steps are carried out, so the data is always shaped the way you specify. This process occurs when you use the query in Power BI Desktop, or when anyone uses your shared query, such as in the Power BI service.

Notice that the Applied Steps in Query Settings already contain a few steps. You can select each step to see its effect in the Power Query Editor. First, you specified a web source, and then you previewed the table in the Navigator window. In the third step, Changed type, Power BI recognized whole number data when importing it, and automatically changed the original web Text data type to Whole numbers.

If you need to change a data type, select the column or columns to change. Hold down the Shift key to select several adjacent columns, or Ctrl to select non-adjacent columns. Either right-click a column header, select Change Type, and choose a new data type from the menu, or drop down the list next to Data Type in the Transform group of the Home tab, and select a new data type.

Note :

The Power Query Editor in Power BI Desktop uses the ribbon or the right-click menus for available tasks. Most of the tasks you can select on the Home or Transform tabs of the ribbon are also available by right-clicking an item and choosing from the menu that appears.

You can now apply your own changes and transformations to the data and see them in Applied Steps.

For example, for sunglasses sales you’re most interested in the weather ranking, so you decide to sort the table by the Weather column instead of by Overall rank. Drop down the arrow next to the Weather header, and select Sort ascending. The data now appears sorted by weather ranking, and the step Sorted Rows appears in Applied Steps.

You’re not very interested in selling sunglasses to the worst weather states, so you decide to remove them from the table. From the Reduce Rows group of the Home tab, select Remove Rows > Remove Bottom Rows. In the Remove Bottom Rows dialog box, enter 10, and then select OK.

The bottom 10 worst weather rows are removed from the table, and the step Removed Bottom Rows appears in Applied Steps.

You decide the table has too much extra information for your needs, and to remove the Affordability, Crime, Culture, and Wellness columns. Select the header of each column that you want to remove. Hold down the Shift key to select several adjacent columns, or Ctrl to select non-adjacent columns.

Then, from the Manage Columns group of the Home tab, select Remove Columns. You can also right-click one of the selected column headers and select Remove Columns from the menu. The selected columns are removed, and the step Removed Columns appears in Applied Steps.

On second thought, Affordability might be relevant to sunglasses sales after all. You’d like to get that column back. You can easily undo the last step in the Applied Steps pane by selecting the X delete icon next to the step. Now redo the step, selecting only the columns you want to delete. For more flexibility, you could delete each column as a separate step.

You can right-click any step in the Applied Steps pane and choose to delete it, rename it, move it up or down in the sequence, or add or delete steps after it. For intermediate steps, Power BI Desktop will warn you if the change could affect later steps and break your query.

For example, if you no longer wanted to sort the table by Weather, you might try to delete the Sorted Rows step. Power BI Desktop warns you that deleting this step could cause your query to break. You removed the bottom 10 rows after you sorted by weather, so if you remove the sort, different rows will be removed. You also get a warning if you select the Sorted Rows step and try to add a new intermediate step at that point.

Finally, you change the table title to be about sunglass sales instead of retirement. Under Properties in the Query Settings pane.The finished query for your shaped data looks like this:

Combine data :

The data about various states is interesting, and will be useful for building additional analysis efforts and queries. But there’s one problem: most data out there uses two-letter abbreviations for state codes, not the full names of the states. To use that data, you need some way to associate your state names with their abbreviations.

You’re in luck. Another public data source does just that, but the data will need a fair amount of shaping before you can combine it with your sunglass table.

To import the state abbreviations data into Power Query Editor, select New Source > Web from the New Query group on the Home tab of the ribbon.

In the Navigator window, select the table Codes and abbreviations for U.S. states, federal district, territories, and other regions, and then select OK. The table opens in Power Query Editor.

Remove all columns except for Name and status of region, Name and status of region2, and ANSI. To keep only these columns, hold down Ctrl and select the columns. Then, either right-click one of the column headers and select Remove Other Columns, or, from the Manage Columns group of the Home tab, select Remove Other Columns.

Drop down the arrow next to the Name and status of region2 column header, and select Filters > Equals. In the Filter Rows dialog box, drop down the Enter or select a value field next to equals and select State.

Select Or, and next to the second equals field, select State (“Commonwealth”). Select OK.With extra values like Federal district and island removed, you now have a list of the 50 states and their official two-letter abbreviations. You can rename the columns to make more sense, for example State name, Status, and Abbreviation, by right-clicking the column headers and selecting Rename.

Note that all of these steps are recorded under Applied Steps in the Query Settings pane.Your shaped table now looks like this:

With the State codes table shaped, you can combine these two tables into one. Since the tables you now have are a result of queries you applied to the data, they’re also called queries. There are two primary ways of combining queries: merge and append.

When you have one or more columns you’d like to add to another query, you merge the queries. When you have additional rows of data you’d like to add to an existing query, you append the query.

In this case, you want to merge the State codes query into the Best states for sunglasses query. To merge the queries, switch to the Best states for sunglasses query by selecting it from the Queries pane on the left side of Power Query Editor. Then select Merge Queries from the Combine group in the Home tab of the ribbon.

In the Merge window, drop down the field to select State codes from the other queries available. Select the column to match from each table, in this case State from the Best states for sunglasses query and State name from the State codes query.

If you get a Privacy levels dialog, select Ignore privacy levels checks for this file and then select Save. Select OK.

A new column called State codes appears on the right of the Best states for sunglass sales table. It contains the state code query that you merged with the best states for sunglass sales query. All the columns from the merged table are condensed into the State codes column. You can expand the merged table and include only the columns you want.

To expand the merged table and select which columns to include, select the Expand icon in the column header. In the Expand dialog box, select only the Abbreviation column. Deselect Use original column name as prefix, and then select OK.

Note :

You can play around with how to bring in the State codes table. Experiment a bit, and if you don’t like the results, just delete that step from the Applied Steps list in the Query Settings pane. It’s a free do-over, which you can do as many times as you like until the expand process looks the way you want it.

You now have a single query table that combines two data sources, each of which has been shaped to meet your needs. This query can serve as a basis for lots of additional, interesting data connections, such as demographics, wealth levels, or recreational opportunities in the states.

For now, you have enough data to create an interesting report in Power BI Desktop. Since this is a milestone, apply the changes in Power Query Editor and load them into Power BI Desktop by selecting Close & Apply from the Home tab of the ribbon. You can also select just Apply to keep the query open in Power Query Editor while you work in Power BI Desktop.

You can make more changes to a table after it is loaded into Power BI Desktop, and reload the model to apply any changes you make. To reopen Power Query Editor from Power BI Desktop, select Edit Queries on the Home tab of the Power BI Desktop ribbon.

Build reports :

In Power BI Desktop Report view, you can build visualizations and reports. The Report view has six main areas:

1. 1.The ribbon at the top, which displays common tasks associated with reports and visualizations.
2. 2.The canvas area in the middle, where visualizations are created and arranged.
3. 3.The pages tab area at the bottom, which lets you select or add report pages.
4. 4.The Filters pane, where you can filter data visualizations.
5. 5.The Visualizations pane, where you can add, change, or customize visualizations, and apply drillthrough.
6. 6.The Fields pane, which shows the available fields in your queries. You can drag these fields onto the canvas, the Filters pane, or the Visualizations pane to create or modify visualizations.

You can expand and collapse the Filters, Visualizations, and Fields panes by selecting the arrows at the tops of the panes. Collapsing the panes provides more space on the canvas to build cool visualizations.

To create a simple visualization, just select any field in the fields list, or drag the field from the Fields list onto the canvas. For example, drag the State field from Best states for sunglass sales onto the canvas, and see what happens.

Look at that! Power BI Desktop recognized that the State field contained geolocation data and automatically created a map-based visualization. The visualization shows data points for the 40 states from your data model.

The Visualizations pane shows information about the visualization and lets you modify it.

1. 1.The icons show the type of visualization created. You can change the type of a selected visualization by selecting a different icon, or create a new visualization by selecting an icon with no existing visualization selected.
2. 2.The Fields option in the Visualization pane lets you drag data fields to Legend and other field wells in the pane.
3. 3.The Format option lets you apply formatting and other controls to visualizations.

The options available in the Fields and Format areas depend on the type of visualization and data you have.

You want your map visualization to show only the top 10 weather states. To show only the top 10 states, in the Filters pane, hover over State is (All) and expand the arrow that appears. Under Filter type, drop down and select Top N. Under Show items, select Bottom, because you want to show the items with the lowest numerical ranks, and enter 10 in the next field.

Drag the Weather field from the Fields pane into the By value field, and then select Apply filter.

Share your work :

Now that you have a Power BI Desktop report, you can share it with others. There are a few ways to share your work. You can distribute the report .pbix file like any other file, you can upload the .pbix file from the Power BI service, or you can publish directly from Power BI Desktop to the Power BI service. You must have a Power BI account to be able to publish or upload reports to Power BI service.

Power BI Tutorial :

Before diving into the Power BI introduction, let’s have a quick look at Business intelligence (BI). It refers to taking raw data from a data source, transforming it into usable data and utilizing it to make reports and informative graphics for data analysis.

Graphically representing tabular data is known as data visualization. It enables a user to visualize important information through charts, graphs, KPIs, maps, etc. to attain valuable insights just by looking at them. Well Microsoft Power BI is a tool having business intelligence and data visualization capabilities.

Before we begin with this journey of learning a new BI technology called Microsoft Power BI, we must learn some basics about it. We request you to take some time out and explore this Power BI tutorial thoroughly as it will act as a building block for learning Power BI technology.

What is Power BI?

Power BI is a cloud-based business analysis and intelligence service by Microsoft. It is a collection of business intelligence and data visualization tools such as software services, apps and data connectors.

We can use the datasets imported in Power BI for data visualization and analysis by making sharable reports, dashboards, and apps. Power BI is a user-friendly tool offering impressive drag-and-drop features and self-service capabilities.

Microsoft offers three types of Power BI platforms:

• Power BI Desktop (A desktop application)
• Power BI Service (SaaS i.e., Software as a Service)
• Power BI Mobile (For iOS and Android devices)

Also, we can deploy Power BI on both on-premise and on-cloud platforms.

In the image given below, have a look at the process flow in Power BI.

Why Power BI?

As we learned in the previous section of Power BI tutorial that, Power BI is an umbrella term having several different kinds of services under its tutelage.

1. 1.There is a cloud-based BI service called Power BI Services used to view and share dashboards.
2. 2.A desktop-based reporting interface known as Power BI Desktop.
3. 3.Another useful service is Power BI Embedded that runs on an Azure cloud platform and we can use it for report creation, ETL and data analysis.

Further, let us discuss a few points regarding why Power BI is an important tool in today’s time and why do we need it.

• Real-time analysis in Power BI can be done by establishing direct connections to the data sources. Also, it keeps data updated to the latest second by data refreshing.
• You can use custom visualizations from a custom visuals gallery. Custom visuals are divided into many options and categories.
• You can quickly search for important insights and datasets within your data by using the Quick Insights option.
• Establish a live or non-live connection to on-premises data sources like SQL Server, and use a secure channel to access data through data gateways. This makes Power BI enterprise-ready as on-premises connections make data transfer secure and the technology scalable and reliable.
• You can connect to other services through Power BI such as SQL Server Analysis Services (SSAS), Microsoft Excel, etc.
• Power BI is a new age software using the latest technologies such as HTML 5.0, column store databases, cloud computing, mobile apps, etc. This helps in keeping Power BI on the top and popular as it is constantly getting updated with the latest features.

History of Power BI :

Power BI is a Microsoft’s product initially released on 11th July 2011. It was originally designed and created by Ron George in 2010, who released it with the name “Project Crescent”. Later in September of 2013, Microsoft changed the name to Power BI and launched it for the public.

This release was a Power BI for Office 365 and had Microsoft Excel add-ins, Power Pivot, Power View, Power Query in it. In later versions, Microsoft added advanced features like natural language Q&A, enterprise-level data security and connectivity, Power data gateways, etc.

Power BI’s first general public release was on July 24th, 2015. As of 2019, Power BI has been officially declared as one of the leading BI tools by 2019 Gartner Magic Quadrant for Analytics and Business Intelligence Platform.

Power BI Features :

There are some of the most important and interesting features of Power BI:

• Visualizations/ custom visualizations
• GetData (Data sources)/data connections
• Datasets
• Dashboards
• Filters
• Ad hoc analysis
• Reports/ ad hoc reporting
• Trend indicators
• Online Analytical Processing (OLAP)
• Navigation pane
• Natural language Q & A box
• DAX functions and formula
• Office 365 app launcher
• Content packs
• Authoring interactive reports

Power BI Components :

Power BI is a business intelligence and data mining software suite which is a collection of different kinds of services by Microsoft. These services play a specific role and work in coordination with each other, to make Power BI function as a whole. In this section of the Power BI tutorial, we will learn about each of these Power BI services or components and their roles.

• Power Query: We use this service to access, search and transform data from public or local/internal data sources.
• Power Pivot: This service provides tools to model data taken from the in-memory data source to use it for analytics.
• Power View: This service has many tools to graphically represent data using visuals and use them for analysis.
• Power Map: It comes with tools and capabilities to visualize Geo-spatial data or information in the 3D model in a map. You can use these maps in a Power BI report.
• Power BI Desktop: It is a companion development tool for Power View, Power Query, and Power Pivot. You can import data from a data source, prepare and transform it and use it in visualizations to create reports in Power BI Desktop.
• Power BI Website: It is a web platform to view and share Power BI apps or solutions. Using Power BI Website, you can create dashboards from reports, share the dashboards with other Power BI users and slice and dice data within a report.
• Power Service: The Power Service enables the sharing of workbooks and data views with other users. The data gets refreshed at regular intervals from the on-premises or/and cloud-based data sources.
• Power Q&A: Using the Power Q&A option, you can search for your data or discover insights by entering queries in natural language. It instantly understands your query and returns relevant results.
• Power BI Mobile apps: Business users view and interact with the reports and dashboards published on a cloud service through mobile hosted Power BI instances. Android, Windows and iOS mobile devices support the Power BI mobile apps.
• Data Catalog: The Data Catalog option offers the capability to search and reuse queries.
• Data Management Gateway: This component manages the periodic data refreshes, data feed viewing and table exposing.