fbpx

Introduction to Power BI – What is it, how to get it, how to create reports with Power BI and how to publish them?

Share

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn

Power BI is a data analytics & visualization software. It is one of the most popular and powerful way to work with complex business data. In this page, you will find a comprehensive guide to start your Power BI journey.

Introduction to Power BI
why use powerbi

Getting Started with Power BI – Table of contents

What is Power BI?

Power BI is a software to create & publish reports and data stories from your data-sets. You can make highly interactive, engaging and powerful reports, dashboards or visuals with Power BI. You can connect to any data (Excel files, SQL databases, BI warehouses, Cloud data, APIs, web pages and more), mashup the data, link one table with others, create clickable visualizations and then share them with your audience securely thru Power BI. 

Who should use Power BI?

If your job / business or life depends on data, then you can use Power BI. There are two kinds of users for Power BI – Creators & Consumers.

Creators are people who make stuff in Power BI.

Consumers are people who read / view things built in Power BI.

Power BI Creators are typically:

  • Reporting professionals
  • Analysts
  • BI Developers
  • Visualization Specialists
  • Story tellers / Presenters

On the other-hand, almost anyone can be a Power BI consumer. 

How is Power BI different from Excel?

So what, even Excel can create interactive reports. But there are several crucial differences between Power BI and Excel.

  • Power BI allows rich, immersive and interactive experiences out-of-box. You can click on a bar in bar chart & other visuals respond to the event and highlight or filter relevant data. You can show graphs & visuals that are very tricky (or impossible) to reproduce in Excel like maps, pictures and custom visuals.
  • Power BI works with large data sets There is no artificial limit of 1mn rows in Power BI. You can hookup to a business data set and analyze any volume of data. The limit depends on what your computer (or Power BI server) can process.
  • Share and read reports easily You can create reports in Power BI and share them in formats that are universal (i.e. browser pages or apps). This means, your boss need not have Excel or Power BI installed to enjoy the beautiful reports you create.
  • Power BI is for story telling while Excel is for almost anything. We can use Excel to simulate pendulum motion, calculate Venus orbit, model a start-up business plan or many other things. Power BI is mainly for data analysis & story telling. If you try to replicate a large, intricate financial model or optimization problem with Power BI, you will either fail or suffer miserably. On the other-hand, if you use Power BI for making reports, running cool analysis algorithms (clustering, outlier detection, geo-spatial patterns etc.) you will wow your colleagues and bosses.

How to get Power BI Software?

Power BI Desktop software is free to download. Just head to Microsoft Power BI website and download the version for your computer.

Things to keep in mind when downloading and installing Power BI:

  • Power BI is always changing. Almost every month, Microsoft releases a new version of the software. One simple way to stay on top is to install Power BI thru Microsoft Store (on Windows 10). This way, your computer will automatically update the software whenever there is a new version.
  • You do not need PowerBI.com account to use Power BI Desktop. While Power BI may prompt you to login, you can use the software without registering for the online account. However, you can sign up for free PowerBI.com account. 
  • Login to publish & share your work. Although you can use PowerBI without logging in, you must log-in if you want to publish or share your reports with others.

Building your first report in Power BI - Tutorial

Okay, so you have downloaded Power BI and eager to play with it. Here is a step by step tutorial to help you.

We will use sample employee data for this process. Click on below button to download it.

#1 - Understanding Power BI Desktop UI

Open Power BI Desktop application. After you exit the welcome splash screen, you will see the blank Power BI application. Let’s understand this screen. Here is an illustration explaining 11 important features / buttons in Power BI Desktop.

11 Important features of Power BI Desktop UI

  1. Ribbon. Find most important and regular stuff in Home ribbon. Navigate to other ribbons for specific functionality.
  2. Get Data. Use this button to get data from almost anywhere – Excel files, websites, databases, APIs etc.
  3. View selection, by default you will be on Report view. Change to data or model view to see behind scenes.
  4. Fields Access the tables and fields (columns) of your data here. Use them in visuals (5) or filters (7) etc.
  5. Visualizations add charts, tables, maps, filters etc to the report from here.
  6. Visual Fields, Format and analytics use this area to set up and customize your visualizations (charts etc.) Note the paint-roller, use it to edit colors, fonts, settings etc.
  7. Filters – set up chart, page, report level filters here. Anything you restrict will be removed from all the linked items.
  8. Canvas this is where you construct your reports.
  9. Save your Power BI reports by pressing CTRL+S or clicking on this button. They will be saved as PBIX files.
  10. Publish the reports with this. You can publish them to online (either free PowerBI.com account or paid plans) so that others can access your reports.
  11. Add more pages to your report using the + button.

