How to use Power BI – Getting Started with Microsoft Power BI Desktop

Last Updated: April 02, 2024
puneet-gogia-excel-champs

- Written by Puneet

If you are looking for an in-depth guide to learning Power BI, well, this a guide that will help you to start learning Power BI to understand all the basics and create your first report.

What is Power BI?

Power BI is a reporting, data analysis (business analytics), and visualization tool. It allows users to import or connect to data from various sources, compile and clean it in one location for analysis, and create detailed reports and dashboards. Power BI transforms raw data into meaningful insights, making it a useful tool for any business.

Why Should I Learn Power BI?

Learning Power BI has numerous advantages, especially for individuals interested in data analysis, visualization, and business intelligence. Here are a few reasons:

  • Data Integration: Power BI can connect and integrate data from various sources, making it a versatile tool for data analysis.
  • User-Friendly: Power BI’s user-friendly interface makes it easy to create comprehensive reports and visualizations, even for beginners.
  • Advanced Analytics: With Power BI, you can perform advanced analytics using built-in features such as quick measures, forecasting, and clustering.
  • Real-Time Insights: Power BI allows for real-time data exploration, providing valuable business insights and aiding decision-making.
  • High Demand in the Job Market: Power BI skills are in high demand. Learning Power BI can thus open a wide range of job opportunities in data science and business intelligence.

Download Power BI Desktop

To download Power BI Desktop, follow these steps:

download-power-bi
  • Visit the Power BI Download Page: Access this page by navigating to the Power BI homepage and selecting “Downloads” from the menu.
  • Choose Power BI Desktop: On the download page, find and select the option for Power BI Desktop.
  • Download the Installer: Once you’ve selected Power BI Desktop, you’ll be prompted to download the installer. Click the “Download” button to start this process.
  • Install Power BI Desktop: Open the installer once the download is complete and follow the provided instructions to install Power BI Desktop on your computer.

Power BI Desktop is a Windows application, so you’ll need a Windows PC to install it.

power-bi-desktop

Signing Up for a Power BI Account

Once you download it, thing you need to do is sign up for an account.

signup-power-bi-account

Here are the steps to do so:

  • Visit the Power BI Website. You can access it from any browser. Navigate to the Power BI homepage.
  • Enter your email address: Power BI requires a work or school email address for the signup process. Unfortunately, it does not support email addresses provided by telecommunications providers or consumer email services. So, you’ll need to use your work or school email.
  • Follow the signup Instructions: After entering your email address, click “Next”. Power BI will guide you through the rest of the signup process. This typically involves verifying your email address, setting up a password, and providing some basic information about yourself.

Power BI Licensing

Microsoft offers various licensing options for Power Bi catering to different user needs and organizational requirements:

power-bi-pricing
  • Power BI Free: This is the free version of Power BI, and it allows users to create and view their own reports and dashboards, but sharing capabilities are limited.
  • Power BI Pro: This subscription-based version enables users to collaborate with colleagues, share reports and dashboards, and publish content to the Power BI service.
  • Power BI Premium: Aimed at larger businesses, this version provides greater capacity and flexibility for organizations that require more extensive usage and distribution of reports. It offers dedicated resources, excellent storage, and more significant data refresh rates than Power BI Pro.

Remember to choose the licensing option that best suits your business requirements.

Note – Microsoft also offers you “Power BI Embedded” which is designed for developers and businesses who wish to embed visuals or reports within an application or portal. This version allows integration of Power BI features directly into a product offering.

Components of the Power BI Desktop

Power BI Desktop has several major components:

  • Ribbon: Located at the top, the Ribbon contains several tabs, each with a set of related options. These options are used to perform various tasks in Power BI, such as importing data, creating visualizations, and publishing reports.
  • Canvas: This is the main work area where you create reports. You can add and arrange visualizations on the canvas.
  • Fields Pane: Located on the right side, the Fields pane lists all the tables, columns, and measures in your dataset. You use this pane to create and modify visualizations.
  • Visualizations Pane: This pane, which is located below the Fields pane, contains different types of visualizations, like charts and tables. You can add these to your report by dragging them onto the canvas.
  • Filters Pane: Located below the Visualizations pane, the Filters pane is used to apply filters to your report. These filters can be applied at the visual, page, or report level.
  • Pages Tab: Located at the bottom, the Pages tab allows you to add, rename, duplicate, or delete report pages.
  • Data View and Model View: These views are accessible from the left side of the window. The Data view lets you see your data in table format, and the Model view lets you see and manage relationships between tables.

You can use Power BI to create detailed and insightful reports by understanding these components.

