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.
Getting Started with Power BI – Table of contents
- What is Power BI?
- Who should use Power BI?
- How to get it?
- Building your first report with Power BI – Tutorial
- Understanding Power BI Desktop UI
- Loading the sample data
- Adding visuals
- Changing calculations
- Understanding interactions
- Putting it all together – Making a sample report
- Saving & Publishing your report
- Updating / Refreshing the report
- When to use each of the Power BI visualizations?
- Video tutorial – Introduction to Power BI
- Downloads – Sample data, completed report
- Glossary of terms
- Power Query
- Power Pivot
- DAX
- M Language
- Data model
- Relationships
- Measures
- Interactions
- Filters, Slicers
- Refresh
- Resources to learn Power BI
- Websites
- Books
- Video Channels
- Online Courses
- Latest on Power BI, PQ and PP from Chandoo
- Closing Remarks on Power BI
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
- Ribbon. Find most important and regular stuff in Home ribbon. Navigate to other ribbons for specific functionality.
- Get Data. Use this button to get data from almost anywhere – Excel files, websites, databases, APIs etc.
- View selection, by default you will be on Report view. Change to data or model view to see behind scenes.
- Fields Access the tables and fields (columns) of your data here. Use them in visuals (5) or filters (7) etc.
- Visualizations add charts, tables, maps, filters etc to the report from here.
- 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.
- Filters – set up chart, page, report level filters here. Anything you restrict will be removed from all the linked items.
- Canvas this is where you construct your reports.
- Save your Power BI reports by pressing CTRL+S or clicking on this button. They will be saved as PBIX files.
- 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.
- 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,
- Click on the type of visual you want.
- A blank visual will be added to available empty space on your report canvas.
- Select fields from your data and add them to relevant places.
- Axis
- Values
- 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.
- Select the power BI visual
- Go to Value field.
- Click on the little down arrow symbol.
- Select the type of calculation you want.
- 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.
#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 - 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). 
- Filter
- Highlight
- 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.
Step by step instructions for our first Power BI report:
- Load the employee data into Power BI. (here is the file).
- Add a column chart.
- Department on axis
- Name (count of name) in values area
- Add pie chart
- Gender as Legend
- Name (count of name) in values area
- Add a table with name, age, rating and salary fields
- 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.
Data bar for Salary:
Click on conditional formatting for salary, set up data bars as shown below.
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...
Compare one with another
- 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.
Trends, changes over time
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.
Composition, how things add-up?
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.
Spacial distribution
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.
Distribution
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.
Outliers, what is different?
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.
Simple numbers, messages and indicators
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
Please 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
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
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.
DAX
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.
M Language
Note: the link to Gil’s book uses my Amazon affiliate code.
Relationships
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.
- 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.
- 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.
Measures
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.
Data model
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.
Interactions
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.
Filters & Slicers
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
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
My top go-to Power BI websites are,
- Microsoft Power BI blog for new updates, announcements, tips and ideas.
- Matt Allington’s blog to polish my Power Pivot skills
- Radacad by Reza Rad for amazing case studies and ideas on Power BI, Azure ML etc.
- Enterprise DNA
- Kasper on BI
- Ken Puls for Power Query to get advanced skills in PQ
- Rob Collie for Power Pivot
- Crossjoin by Chris Webb
Books on Power BI
- Mastering Power BI by Brett Powell
- Supercharge Power BI with Power Pivot by Matt Allington
- Collect, combine and Transform Data with Power Query by Gil Raviv
- Power Pivot and Power BI by Rob Collie and Avi Singh
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.
- Microsoft Power BI official channel: this is the pace to go for monthly update news, community webinars and more.
- Guy in a cube: for videos on Power BI tricks, interviews and more
- Curbal: for interesting tricks, DAX and more
- My own channel: If you haven’t already subscribed to it. For all things Excel and Power BI.
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
D’oh – Visualizing Homer’s favorite sayings in Power BI
Before we begin:
Today is the last day for enrolling in our Power BI Play Date. Don’t miss out on this amazing opportunity to learn, use and benefit from Power BI at your work. Check out my online class and sign up before the doors close at midnight. Click here.
Let’s get our Simpsons on then.
D’oh, How often Homer says his favorite things?
Here is the visualization to explore Homer’s (and other character’s) favorite sayings in 27 years worth of Simpsons episode. Click on the image to play.
How to “auto” generate calendar tables with Power Query – The best method
Calendar (or date) table is crucial for performing date intelligence calculations in Power BI.
Normally, you would find a reasonable calendar table in most data models. But occasionally I come across models where there is no calendar table.
So I present to you, the ultimate & best way to generate calendar table using Power Query.
A trick to Pivot text values
We all know that Pivot Tables are best thing since avocado on toast. But they can’t slice text values and spread them in a table with Pivots. So how to take a large blob of text and turn it in to something meaningful like above?
Simple, we use Power Query.
Extract data from PDF to Excel – Step by Step Tutorial
Recently I had to extract data from multiple credit card statements and combine them to one Excel table. In this tutorial, let me share you simple steps to deal with PDF data and extracting it to Excel.
How many people used their entire sick leave entitlement? [Power Query / Excel homework]
Imagine you are the HR analyst at BigLargeCompany. You are asked to find out whether staff at BLC (BigLargeCompany you silly) use up their full sick leave entitlement.
You have two tables – emps & leaves as illustrated below.
Your mission is to find out answers to below questions.
- How many employees used exactly 100% of their entitled sick leave?
- How many employees did not take any sick leaves?
- Listing of all employees who used 100% of their entitlement
Use either Power Query, Excel formulas or any other technique to answer the questions.
Getting Started with Power BI – Sales Dashboard in one hour
Learn Power BI and create your own Sales Dashboard with this FREE Masterclass. Details inside.
How to get a random sample of data with Power Query
Today’s Power Monday trick is about Power Query. This is based on my experience of working with large volumes of data.
Today I have been building a hotel dashboard (more on this later). As part of the dashboard, I wanted to show a random sample of user reviews. Reviews database had quite a few rows, so I wanted to extract a randomized sample of 100 reviews and show them in the report. When you refresh the report (Data > Refresh), then a new set of reviews will be fetched and shown.
Let’s learn how to generate a random sample with Power Query in this article.
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. 👍
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?”
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!
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.
Thanks for such a useful post. It will surely help to understand PowerBI.
Hi ,
Thanks for such a comprehensive introduction to a fascinating product.
Hope you will follow this up with more.
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?
Watched the video which addresses the gender issue but did not touch the 2 Millie Hanway's question.
Thanks
@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.
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
I want to learn PowerBI from you.
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
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.
Dear Chandoo,
i want to join your power BI classes.
Kindly inbox me the details.
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.
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.