#2 - Load data into Power BI

  • Click on Get Data button. 
  • Select “Excel” as as source.
  • Point to the downloaded sample data.
  • Select “Table1” in the navigator screen and click on “Load” button.
  • Done, your data is loaded. 

#3 - Adding Visuals

Working in Power BI feels like playing with your data. This is because of the drag-and-drop nature of report building process. To add a visual,

  1. Click on the type of visual you want. 
  2. A blank visual will be added to available empty space on your report canvas.
  3. Select fields from your data and add them to relevant places.
    1. Axis 
    2. Values
    3. optionally legend 

See this demo to understand the process.

 

How Power BI visualizations are different...

  • Power BI visualizations are always interactive
  • They are sorted by default (for ex: descending order for column charts)
  • Value field will be the number of chart. You can change the calculation to SUM / COUNT / AVERAGE etc.
  • You can even use DAX Measures in the value area of charts
  • All visualizations support extra tool-tips (both simple and report-page type tooltips)
  • Use Legend field (where available) to see 2nd level detail.
  • When you add multiple fields to axis, Power BI adds drill-down buttons to see chart at various levels

#4 - Changing Calculations for the Visuals

You can use two methods to change the calculations for the charts. 

  • Use default options for calculations – SUM / COUNT / AVERAGE etc.
  • Write your own calculations with Power Pivot measures

To change the calculation of a chart with default options, follow below steps.

  1. Select the power BI visual
  2. Go to Value field.
  3. Click on the little down arrow symbol.
  4. Select the type of calculation you want.
  5. Done.

Here is a quick demo of changing chart calculations in Power BI. It shows how to change the chart from SUM of salary to AVERAGE of salary.

how to change calculations in Power BI

#5 - Understanding Power BI Interactions

Power BI visuals are interactive. This means, if you have more than one chart on a report page, when you click on a particular item on a chart, all other charts respond to the selection and change. 

This is quite different from normal Excel charts, but once you get used to it, you will see the true power of Power BI visualizations.

Here is a quick demonstration of Power BI visual interactions.

power bi interactions - demo

Power BI interactions - FAQs

Here are some of the common questions you may have about Power BI report interactions.

Are all visuals interactive?

By default, all visualizations in Power BI report are interactive. The only exception is card visuals. They are not interactive. So if you click on them, nothing happens to other charts.

How to unselect ?

Simple, click or touch the selected item again. The interaction will be gone.

How to disable or change interactions?

Select any visual, go to Format ribbon. Now click on “Edit interactions” button. This will show interaction buttons on top of all your visuals. Click on do not interact button (looks like no entry sign 🚫).

You need to do this for each visual.

I want to filter instead of highlight on interaction…

You can use the “Edit interactions” button to change the style of interaction. There are three possible interactions (as depicted to the right). power bi report interactions

  1. Filter
  2. Highlight
  3. No interaction (no entry sign – 🚫)

#6 - Putting it all together - Making a sample report in Power BI

Now that you have some understanding of Power BI, let’s create our first Power BI report. The focus of this report will be,

  • For a specific manager
  • Show staff distribution by department
  • Gender break-down
  • All of their staff by salary and rating

This is a fairly simple report, but it does demonstrate the power, elegance and ease of working with Power BI.

Here is the final output we will create.

demo-first-power-bi-report

Step by step instructions for our first Power BI report:

  1. Load the employee data into Power BI. (here is the file).
  2. Add a column chart.
    1. Department on axis
    2. Name (count of name) in values area
  3. Add pie chart
    1. Gender as Legend
    2. Name (count of name) in values area
  4. Add a table with name, age, rating and salary fields
  5. Add a slicer with manager as field

Our report is almost ready.

Adding conditional formatting

Background colors on rating field:

Select the table. Click on down arrow symbol next to Rating and apply conditional formatting > Background color. Set up a color scale as shown below.

background color-in-powerbi-conditional-formatting

Data bar for Salary:

Click on conditional formatting for salary, set up data bars as shown below.

databars-powerbi-tables

Adding title for the report

From Home ribbon in Power BI, click on Text box and type your report title in that. Format the text and position it on the top. 

That is all, your first Power BI report is ready. 

Play with slicer or charts to see powerful insights from this report.

#7 - Saving & Publishing your Report

To save your Power BI report, press CTRL+S. This will save a copy of your report on your computer. Power BI files use the .PBIX as extension.

How to share / publish your reports?

You can publish your reports in various methods.

  • Email or share the file: this is the simplest method. Just email or share the file with your audience. They will need Power BI Desktop to view the reports though. Also, they will need to access the source data sets to be able to refresh or update the reports.
  • Publish to Power BI online: This is the recommended way to sharing your reports. But you do need PowerBI account (either paid or free) to be able to publish the files to online workspace. Once you publish your reports to the workspace, you can invite others to view them or pin parts of it to a dashboard etc.
  • Share to Mobile / Tablet via Power BI app: Once you publish the reports to Power BI workspace, others can view the reports on web or on mobile / tablet apps by accessing the workspace.

#8 - Updating & Refreshing your Report

With Power BI, you create once, use forever. As your business data changes, all you need to do is, refresh the report. This will automatically fetch any new data from your source, update all calculations and visuals. If you publish the report again, this will replace the online version with new one so your colleagues or clients can access updated reports easily.

What if your data format changes?

For example, if you add new columns or rename things, then you may need to rebuild some visuals or calculations. You will notice any broken items upon refresh and you can easily fix them.

When to use each of the Power BI visualizations?

As of November 2019, Power BI has got 34 default visualizations. You can also install any of the 100s of custom visualizations from Power BI marketplace. All of this can be overwhelming. So I made this handy illustration and check-list to help you decide the right visualization for any situation.

 

Picking right visualization for your situations...

  • 2 items to compare: Use two cards or KPI visualization.
  • Up to 12 items: use column or bar charts
  • More than 12 items: Use tables with conditional formatting. Try column / bar charts with Top N filter if you want to see top few items.

Use line charts or area charts with time on x axis with oldest time to left.

If you have just two points in time and want to compare the changes, consider the waterfall chart.

You can also toy with ribbon chart to see if that provides any valuable insights.

Use 100% stacked bar / column charts, area charts.

When you have few values to see the composition, use pie chart or donut chart. 

As Power BI is interactive, you can getaway with having too many (but definitely not 100s) of slices in your pie / donut charts.

When you have lots of values with few clear outliers, try the tree-map visualization.

Use Maps for geographical distribution of data.

  • Bubble map: when you have data about specific locations
  • Filled map: when you have data for regions
  • ESRI maps: for maps with additional reference layers (say population, crime rate, GDP etc.)

For distribution by floor / store shelves or something else, use shape maps.

For one-dimensional distribution, use line or area charts

For two-dimensions, use scatter plot

More than two-dimensions, you may want to avoid the visualizations altogether as they can be quite hard to explore. If you must, try a table.

Use column, bar or table charts with conditional formatting to bring out the differences. 

You can also try the key influencer visualization to let Power BI analyze your data and tell you what is the most significant item impacting outputs.

Use card visualizations or KPI visualization.

Video Tutorial - Getting Started with Power BI

Here is a complete tutorial on how to start from zero and create a report with Power BI. Please watch it to learn what Power BI is, how various components of the technology (Power Query, Power Pivot and Visualizations) relate to each other and how to work with the software for the first time.

Download the Getting Started Files

getting started with power bi - v2 reportPlease use below links to download the files.

Power BI - Glossary

Power BI is a new and rapidly evolving technology. There are a lot of technical terms you will hear as you start using Power BI more. Here is my list of top 10 Power BI glossary. 

Power Query is the data processing engine for Power BI. Whenever you connect to a data source using “Get Data” button, you are using Power Query. This engine runs in the background to,

  • connect to data sources
  • gather data
  • change / pre-process data based on rules
  • combine multiple datasets to form one table (joins, appends)
  • publish finished datasets to Power BI for analytics and visualization.