Get Data into the Power BI (Load Data)

Process of loading data into Power BI Desktop

  • Launch Power BI Desktop: After installing Power BI Desktop, locate it in your Applications and open it. And the click on the “New” button to create a new report.
launch-power-bi-desktop
  • Access the ‘Get Data’ Option: Once the Power BI Desktop is open, navigate to the Home tab in the ribbon at the top of the application. Here, you will find the “Get Data” option.
get-data-option
  • Select Data Source: Power BI Desktop can import data from a wide range of sources. These include Excel files, SQL Server databases, websites, other databases like MySQL and Oracle, simple text files or CSV files, SharePoint lists, Azure services, and other services like Google Analytics and Salesforce. Choose the type of data source that contains the data you want to import.
  • Navigate to Your File: After selecting the appropriate data source, navigate to the location of your file or server. This can be a file saved on your computer or a database on an external server.
  • Load the Data: After selecting your data, click “Load”. Power BI Desktop will process the data and load it into the program.
load-the-data

The moment you click the “Load” button, it loads the data (excel file) to the Power BI and show a pane at the right side of the window, where you have names of the columns to use in the report.

columns-name

Types of Data Sources to Load in Power BI

There is options to import various data sources into Power BI:

  • Excel: You can import data directly from Excel files, including data in tables and data models created in Excel.
  • SQL Server: Power BI can connect directly to SQL Server databases, both on-premises and in the cloud (Azure).
  • Web: Power BI can scrape data from websites. This is useful if the data you need is publicly available on a website, but not available in a downloadable format.
  • Other Databases: Power BI supports a wide range of other databases, including MySQL, Oracle, and many others.
  • CSV/Text Files: You can import data from simple text files, including CSV files.
  • SharePoint: You can import data from SharePoint lists.
  • Azure: Power BI can connect to a variety of Azure services, including Azure SQL Database, Azure Data Lake Storage, and more.
  • Other Services: Power BI supports importing data from a wide range of other services, such as Google Analytics, Salesforce, and many more.

Create Your First Power BI Report

The data that we have as a sample is data of contacts, and now we need to create a basic dashboard with it.

1. Add a Card for “Amount” and “Quantity”

A card is a type of visualization in Power BI that displays a single value. This value can be a sum, average, or other aggregate. Cards are an excellent way to highlight a single significant value. For example, if you have a sales dataset, you could create a card to display the total sales.

This value is easily seen and understood immediately, making cards a powerful tool for emphasizing key metrics. Let’s follow the below steps:

  • Select and Add the “Card” Visualization: The “Card” icon usually resembles a rectangle with a number in it. Click on this icon to add a new blank card to your report canvas. The report canvas is the main work area in the middle of the Power BI Desktop interface.
card-visualization
  • Add Data to the Card: Once you’ve added a card to your report canvas, the next step is adding data. To do this, use the “Fields” pane. Here, you’ll see a list of your dataset’s tables and fields. Locate the field you want to display on the card, then click and drag this field into the “Amount” field in the “Visualizations” pane. The “Values” field well is usually located below the icons in the “Visualizations” pane.
add-data-to-card
  • Change the field’s name in the Visual: Once you add data to the card, you must change it to make it more meaningful. So, for this, you need to click on the down arrow and then click on the “Rename for this visual.
rename-the-visual
  • Now, your first card in the Power BI desktop with the amount is on the canvas.
first-card-in-power-bi-desktop

In the same way, you can add another card to show the total quantity.

You just need to use the same steps and then change the name of the field to “Quantity”. And you will have two cards in the canvas.

Like we have in the below screenshot.

add-another-card

You can also change the formatting and the style of the card from the “Format Visual” section.

change-formatting-and-style-of-card
  • Select the card visual you want to customize.
  • In the Visualizations pane, select the Format button (represented by a paint roller icon).
  • You can customize the card’s appearance, including the font color, background color, and text size.
  • Once you’ve made your changes, click “Apply” to save them.

Customization options can greatly enhance the effectiveness and readability of your Power BI reports.

enhance-power-bi-reports

2. Add a Bar Chart for Top 10 Cities

Now let’s add bar chart for the top 10 cities from the data we have in the example.

  • Among the options in the “Visualizations” pane, you will find the “Bar chart” icon. Click on this icon to create a new bar chart.
add-a-bar-chart
  • With the new bar chart created, you will need to assign data to the X and Y axes. To do this, drag the field you want to represent on the X-axis to the “Axis” area. Similarly, drag the field you want to represent on the Y-axis to the “Values” area. For our example, add “Amount” in the X-Axis and city in the “Y-Axis”.
