fbpx
Search
Close this search box.

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

Share

Facebook
Twitter
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...

#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

Top 5 HR Analytics Examples – Free Video Masterclass

I recently finished a long consulting gig with one of the government ministries in New Zealand. Guess what I was doing? HR Analytics and Reporting. In this post, I want to share my top 5 Excel tips for HR people, based on what I learned in the last 18 months.

Specifically, we will cover:

  • Gathering and structuring Employee data in Excel
    • How to use Power Query to collect data
    • Polish / clean data in Power Query
    • Bring cleaner data to Excel as refreshable table
  • Answering questions about employees
    • Using Excel formulas such as COUNTIFS, SUMIFS, AVERAGEIFS
    • Pivot tables for data analysis
    • Understanding the results quickly with conditional formatting
  • Understanding pay gap
    • Calculating gender pay gap
    • Visualize pay gap
  • Creating salary distribution charts
    • Working with histogram charts in Excel 2016 / Office 365
    • Making interactive charts
  • Generating letters thru mail merge
    • Calculating employee bonus based on bonus mapping logic
    • Creating 100s of letters with a single click using Mail Merge + Word

Sounds interesting? Read on for details.

Read More »

Convert unevenly spaced list to table [Data from Hell]

Introducing Data from Hell:

Watch out, its data from hell. In this new video series, we are going to examine some nutty, frustrating and fun data reshaping challenges and solve them using Excel. We will use Power Query, Formulas, VBA or other features as needed to free this data from damnation.

For our first installment, let’s reshape unevenly spaced list of values to a table.

Read More »

Employee Turnover Dashboard – Power BI for HR

Work in HR and use Power BI? You are going to love this extensive, powerful and useful Employee Turnover Dashboard. In this detailed article, learn how to create your own attrition dashboard system with Power BI. Full example workbook, video and sample data included.

Read More »
make a list of numbers in Power Query

Quick tip: Make a list of numbers (or dates) in Power Query easily

Just a quick tip to revive the blog from a month long silence. I am alive and kicking. I have been occupied with a quest to rescue princess & maidens on video game console. Recently we bought SNES classic console from Nintendo and I have been playing Legend of Zelda – a link to past regularly. As it is almost summer, I am also enjoying the beautiful outdoors in Wellington. All this means, little time for blogging. I will try to post a few more times before the end of year.

Make a list of numbers in a jiffy with Power Query:

We know that in Excel, you can type a few numbers and use the fill handle to fill down (or up etc.) numbers as you want.

But what if you need some numbers in Power Query?

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. 👍

Facebook
Twitter
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

Excel School made me great at work.
5/5

– Brenda

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.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

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.

Weighted Average in Excel with Percentage Weights

Weighted Average in Excel [Formulas]

Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.

What is weighted average?

Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

14 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!

    • Chihiro says:

      Think of PowerBI as completely separate from Excel. If you try to understand PowerBI's DAX formula (or M query language for that matter), based on traditional Excel formula. You will have difficulty understanding it.

      Unlike Excel formula, DAX are highly contextual and can give different answer based on how data is shaped/filtered/sliced.

      In my opinion, it's best to approach it from SSAS perspective or as completely new tool for business intelligence.

      Suitability will depend on your commitment level to learning a new tool.

  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.

  4. Nick Partridge says:

    One question that comes from dealing with a data set that may be representative of the real world.

    How can I edit the data set? - Is it possible to carry our minor edits within Power BI - example, the data set has two Millie Hanway's - they get lumped together so sliding in a middle initial to one or the other is a minor edit. Without this, they skew the output for Salary, Age, Rating etc.

    Similarly the null genders - did Madge and Benny actively reject their assigned genders or is it a couple of data entry issues from when they were hired? If I was in the company I'd know and could quickly fix it in line with their identity.

    Do I have to go back to the source or can I carry out simple edits in Power BI / Data?

  5. Nick Partridge says:

    Watched the video which addresses the gender issue but did not touch the 2 Millie Hanway's question.

    Thanks

    • Chandoo says:

      @Nick... good points
      Power BI doesn't allow you to make ad-hoc edits to data. Such changes should be done at source as a best practice.
      For rule based changes (ie remove duplicates or replace gender value or such) you can use Power Query to do that. For example, to remove the 2nd Millie, you can edit the query, select the name column and right click on it to "remove duplicates".

      I hope this helps.

  6. PUNIT SEJPAL says:

    Hi

    I want to avail combo package of excel, power BI, VBa, dashboards. How can i ?

    Also any customer care contact /whatsapp.

    Punit.
    + 971 55714 9225
    capunit2005@gmail.com

  7. Pran says:

    I want to learn PowerBI from you.

  8. Shilpa says:

    I am trying to prepare a Power BI report to automate monthly billing of my team. It's like stopping me to get the exact result what I am trying to get. There is difference of price. It would be great if you can help me with that

  9. Vismita AS says:

    Hello Chandoo,

    Could you please let me know tentative date for starting the next batch for Power BI online class, so that I can plan my schedule accordingly.

    Thank you.

  10. Mohammad Arshad says:

    Dear Chandoo,

    i want to join your power BI classes.

    Kindly inbox me the details.

  11. Carlos says:

    Thank you so much Chandoo, I follow your website from many years and I use Excel a lot, I really appreciater your valuable support to move into the undiscovered lands of Power BI.

    I still feel safer with the Excel do-it-all philosophy, but the society is moving and we cannot stay behind.

    Regards
    Carlos from Chile, South America.

  12. Harika says:

    Hello Chandoo,

    I am looking for advanced Power BI topics like complex data modelling Techniques, using statistics in power BI to find Frequency, Regression, Correlation

    Using Linear regression and predict the trend something which amazes my manager which solve complex Business problems.

    will this be covered or please suggest me best places to learn these techniques.

Leave a Reply