You can use Power Query in Power BI or in Excel too.

Here is introduction to Power Query.

Power Pivot is a calculation engine for Power BI. You can use Power Pivot to model complex data, set up relationships between tables, calculate things to be show in value field area of tables or visuals.

Think of Power Pivot as a calculation layer between your data and outputs. You can tell Power Pivot how you want your calculations done thru a language called as DAX and Power Pivot can give the answers. It is an extremely fast & scalable software.

We can use Power Pivot in either Excel or Power BI.

Here are some links about Power Pivot. They explain it from Excel perspective, but the idea is same.

Introduction is Power Pivot

What is a measure and how to create one?

DAX stands for Data Analysis eXpressions. This is a language for calculating things with Power Pivot. 

DAX expressions or formulas look almost like Excel formulas. 

Example DAX formula:

Total Salary: =SUM(Table1[Salary])

Sums up Salary column in the Table1 and presents it wherever you use this [Total Salary] measure.

This is the language used by Power Query for processing your data. You almost never have to learn this.  This language also follows Excel formula style for writing expressions but does breakaway from the pattern quite a bit. Power Query is a step-by-step engine. Each time you do an operation (step) on your data, Power Query writes an instruction in M language. These steps (most often) refer to previous steps. As M is a very technical language, it is hard to explain it in one paragraph. I recommend Gil Raviv’s Collect, Combine, and Transform Data with Power Query book for any one interested.

Note: the link to Gil’s book uses my Amazon affiliate code.

You can link two tables based on a column. This is called relationship.

For example,

Say have two tables – Sales & Customers.

You can link Sales table and customer table based on Customer ID. We then say Sales & Customer tables are related.

It means, both columns have the same meaning. 

There are two kinds of relationships.

  1. One to many relationships: a value in one table is linked to one or more values in another table. Example: Customers to Sales relationship. Each customer appears once in Customers table but can have many matching transactions in Sales table.
  2. Many to many relationships: Each value in one table can be linked to one more more values in other table and vice-a-versa. For example: People and Projects. Each person can be part of any number of projects. Each project can have one or more persons.

Measure or calculations are what gets displayed in visuals / tables / cards. 

The Count of Name, Average Salary things we used earlier are measures.

There are three kinds of measures in Power BI.

  • Implicit measures: These are automatically created when you drop a filed in the “Value” area of a chart / visual. Example: Count of Name. 
  • Explicit measures:  These are the ones you create by using DAX language. Example: =SUM(Table1[Salary]) 
  • Quick measures: These are same as ‘explicit measures’ but instead of typing the DAX formula, you use Power BI quick measure feature to make them.

You can create measures by right clicking on a table (area 4 in the Power BI Desktop UI) or clicking on the “New Measure” button on the ribbon.

Imaging a big black box with all your tables and any relationships between them along with the measures you have defined. This black box is your data model.

Other common names for data model are Cube, tabular model or simply model.

what is data model - power bi

Interactions refer to the clicks and selections you make on the report to see data relevant for selection. 

Here is a quick demo of Power BI interactions.

power bi interactions - demo

While interactions allow you to peak at data for a point, Slicers & Filters allow you to restrict an entire report or visual(s) to use only some part of your data.

Power BI offers various levels of filtering.

  • Slicers: These are on-page filters. By default they interact with every visual on the page and update them whenever you change something. For example: Manager slicer in our Power BI report from above.
  • Visual level filter: This is a filter set on a specific visual. It will not impact other visuals on the page.
  • Page level filter: This will act on all the visuals on a page and restricts the data that is sent to them for calculation. 
  • Report level filter: This will impact all pages in a Power BI report.

Only slicers can be added to a report page. Other filters are set in “Filter Pane”.

Refresh refers to the concept of updating all the data, calculations and visuals based on source data changes. You can manually trigger refresh by clicking on the “Refresh” button in Power BI Desktop Home ribbon. 

You can also schedule refresh for online published reports so that every day (or whatever frequency you determine) Power BI online will refreshes your data and updates the published reports. 

How to learn Power BI - Resources

Here are my top recommendations for learning Power BI.

power bi websites

Power BI Websites

My top go-to Power BI websites are,