assign-data-to-chart-axes
  • Within the “Filters” section, you’ll find a designated area labeled “Filters on this visual”. Drag the field “city” field in the “Filter on this visual” if it not there already. Otherwise, from the filter type dropdown menu, select “Top N”.
filter-section-options
  • In the input box that appears after selecting “Top N”, enter ’10’. This tells Power BI that you want to filter by the top 10 values in the chosen field. Next, select the field you want to use for the Top 10 filter from the “By value” dropdown menu. Drag and drop “amount” in it.
input-box
  • Once you’ve made all these selections, click “Apply filter”. This will apply all the changes you’ve made to your bar chart.
click-apply-filter

Now you have a chart like the following in your canvas.

chart-ready-in-canvas

3. Create a Year, Quarter, and Month Wise Table

To add a table in Power BI, follow these steps:

  • Among the options in the “Visualizations” pane, find the “Table” icon. Click on this icon to create a new table.
create-a-table
  • With the new table created, you will need to assign data to it. To do this, drag the fields you want to include in the table to the “Values” area in the “Visualizations” pane. So, add the date and the “amount” to the columns box.
drag-the-fields-to-include
  • This will add a table in the canvas like the one we have in the example.
table-in-canvas
  • Once you’ve assigned the data, you may want to customize the look of your table to make it more visually appealing or easier to read. To do this, click on the “Format” button, which is also found in the “Visualizations” pane.
  • The “Format” button will allow a range of customizations. Here, you can change aspects such as the color scheme, the font size, and more. Spend some time exploring these options and adjusting as you see fit.
range-of-customization
  • After you’ve made your changes, it’s important to save your work. To do this, click on the “Apply” button.

So now, you first Power BI Dashboard is Ready.

power-bi-dashboard

Save Power Bi Dashboard

To save a Power BI dashboard:

save-dashboard
  • Navigate to the “File” menu, which is located at the top left corner of the screen. Click on this “File” menu to open a drop-down list of options. From the File menu, select “Save As”. This will open a dialog box.
  • You will be prompted to choose a location where you want to save the file. You can choose to save the file on your local device or choose a cloud storage location if you wish to access the file from different devices.
  • After choosing a suitable location, you can enter a name for the dashboard. Once you’ve entered a name, click “Save”. Your Power BI dashboard will then be saved to the location you specified.

Publish Power BI Dashboard

Publishing a Power BI Dashboard allows you to share your reports for further analysis and visualization. Here are the steps to do this:

  • On the Home tab, you’ll see an option labelled ‘Publish.’ Click on this to initiate the publishing process.
  • A new window will appear, asking you to select the workspace in the Power BI service where you’d like to publish your report.
  • A window will appear, asking you to select the workspace in the Power BI service where you’d like to publish your report.
  • After you’ve selected your workspace, the ‘Publishing to Power BI’ dialog box will appear. This might take a few moments to complete, so be patient.
  • Your report is now available in the selected workspace.

Power BI Workspace

In Power BI, a Workspace is much more than just a storage location.

It’s a collaborative space where you and your colleagues can collaborate on your analytics projects.

It allows you to create, store, and manage collections of various items, including dashboards, reports, datasets, paginated reports, and dataflows.

Power Query to Transform Data in Power BI

Power Query is a data engine that enables you to connect, combine, and refine data across various sources.

Here are the steps to use Power Query in Power BI:

  • Go to the ‘Home’ tab. Click on ‘Edit Queries’ to open Power Query Editor.
transform-data-in-power-bi
  • You can use the tools in the Power Query Editor to clean and transform your data. This includes changing data types, removing columns, and merging queries.
power-query-editor
  • Once you have finished cleaning and transforming your data, click on ‘Close & Apply’ in the ‘Home’ tab. This will apply your changes and load the data into Power BI Desktop.
close-and-apply

Changes you make in the Power Query Editor can be saved as queries, which can be reused or shared with others.

I suggest you check this complete guide on power query.

Refreshing Data in Power BI

Refreshing data in Power BI is an essential step to ensure that your reports and visuals are up to date.

refresh-data-in-power-bi

Here are the steps to do it:

  • Locate the Refresh button: Once your report is open, locate the ‘Refresh’ button in the toolbar at the top of the screen.
  • Click Refresh: Click on the ‘Refresh’ button to update your report with the latest data.
  • Check your data: After the refresh is complete, check your report to make sure the latest data is displaying correctly.

Use Data Models in Power BI

Data modelling in Power BI allows users to build complex data models from multiple data sources.