Books on Power BI

Good Power BI books are like hens teeth. As Power BI constantly evolving, it is very hard to write books on it. Heck, I find even writing this article is hard. That said, these are the books I own and recommend for learning Power BI and other elements of Power Platform (Power Query, Power Pivot)

Note: All these are Amazon affiliate links. I recommend these books because I find them immensely helpful.

Power BI Video Channels

The quickest and most fun way to learn Power BI is by watching videos. I subscribe to a handful of channels to stay on top of Power BI developments, news, tips and ideas.

Power BI online course

Power BI Courses

Power BI is vast, technical and often confusing. If you are finding the journey too hard, consider an online course.

I recommend my own online class – Power BI Play Date.  Next round of enrollments begin in late November. 

Latest Power BI, PQ and PP Articles on Chandoo.org

key-influencer-chart-in-excel-header

How to fake “Key influencers chart” in Excel?

Recently, Microsoft Power BI introduced a very useful visualization, called key influencers visualization. As the name suggests, this is a chart of key parameters that effect a measure or outcome.

For example, you have customer satisfaction rating as a measure. Now you want to know which aspects of your data impact the ratings most? You can create the key influencer visual and Power BI finds all the top ranking influencers (using rules and machine learning).

But can we make it in Excel?

Let’s see…

Read More »

Epic Introduction to Power BI

Yay, finally our first ever full length Power BI tutorial is up. In this 30 minute video tutorial, learn how to get some data in to Power BI and make a quick report to understand it. We will be creating this.

customer-service-db-pbi-v1

Let’s get started then.

Read More »

How windy is Wellington? – Using Power Query to gather wind data from web

Let’s take a whirlwind trip to coolest little capital – Wellington. It is a windy place, so hold on to your hats and spreadsheets.

Almost everyone who spends more than 2 days in Wellington would agree that it is a windy place. But how windy is Welly? In this two part series, we will use Power Query, Excel charts and coffee to answer that question.

But, first let’s start with a joke.

What happens when you throw a boomerang in Frank Kitts Park?

You will have to buy another one, coz you are not getting that one back.

Read More »

Nest Egg Calculator using Power BI

Welcome to Power Mondays. Every Monday, learn all about Power BI, Power Query & Power Pivot in full length examples, videos or tips. In the first installment, let’s take a look at something we all can related to – Money. 

We all know that Power BI is good for creating awesome visual experiences. Today let me share another fun way to use Power BI – to build a calculator. Learn how to create nest egg calculator in this Power BI parameter example tutorial.

Read More »

Closing Remarks on Power BI

Power BI is one of the most fun and elegant ways to work on complex data sets for analytics or reporting needs. I encourage you to learn it so that you can ahead in your work. It is both deceptively simple and inherently complex software. That means, just like Excel, almost anyone can pick up Power BI and start building things immediately. But if you know which buttons to press and what formulas to write, you can unravel a marvelous world of data analytics with Power BI.

I wish you all the best in this journey. 👍

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Chandoo is an awesome teacher
5/5

– Jason

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

Celebrate with spider web chart [Halloween Fun]

It’s Halloween time. As adults, we can’t go trick or treating. We can of course dress up in costumes and entertain others. But what about the poor spreadsheets. Don’t they deserve some of this fun too?

Hell yeah! So I made a spider web generator in Excel. Just use it to make a spooky cob web pattern and add it to your report / dashboard / time sheet or whatever else. Surprise your colleagues.

3 Responses to “Introduction to Power BI – What is it, how to get it, how to create reports with Power BI and how to publish them?”

  1. S1 says:

    Hi!

    Thanks for your offerings.

    on powerBI have a question, I am not skilled in writing formulas or coding in VBA - can I create meaningful reports my job requires me to make reports all the time.

    IAM comfortable with simple pivot tables in Excel and vlookup formula. Even IF formula more than one condition I struggle.

    How can I begin with power BI ? Is power BI not suitable for me with current skill level?

    Thanks!

  2. Nisha Batel says:

    Thanks for such a useful post. It will surely help to understand PowerBI.

  3. Narayan Rao says:

    Hi ,

    Thanks for such a comprehensive introduction to a fascinating product.

    Hope you will follow this up with more.

Leave a Reply