Power BI’s data modelling allows you to create relationships between different data sets, calculate columns and measures using DAX (Data Analysis Expressions) formula language, and manipulate data as needed.

This, coupled with Power BI’s powerful visualization tools, allows users to explore and analyze data more interactively and intuitively.

To understand the usage of the data models let’s take an example. In this example, we have a list of state names and state abbreviations. Now we need to content this list with our main data in which we don’t have the full state names.

Load this file (Sheet1) also into the Power BI using the steps we have learned above.

locate-file

Once your data is imported, you must switch to the ‘Model’ view. You can do this by clicking on the ‘Model’ icon located in the left pane of the Power BI interface.

switch-to-model-view

In the ‘Model’ view, you will see imported data tables. Each table represents a different data source. To create a relationship, you need to click on Abv (Sheet1) and drag it to the state (us-50000). A line will appear connecting the two fields, indicating an established relationship.

imported-data-tables

When you right-click the arrow and click properties, the edit relationships window opens. This window is where you define the relationship’s specifics.

click-properties

You can specify the relationship type, whether it’s one-to-one, one-to-many, or many-to-one. You can also set the cross-filter direction, which determines how the relationship affects the data filtering.

specify-relationship-type

It’s important to note that creating accurate relationships is a crucial step in developing your data model.

The relationships you define directly impact the results of your calculations and the accuracy of your reports.

Incorrect relationships can lead to misleading data analysis and interpretations. Therefore, always ensure that your relationships accurately reflect the connections between your data.

Using Dax in Power BI

DAX, or Data Analysis Expressions, is a formula language used in Power BI for creating custom calculations in Power Pivot, Analysis Services, and Power BI Desktop.

It allows users to define custom measures from existing data.

Let’s take an example: we have the data where we have a column for cities and now we need to create card for the unique count of the cites to show that how many cities we have sold our products.

  • In Power BI Desktop, you can create a new measure or calculated column in your dataset. To do this, go to Modelling and click on the “New measure” button.
click-modeling-tab
  • A formula bar will appear at the top of your screen. This is where you can write your DAX formula. The syntax of DAX formulas is like Excel formulas. Begin by typing the name of your new measure or column, followed by an equal sign (=). Then, write the DAX formula. For example, a DAX formula for unique cities will be Unique Cities = DISTINCTCOUNT(‘us-50000′[city]).
write-dax-formula
  • After writing your DAX formula, press Enter to apply it. The new measure or column will be added to your dataset and can be used in your reports.
new-column-added-to-dataset
  • You can use your new measure or column in your reports like any other field. Drag it onto your report canvas or use it in a visual field list.
use-new-column-in-reports

Quick measures in Power BI

Quick measures in Power BI can quickly and easily create new measures based on your dataset’s existing measures and numerical columns.

They use the DAX formula, which is automatically created based on input.

quick-measures-in-power-bi
  • In the Home Tab, click on the quick measures button.
  • In the Quick Measures dialog box, choose a calculation you want to perform from the calculation list.
  • Depending on the calculation you choose; you may need to select additional fields.
  • After you’ve made your selections, click ‘OK’.

Common Terms in Power BI

  • Workspace: A place to collaborate with colleagues, create collections of dashboards, reports, datasets, and paginated reports.
  • Report: A collection of visuals, reports, or other data that provides a consolidated view of business data.
  • Dashboard: A single canvas that contains one or more tiles. Each tile displays a single visual chart, graph, map, or other visual representation of data.
  • Dataset: A data collection tool that Power BI uses to create visuals.
  • Visual: A graphical representation of data.
  • Measure: A calculation for the data in your tables is always aggregated.
  • Calculated Column: A column you add to an existing Power BI data model table.
  • Data Analysis Expressions (DAX): A formula language used in Power BI.

Power BI Vs. Excel

Power BI and Excel, developed by Microsoft, are robust data analysis and visualization tools.

Excel, one of the most widely utilized software applications worldwide, is mainly known for its data transformation, and analysis capabilities. Its features, such as formulas, pivot tables, and various data sorting and filtering options, make it ideal for managing and analyzing large data sets.

Power BI Vs. Excel

Moreover, Excel is often used for budgeting, financial analysis, and project management tasks. However, Excel falls short in dealing with complex data structures and creating interactive visualizations.

On the other hand, Power BI is a business analytics tool suite that allows for interactive visualizations with self-service business intelligence capabilities. It enables users to create reports with drill-down features and real-time data.

With Power BI, users can create sophisticated data models and visualizations, which are not possible with Excel. It also offers seamless integration with other Microsoft products and cloud-based data sharing, making it a superior tool for team collaboration on data-driven projects.