Power BI the market leader in solving data management crisis.
This tool is mainly aimed to help organizations and individuals to visualize and organize their data.
Hi everyone, I welcome you to this video.
session on Power BI course which contains everything that you need to know in order to master Power BI.
Now before we move any further, let's take a look at the agenda for today.
The first module which is an introduction to Power BI will help you understand the importance of BI,
the different tools that are there and why you should go for Power BI.
It also covers the basic fundamentals of Power BI.
The second module which is Power BI Desktop,
here you learn how to install and download the tool and you will also get familiar with the UI of the tool.
The third module which is Power BI Charts will help you understand how to create impactful
and comprehensive reports
on The next module which is Power BI KPI Indicators will help you understand the importance of KPI visualization
and how they can benefit an organization in visualizing their growth.
The next module which is Power BI Dashboards will help you understand how to create interactive dashboards
with the help of lot of examples and use cases.
Followed by this we have a comparison module between Power BI and Tableau.
In this module you will understand the important features of both of these powerful tools.
Followed by this we have the last module which is Power BI interview questions.
Here you will understand the important concepts of Power BI and how you can ace a Power BI interview.
We'll also look at a couple of market trends of Power BI.
So guys before we move any further make sure you go ahead and subscribe to EDU data YouTube
channel in order to stay updated about the most trending technologies.
The concept of business intelligence is something that is alien to very few people these days.
With newer tools emerging every day to help solve the crisis of data management,
most organizations have already moved in or have plans to use business intelligence in solving their crisis.
And in this module we are going to talk all about power BI power BI is Microsoft's latest
BI tool mainly aimed to help everyone analyze and visualize their data.
So without much ado, let's get started.
Now why should you?
Before I answer this question, I would like to answer something a little more fundamental.
Let's start by addressing the most essential and fundamental question.
What exactly is business intelligence?
Now, in an age where business intelligence has become a bigger domain than most trending technologies,
if you ask 20 different people what the term means, you're likely to get at least 10 different answers.
So let me try and put it in the simplest of terms without having to lose the technical.
Business Intelligence is a set of techniques and tools for the transformation of your raw data,
meaning your data sets into meaningful and useful information to take important business decisions.
To put it simply,
business intelligence is the technology would get the right data to the right people at the right time so that they can take more effective business decisions.
Over the years,
the process of business intelligence has grown and adapted to help solve almost all the challenges while dealing with data by involving newer tools and techniques.
The change that business intelligence has seen over years can be divided into three waves.
So, let us continue with our tutorial and take a look at the first wave, IT to end user or the technical wave.
During the first wave...
the end user had to be dependent on the IT department for data inside.
This is because it was not possible for end users to create visualizations or reports on their own as tools
available required some technical or coding knowledge.
This dependence on the IT department for insights resulted in more efforts and time consumption to get the updates done.
Now the second wave, which is analysts to end user or the self service Now the second wave gave analysts access to BI.
Now people with some knowledge of analytics could use BI tools.
This meant more teams had the access to BI and more people could have better data insights.
This is the role of the IT teams.
And finally the third wave, which meant everywhere.
one, which means the power lied in the hands of the end user.
The third wave made it easier to access data and create reports and visuals to get a better business insights.
The introduction of tools like power BI, Tableau, click view and spot fire made this transition easy.
Now anybody who had a basic understanding of data could create reports to build intuitive and shared.
This was about the three waves of BI and in the third view came a very important aspect data visualization.
Now data visualization is nothing but the pictorial or graphical representation of information.
data.
It provides insights into complex datasets by communicating the key aspects in a more intuitive and meaningful way.
Data visualization lies at the intersection of design, communication and information.
Even though your data visualization has been termed as the key skill for research in the 21st century, it goes way, way back.
It existed in the late 18th century and can be traced back to when William Playfair invented the geometrical charts.
His bar charts were used to represent Scotland's imports and exports of 17 countries in 1781.
These bar charts constituted a pure solution to the problem of the quantitative comparison.
Now, obviously, we have grown to learn about more and more charts as the years pass by.
We also have a few hybrid charts to make our jobs easier and our calculations more granular.
The way our human brain processes the information is easier to use images,
charts and graphs to understand and visualize large amounts of complex data, then going through tons and tons of spreadsheets on reports.
take the quote and image is worth a thousand words.
For example, this is completely true because as a human mind images aren't just a mere collection of pixels.
They hold a lot of information.
This information in visual form is easy to understand then reading the same facts in text or number form.
So let me give you an example.
Suppose there's a company which deals with a lot of products like Amazon.
on and it's widespread over the world and has many many vendors that sell through this platform.
So obviously there's a lot of data being generated in a lot of different formats people use Excel
access different databases sequel server so on and so forth.
Some people even make all these spreadsheets
So obviously all that has to be bought to a single platform to analyze and then
these are the reports that go to the CEO's CFOs and the CXO position all the big people Now,
you can't just take 1 million or 10 million rows of data to a person to look at and infer something, right?
You need them to know what's going on,
what is going on inside the company,
how is the market outside of the company and this cannot be done with hundreds and thousands of numbers.
This is where data visualization comes into picture.
It's about giving them an idea of what's going on
inside their company in different departments without having to look at tons and tons of numbers.
Well-designed graphics have the power to put this complicated data into simple pictures.
And this is where modern BI wins.
Data visualization is a quick and easy way to convey concepts.
in a universal manner it can help to one identify key areas and hidden patterns to get factors
that give better customer insights three analyze and associate data and products properly and finally make proper predictions And obviously,
presentation has a big role to play in this.
So pardon me for my analogy, but if this was a beauty contest, let's go ahead and look at our winner.
Now let's see why we need Power BI.
Now there are a few points that make Power BI one of the most prominent tools for data visualization.
Now this tutorial would be incomplete without understanding these points.
Firstly, Power BI can spot trends in the Traditional BI tools like Tableau or ClickView restrict you to historical analysis.
By using Power BI, you can access real-time information so you can identify trends early.
By doing so, you can identify issues and improve performance.
Secondly, Power BI can automatically search hidden insights.
With Power BI, you can auto-search datasets for hidden insights in seconds with quick insights.
Users can simply ask questions and Power BI Q&A will answer their questions with an immediate effect.
Third, advanced analytics and custom visualizations.
With custom visuals, Power BI allows you to visualize data in almost every way possible.
As long as you can imagine it, you can put it on your data.
Thus, you're not limited to something that lies in a box.
Finally, Power BI is Enterprise Red.
With Power BI and Power BI Desktop, you can securely connect to your own on-premises data sources.
With on-premises Data Gateway, you can connect live to your SQL Server and other data sources.
It gives you a secure, scalable, and reliable enterprise-grade information technology.
And mentioned reasons make Power BI very important in context of data visualization.
So, who can you...
IT professionals, developers, companies big and small, subject matter experts and plane analytics enthusiasts.
As long as you want to, you can all use Power BI.
Now let's continue to understand this by knowing what is Power BI.
The power BI as a name has been in the BI market for quite a long time.
The Microsoft team has worked for a long time to build a big umbrella called power BI.
And this umbrella is a combination of strong visualization, data analysis, data sharing, aggregating and cloud sharing.
To define it, Power BI is a business analytics service provided by Microsoft.
It provides interactive visualization with self-service business intelligence capabilities
where end-users can create reports and dashboards by themselves without having to depend on information technology staff or database administrators.
It also gives you cloud.
services known as Power BI services along with a desktop based inference called Power BI Desktop.
It offers data warehouse capabilities using data prep, data discovery and interactive dashboards.
In March of 2016,
Microsoft released an additional service called Power BI embedded on its Azure Cloud platform,
which enables the user to analyze data easily and perform various ETL operations and deliver reports with.
The Power BI gateways let you connect with access,
Excel, SQL databases, analytics and many other sources to your dashboard in Power BI and reporting portals,
embed Power BI reports and dashboards to give you a unified experience.
What you see on your screens right now shows Power BI's general work.
flow.
You have a thousands of data sources which are being connected to Power BI Desktop,
which then can be published into the service and gives you an option of connecting your organizational data live through your Power BI.
In the end,
this can all be accessed through your tablets, laptops, and cell phones with you, your colleagues, and everybody involved in your business decision.
Now that you understand what Power BI is, let's go ahead and look at a few of its benefits and why are we using this.
Firstly, it has pre-built dashboards and reports for popular software as a service solution.
Power BI helps you create powerful visualizations in the form of dashboards and reports and you can do them without any technical knowledge at all.
All you need to do is have a little bit of analytical sense and you can use this service
Next it has real time dashboard updates as I had mentioned before power BI works real time and it can forecast trends in the coming few years as well.
Third secure life connection to your data sources on premises and in the cloud.
through the Power BI Gateway you can establish connections that are secure and your organizational
data can be connected to live every time that you want to.
The best part about this is it is scalable and very, very simple.
4.
Power BI also provides you intuitive data exploration using natural language query.
You not have to know the query language to explore your data in Power BI.
Just using your everyday English or your natural language, data exploration can be made possible.
5th integration with familiar Microsoft products to utilize commitment for scale.
Power BI can integrate with a number of sources and a number of Microsoft products, which makes it highly scalable compared to other BI tools.
And finally, immediate deployment.
Power BI is known for its quick deployment, which makes your job quick as well as easy when you have to take critical business decisions.
And that was all about Power BI.
In the section ahead, we are going to components of Power BI.
Now, Power BI has a few components.
You have Power Query, Power Pivot, Power View, Power Map, Data Catalog, Data Management Gateway, Power BI Q&A, and Service.
Starting up, we have Power Query.
Now this is a component which can be used to search and access and transform public and internal data source.
uses.
It is the Microsoft's data connectivity and data preparation technology,
which enables business users to seamlessly access data stored in hundreds of data sources and reshape
it to fit their needs with an easy to use, engaging and no code user experience.
Next we have.
it.
Now you can use this for data modeling for in-memory analytics.
It extends a local instance of Microsoft analysis services tabular that is embedded directly into your workbook.
It enables you to import millions of rows of data from multiple data sources into a single Power BI workbook.
It helps you create relationships between heterogeneous data,
create calculated columns and measures using formulas and build pivot tables and pivot charts and further analyze the data.
Then you have power view which is a data visualization technology that lets you create interactive charts graphs maps and other visuals that bring your data to life.
Now power view is available in power BI Excel and other analysis services from Microsoft.
Then you have power map which is also another feature in Excel.
It for exploring map and time base data.
It lets you plot geographic and temporal data visually analyze that data in 3D and create cinematic tours to share with others.
Next, you have Power BI services, so this is a collection of apps, dashboards, and reports built to deliver key metrics for your organization.
These apps are interactive with each other and helps customers work with their own content.
Then we have Power BI.
It's basically a feature which helps you ask questions and get immediate answers.
Sometimes the fastest way to get an answer from your data is to ask the question using natural language.
So you can use Q&A to explore your data using intuitive natural language capabilities and receive answers in the form of charts and graphs.
Next you have the data management gateway.
So basically what this does is it connects your on-premise servers with your Power BI in the cloud.
If you want to refresh your data in the cloud with the data that is on the premise,
you will need to have the data management gateway configured and available to your tenant.
And that is how this works.
And finally, we have our power BI data catalog.
Now this contains the metadata for felicitated search functionality in power BI.
Now your metadata gets stored in power BI data catalog in the cloud for a
It gives you a search access list for the query to determine which users and security groups can find and use this shared query.
Now that we've seen how the components work, let's continue with this tutorial and understand the architecture of Power BI.
Now broadly describing barbed is architecture has three phases the first two phases partially use ETL to handle data
And then you have the presentation of your data.
So take a look at these phases one by one first you have data in An organization can be required to deal with data
that comes in from different sources as I had earlier explained in my Amazon example.
Now this data comes from different sources and can be in different file formats.
Now the data is first extracted from these sources
which can be your different servers or databases so on and so forth from wherever you can pull in.
data.
This data is then integrated in a standard format and then stored at a common area called as a staging area.
Then we go to our second step, data processing.
Now the integrated data is still not ready for visualization because the data needs processing before it
Now this data is pre-processed or cleaned as we can call it.
This is also known as transformation of data.
For example, missing values or redundant values are removed from the data set.
After the data set is cleaned, business rules are applied to the data and it is transformed into present table data.
Now this data is then loaded into a data warehouse and now that you have extracted transformed and loaded data your ETL is complete.
Finally, you have data presentation.
So once all this data is loaded and transformed,
it can be visualized much better with use of various visualizations that Power BI has to offer.
You use report.
and help one represent data in a more intuitive manner.
These visuals, reports have business and users take important business decisions based on these insights.
With that, let's move on to the building blocks of Power BI, where we can talk a little more about these insights.
Now everything you do in Power BI can be broken down into the following building blocks.
A good understanding of these building blocks would help you understand concepts and will let you create detail and complex reports.
So the basic building blocks of Power BI are the following.
You have visualizations, data sets, reports, dashboards and tiles.
First up, you have a visualization.
A visual representation in the form of graphs and charts and maps of a data is called visualizations.
For example, a chart or a graph can be used to represent data visually.
Power BI gives you different visualization types which keep getting updated with time.
Now some of the commonly used visualizations are map representation, card visualization, stacked chart, free map and pie chart.
Now, these visualizations can be simple or complex.
However, visualizations aim at presenting data in such a way
that it gives you more insight in the context, which is otherwise difficult to discern from simple data.
Next we have data sets now we know that a data set is nothing but a collection of data or information in the form of spreadsheets now power be I can harness this data to create visualizations.
It can be a simple data set or a combination of many different sources which can be filtered and combined to provide a different data set altogether.
For example,
you can pull together data from many different sources like a different database fields and Excel table and online results of some email campaign to create your data set.
Having said that, you may want to filter your data before you bring it into Power BI.
filtering lets you focus on the data that actually matters with your data set ready you are now free to create
visualizations and display different portions of the data that's set in different ways and with this you gain
insights next you have reports now a collection of visualizations that appear together on one or more
pages is a report in power In a collection of items, these reports combine to form a workbook and are all related to each other.
You can create visualizations on multiple different pages if necessary and arrange them in a way that best suits your interest.
What you see on your screen is the image of a sample report.
Next you have dashboards.
Now power bi dashboard is a single page interface.
It is often called a canvas that uses visualizations to tell us Now,
a lot of you might be confused within the difference between a report and a dashboard.
Now, this is because it is limited to one page.
A well-designed dashboard contains only the most important elements of your story or your report.
The visualizations you see on your dashboard are called tiles and are pinned to the dashboard from the reports.
So in a way, you can see that your dashboard is a compressed version of large reports that you are going to see.
Now because this is limited to just one page, a well-designed dashboard contains only the most important elements of that story.
The visualizations you see on the dashboard are called tiles and are pinned to the dashboard from reports.
In Power BI, a tile is a single visualization found in your report or on a dashboard.
It's the rectangular box that contains each individual visual.
Now, Power BI gives you the freedom to move or arrange tiles so you can present the data the way you want to.
Even while you're creating a report or dashboard,
you can make the tiles bigger, change their height of width and snug them up to other tiles any way you want.
So this was all about Power BI's building blocks.
Now I'm going to take this Power BI tutorial a step further with a demonstration of creating a simple report using Power BI.
Microsoft Power BI is a suite of business analytics tools that helps you create and share actionable, intuitive reports for business insights.
And now I'm going to show you how you can put your data to work with Power BI.
We'll go through the basics of data visualizations and dashboards and we'll go through how to create and modify data visualizations.
We'll also look at how to join data from multiple sources and build a dashboard report to share with our colleagues.
So what we're going to cover today in part one will be getting started using it.
Part two will talk about joining data from multiple sources and part three will talk about building and sharing a dashboard.
And at the end of this, we'll have a demo.
So part one, getting started.
In this section, we'll learn how to install the application.
We'll talk about importing data from Excel to Power BI.
We'll create and modify simple visualization and we will save our report and publish to Power BI service.
Now, let's talk about installing the laptop application.
First, what you want to do is go to HTTP colon slash slash app.parbi.com.
Here you will sign in with your credentials.
You'll run a simple wizard to install the application.
Then you look for the download icon.
It is here.
It's an arrow pointing down with a line underneath it.
So run that wizard.
and the Power BI will be ready to launch and it will automatically launch the first time that you run it.
When you run it, you'll get this start screen.
It is black and yellow and you'll have access to forums,
the Power BI blog, various tutorials as well as some videos that you can watch and learn.
But you don't need any of that videos because you have this one.
So let's go ahead.
You can also access the get data functionality here from the screen.
If you decide that you never want to see it again,
you'll notice a checkbox in the lower yellow part of the screen that says show this page on startup.
Simply uncheck that box and you won't ever see it again when you run the application.
So next up you will want to install some data after you've installed the application.
We're going to go to the get data button and you'll be able to pull in data depending on what you're using.
You may be using data from Excel like I'll be doing further in this demo or you may be using data from an example,
a SQL Server database or an access database.
The options you have for pulling in data are a plethora.
So there's a lot of available data sets and we'll be sticking with the Excel for my demo.
But keep in mind that you can pull data from a lot of.
So after you have selected what data you're going to be using, you'll be given the option.
Then the navigation window to select the exact data set what you want to pull, you'll check mark a box.
For example, right here we have this box.
and then you'll have the option to load the data or edit the data in the query editor.
You'll probably want to edit the data in the query editor just to make sure that you'll pull in exactly what you want.
Once you have pulled in the data that you want,
you'll see that the data up here as a fields list on the right hand side of the application.
As you can see here in the sample data,
we have a budget business team delivery day, but whatever you've pulled in will appear in the fields list.
And you'll be able to use that data in the fields list to create your visualizations.
So let's start here by creating a simple version.
This is a very simple visual.
It's simply a column chart with one number.
So it's one column right here.
It's the budget field that we pulled in drag whatever field that you want from your data.
There is no strict rule to it and make a visualization.
You'll end up with a column chart like this.
You can also see that there are a variety of charts available there and then in the visualization
box you will be able to make modifications to it and like I said this right here is
a bar chart but you will be able to make pie charts column charts line charts and a variety
of other visualizations A variety of modifications are available for visualizations to write here.
We've highlighted where you can click on the lower right hand corner of the visualization
and drag it up to make the visualization bigger or bigger.
For example,
you might want to make it smaller because maybe you want to put more than one visualization on the canvas there or you may be wanting to make it large so that it can take up
the entire canvas so you can drag around the corners of your visualization to make it the size that you want.
The format that power BI saves reports says dot PBI X5.
This is not the best way to share a report, but it's definitely the best way to save your work.
If you are in the middle of something,
you can go ahead and save your report as a dot PBI X on your machine or your one drive,
maybe even your one drive for business or a shared point online set wherever you're saving it.
And so that way you'll be able to pick up later
and save your If you're in the middle of building a Power BI report now,
if you want to share your results of someone, the best way to do that is to publish the Power BI service.
And you'll be able to do that using the publish button.
That is the ribbon.
And that button is on the home tab on the furthest right.
We have highlighted.
and then when the publishing is complete,
you'll be given a link that you can click on in order to go see your workbook or your report.
This will be there on your Power BI website and you'll probably want to do that just to make sure it looks as good as you wanted it to in your tutorial.
tool.
So this is the parbi.com interface.
The parbi.com interface gets changed from time to time.
That is also the reason for this particular tutorial because it got updated in 2019 recently.
It is a cloud service and when new features come,
you may see things in a slightly different For example,
the search bar here I believe is slightly different now,
but the gist of it is what we want and you'll be able to use that search bar
once you've published a lot of reports and have a lot of data sets available on your workspace.
You'll be able to find them easily using the search bar or you can use the functionality.
of defined your recent reports as well as when you will be able to manipulate and share them using Power BI.com interface.
We're now going to go into a part two, which is using multiple data sets.
So now we are going to be using multiple datasets and joining together in this section.
We'll talk about adding data from other sources,
joining the data from multiple sources creating more interactive visualizations as well as updating that published data on the parbi.com web service.
So here we go with getting additional data now to get additional data.
You do the same thing that you did before you got your first data
set and it's really not very different from how you did it.
You just are having some data you're working with.
You'll go to the get data button.
You'll push it and our demo will be using Excel.
But remember you can pull in data from a variety of sources.
keep in mind that they don't all have to be the same source.
You could be using some data from an Excel spreadsheet, some data from example, a SQL server query or from another database query.
You can pull them all together in Power BI, create relationships and manipulate that data.
and create some great visualizations right here, just as you did before.
You'll select the data that you want to load, and you'll probably want to edit your query before loading it just like you did before.
So whenever you're loading queries,
you want to make sure that you have the data that you want and your data is in the form that you want it in before you pull it in power be I will load the data
that you have selected and there may be relationships in that data.
For example, here we have some actuals and some budget information that we are loading in sample data.
But you know, you may have two different tables that you are pulling in and they both have a month column.
For example, in Power BI, the tool is actually smart enough to detect the relationship and join the data together.
If it did not auto detect your relationship, you'll be able to manually created.
But for most times in my experience, it does it on its own.
So I want to introduce here the modeling tab.
It's right next to the home tab and with the modeling tab What you can do is change some things with the
You might want to,
for example,
sort in a different way that then Power BI has already sorted your data and you might want to change the format you know if there's a number you want to change to a currency
or maybe you've accidentally formatted some numbers as
text and then it's important that you notice that you can't do any math
because of course you can't do math with text and your problem then is that it's formatted as text.
You can to the modeling tab and change the format of the data over to the number format.
So now you have pulled in data from multiple sources and you want to create some new visualizations.
Well, keep in mind that the canvas view here.
That's what we've highlighted.
On the slide, that is the top of the three icons.
You see there on the top, you've got the canvas view below that is the modeling tab that we just talked about.
And then there's also the relationship view and we'll be talking about this as well.
And in the demo, you'll be able to create visualizations using all of the sources of your data and whatever you want.
you'll be able to create visualizations with all of your data combined.
And that's one of the really powerful aspects of Power BI.
As you create visualizations with your multiple data sources, you'll be able to modify those just like you did before.
You can use the handlebars on the visualizations to drag and drop the corners around.
Make the larger, smaller fit how you want to.
Also keep in mind that you can have more than one canvas page.
You certainly don't need to cram all of your visualizations onto one page.
If you've used Excel before, it's kind of like making a new show.
sheet.
You'll see the bottom of the page and there's a little plus sign and you'll be able to create a second,
a third,
a fourth canvas page and add more visualizations into the same report using multiple canvas pages and made a couple of visualizations with Right here,
what I want is to highlight the fact that you can change the colors on your report to highlight certain things.
For example,
right here we've added some black to the columns and Power BI actually gives you really strong granular control over,
you know, what your charts look like.
For example, in a pie chart, you'd even have the granular control to change the color of one slice of the pie.
So you really have a strong control on how your visualizations look right here.
Now that you're done pulling in data from multiple sources, you're probably ready to publish.
Now, whether you're publishing just for the first time or publishing again, it's just...
You simply go to the publish button that's on the home tab.
Now, if this is your first time publishing simply just publish it and then click on the link to go take a look at it.
If it's your second time publishing,
you'll be either given a choice to say override a previous publication or you're going to rename it and publish it again as something else.
You'll get this wizard opening up giving you the option to go to your published report.
You'll go into powerbi.com's interface and view it.
With that we are moving into part three.
Now this is the creation and sharing of dashboard.
In the section, we'll talk about creating a dashboard and we'll talk about pinning visualizations to that dashboard.
We'll talk about modifying a dashboard and we'll talk about sharing that dashboard with your colleagues or customers or business partners.
So you obviously might be asking illogical question what exact Well,
most of you probably kind of know what a dashboard is, but what is it exactly?
And at this point it might be useful to make a distinguishing characteristic between data sources reports and dashboards.
Now we all know what our data sources are.
Those are our Excel spreadsheets, SQL Server queries or other data based queries.
queries.
When we've pulled in numbers and text and other types of data, then we've built a report on top of it using Power BI.
Now, once we have created visualizations on that data, a dashboard is a really
It really is just a type of report and what it is,
is that visualizations from other power BI reports are all pinned to one specific place that can be updated in real time so that
your business partners and your business decision makers,
your customers and your colleagues can look at the dashboard and instantly have the information they need to make those important business decision Basically,
if you bring all your Power BI reports regarding that particular decision and put them into one canvas, that is when you have a dashboard.
It is a compact form of your complete report.
So now how do you create a dashboard?
The easiest way to create a dashboard is to simply click on the pin icon,
which you'll see on your visualization and we'll then give you the option to create a dashboard.
You'll also see as we show you on the screen here, there's a plus sign next to the word dashboards in the Power BI interface.
You'll create a new dashboard there too.
Once you've created that dashboard, you can continue to pin visualization.
Here we go,
there's that pin icon there and you will be able to click that and attach visualizations to your dashboard and once you've done that,
you'll be able to move them around and make it look like how you want it to look like.
Eventually, of course, you'll have to share that dashboard with whoever needs to see it.
The pin icon will ask you which dashboard you want to pin it to.
Do you want to pin it to an existing dashboard or a new dashboard?
If you need a new one, go ahead and select that.
But you know you're building one dashboard at a time and you're probably going to be sticking
with your existing dashboard for a few visualizations at least.
You can view your new dashboard with your visualizations, of course, and you want to look at it before you share it.
Just to make sure that it looks how you want it to,
go ahead and click and share and send it to some of your colleagues.
You can modify your dashboard anytime and you'll be able to click on the
corners of your visualizations to make them larger or smaller just like you would want it.
Now, you won't have quite the degree of freedom you did with the canvas though.
There are certain size settings for these visualizations.
They can be so big, but you can't choose exactly how big you want them like you could in the canvas.
When you're in the web view,
the predefined sizes is what they are called when you're finally ready to share your dashboard go ahead and click the So you'll type in the names or email addresses of the people you want to
share it with if you're dealing with a circ*mstance where you've got an internal and you know the global address list you'll be able to simply type names from that or if you're sharing externally you
may need to use email addresses and you'll type those in and go ahead and click share you notice you have some options here and you can allow the recipients also to share your dashboard.
And generally you do want to send an email notification when you share something with them.
So then they'll know that it's shared with them and they'll be able to go access it.
There's an option you can check as well.
You can also share your dashboard once again by simply copying and pasting the URL.
Once you've clicked that share button and added the person's email address or their name and shared it with them,
even if they lose the link or they forget about it, they will never have to go through that process again.
You'll actually just be able to copy that URL and paste it and they can view your dashboard.
board.
Apart from that, there is also a QR code generated for every report for the very same purpose.
Now the first thing we need to do is that you need to install Power BI.
For that, you need to go to Power BI's official website that is powerbi.microsoft.com and you can directly download the option here.
So here you have the option of Power BI Desktop.
So first what we need to do is that we need to create our report.
Then we will go on to create a file dashboard which has all these inside.
So what we will do is we'll be creating a report for each of the insights in the Power BI Desktop.
So here just click on download option and it will automatically initiate the download.
So once you've downloaded this file, you need to install it.
It's a very easy installation step.
So this is what your power bear would look like when you've been launched.
So one thing I would like to recommend is that you sign it.
If you not created a sign it then definitely and make sure that you create a sign in our power bear desktop.
So once you've successfully logged and you get this notification here of your user name as well
So let me just give you a simple overview with respect to how Power BI works and then we'll start with respect to our session
Now first you have a simple workspace that is the first workspace that you see is the report workspace
This is the workspace where you will be creating the different visuals as well as creating the different reports as well
Then you have the data workspace.
So when you're loading a data any data that you are working with can be viewed here All the modifications that you want to perform with respect to the data can be done here
And finally you have the relational workspace.
Now the relational workspace is one of the useful workspace When you're working with multiple tables.
Now this helps you establish as well as plan is the different relationships between the.
Today I shall be discussing all the charts you need to build effective reports on the Power BI Desktop.
This session will take you through the various Power BI Desktop Charts and most importantly, when is it more appropriate to use?
use them.
We'll be going in order with which these charts are present in the desktop app.
So without any further ado, let's get started.
So first of all, this is my data set.
I've already imported the object.
I think I should explain where this data set comes from because this looks pretty morbid
with all the bombs and weapons I assure you this was no real life data.
So I hope most of you have heard of if not played the game of Counter Strike Geo for those who haven't.
It's one of those first person shooter games where you go around killing a bunch of your friends.
It's great.
Some good clean fun right?
But the best part about it is that all the data you've generated on the game is available
So I've acquired these datasets using various game attacks and it has an entire history of how many minutes I've played,
what weapons I've been using, what maps I've been playing on, how many have killed, how often I've been shot, all this great data.
And I thought it would make for a great demo.
So let's start with the charts.
So first of all,
I'll be creating a basic bar graph or a column graph with this for that you can use any of these given stacked bar charts column charts any of these
which wouldn't really matter because we are just using one case this is basically because
I want to show you guys what you can do and how you can transform these charts into its most effective form.
so let's take a column shot now I'll be using this other data set which I also
got from the CSGO it's a players data set which has a 20 players count the
kills how many times they've been shot the latitude and longitude from where
they've been playing etc etc so it's pretty simple actually all you have to do is and drop it here on the field.
What can also do is you can drag the same column tab and drop it right into your graph.
There you have it.
Now there are a bunch of interesting things you can do with it.
For example, I would like to change the color saturation according to the number of absolute kills.
So, green being good and red being bad, as you can see, this player number 20, despite
the best kd ratio,
does not necessarily have the best absolute number of So you can do a lot of cool stuff like this with the power BI.
Let's move on to our basic stacked and clustered charts.
Now this entire column gives you bar and column charts.
Now they are of two types mainly one is the stacked chart and one is the clustered chart.
I'll be showing you the difference between the both.
Now first of all, I'm taking the stacked chart.
Here I'm taking a bar chart.
As you can see, it's horizontal instead of vertical.
On the axis, I'll be taking whether the bomb is planted or not.
It's a common axis.
So it's basically in a true and false situation.
In the legend, I'll be taking the weapon type.
So the legend is where you can specify and a lot of color to each category.
And in the value, I'll be obviously putting down a count.
And there you have it.
As you can see, the rifle has been used the most immediately followed by the pistol.
Now, if I had to represent the same data using a cluster chart, this is what it'll look like.
I'll use a clustered column chart here and I'll drag and drop the same data which I did for the previous chart
So basically you use both these charts when we compare different cases depending on the same two parameters
The stack charts are where you compare things as parts of a whole
but a cluster chart is where you do the same thing but in separate bars so with
that let's move on next we have our line and area charts so these are the
charts which usually show growth but you can also use an area chart to show
volume in some cases here I'll be finding out the tick rate by plotting tick
against this second on the axis will take the second step and in the values will take the count of So,
for those who have a doubt, tick rate is basically the number of times your game refreshes in a second.
For people who do competitive gaming, a good tick rate would be 128 as we can see if we calculate the slope of this graph.
It's 128.48 right here.
We can plug the same thing using an area graph.
As you can see the graph looks similar, but it gives us an idea of the area shaded under it.
It gives us an idea of the volume.
So with that, let's move on to our next chart.
So here I'll be using a combination chart.
As the name suggests, it's a combination between the bar chart and the line chart.
And you can use it the same way as we did the previous charts so on the shared axis I'll
be pulling down the players on the column values I'll be putting the kd ratio or let's
just put the absolute number of kills and the line values I'll be putting the kd ratio.
As you can see, it's pretty similar to what we had inferred in our column chart.
Another interesting chart here is the ribbon chart, which is like an area chart, but it shows data with respect to the maximum measure.
So let's try that out as well.
Now on this, I'll be plotting, let's say.
The weapons used in the number of rounds, so let's bring the round to the common axis.
We'll categorize the colors according to the weapon type and then we'll bring the count of weapons to the values.
See how intuitive this is, because when I just drag the weapons value to the value field, but the count it selected on its own.
So there we have it.
We have the combination chart as well as the ribbon chart.
As you can see as we had inferred before the rifle has been used
most and by just touching on each of these colors or any part of the
graph you can find out the absolute information regarding the bar.
Next up, we have another one of our very common charts.
You've probably all seen this one before.
It's a pie chart, it's a big circle cut into pieces, can't really miss it.
A chart is essentially the same thing except for that it has a smaller circle cut out in the middle
turning the filled pie into a hollow donor.
It's a visual preference mainly but there is a key difference between both of them.
Let's start with the pie chart.
In the legend I'll be putting the weapon and in the values also I'll be putting the count of weapon.
I'll be doing the same thing for a donor chart.
closer to make it look better so now go ahead and look at the pie chart notice
how you look at it chances are your eyes go straight to the center at least at
first you view the pie chart in its entirety because pie charts are filled to
the center and here's a Because donut charts are hollowed out, there is no central point to attract your attention.
So where do your eyes go instead?
If you're like most people, your eyes travel around the circumference of this donut chart.
You judge each piece according to its length.
As a result, you can also think of a donut chart as being a stacked bar graph which has been curled around.
itself.
So essentially we use donor charts for its readability and the pie charts for percentage breakdowns.
So next we have the tree maps which serve the same purpose but according to the higher archive.
Let's plot the same thing.
Let's just plot the weapon type.
With that, let's move on to the maps on BI.
Now, we can be using a number of maps here.
The maps here are where you can show data density on certain states,
but we'll be using the regular map because we don't honestly have so much data.
So we'll be plotting where the players come from latitude at the latitude,
longitude at the longitude for that you'll have to categorize the latitude and the longitude as latitude and longitude in the data view.
So we can also do a bunch of other things with it
like we can change the size according to the count We can change the size of the bubbles,
as you can see, wherever there's a concentration of more players, you can see the bubble is larger.
You can also change the color saturation.
Let's change the color saturation according to the absolute KD ratio.
There you can see green being good, red bad again.
You can do something really, really similar with another tool here, which is the ArcGIS map, the latitude, the launch dude.
The size will be according to the count and the color could be according to let's say absolute kills.
There we go.
You can also change the colors if you like if you go to this formatting tab over here the background border lock aspect a bunch of different things another thing worth noting is that here msbi
this uses Bing's map engine so it's very precise that's also why it takes some time to plot next we have funnel charts.
So basically this shows stages in progress.
This really cool.
Change the color saturation.
While I'm here, let me also explain the slicer to you people.
Basically a slicer slices the data according to how you need it, according to all.
And I'm slicing data according to the absolute number of kills here.
You can actually control the data visualization from both the sides.
You can see the absolute number of kills in each bar between the 61st and the 104.
So that's basically how slicer works.
You can use it on maps, your pie charts.
You can basically use it on any other chart that you want to.
So now with the visualizations we've used so far, these have been visualizations which are used to compare values across different fields.
But to create Power BI reports sometimes you only want to show a single metric just so you can track as it changes over time.
So here are a few different visuals that do it.
So gauges are great if you want to show progress towards a particular target like so.
By default you can always see double the amount of the amount shown here.
But you can obviously go change it here.
You can go,
you can change the data labels,
you change the gauge axis, you can change the call out value, the lock aspect, a bunch of different things.
You can also add other fields here like minimum, maximum or the target.
So that's one thing.
Another thing we can use is the card.
here.
You can also use a multi-level card but this is a single row card.
So this is the card which just shows the numeric representation as text.
By default we use units to trim down the number but we can also use the formatting tab to change how it shows the number.
So you can do a bunch of really smart things with it like you can use the measure and ask MSBI to return a string moving on.
So all these numbers lend themselves to showing KPI's where you've got a particular value and a target to your working towards the great thing about this KPI is that it shows you an indicator and
a number as well as a trend over a period over time.
You can control your goals right here again back to the formatting tab.
There is the goals bar here.
There's the goal and distance.
You can control the trend axis.
You can change the indicator and how it displays the units and so on and so forth.
Now, along with these charts, Power BI also has some tabular visualizations to look at your data.
For example, I'll bring the table over here, and I'll start adding fields to this.
Let's say, you can just go on adding tables that you want to.
You can just go on adding as many fields as you want and it will keep giving you a total.
Similarly you have the matrices here.
Now I'll be creating a very simple 2x2 matrix.
say the rows could be the weapons and the columns could be the weapon types and the values could be your count.
A thing to notice here is when you add another field you do not get repeated values hence
you get the absolute total from both sides.
With that, we've got just one last visual left.
I would only like to address this one as it deserves a session of its own.
Now if you're into data science, you might be familiar with something called the R.
This is a really common application used to do deep analytics and statistics.
It is also a great visualization platform.
form.
So MSBI allows us to integrate with R.
So it basically means you can get your power BI file over to R,
get visuals to run and bring it back to the desktop and use it like any other chart.
This session will be answering all your questions you have regarding KPIs stop.
So before we begin, let's take a quick look at the outline of this tutorial.
So today we shall be discussing one.
What is KPI next when to use KPI third.
What do you require for KPI and finally how to use the KPI visualizations in the power BI desktop.
So without much ado, So a lot of you might wonder, what is KPI?
So a KPI or a key performance indicator is a visual cue that communicates with the amount of progress you've made toward this certain goal.
It basically demonstrates how effectively a company is achieving key business objectives.
So organizations use this KPI at multiple levels to evaluate their success on reaching targets both internally and externally.
So high-level KPI may be one switch focus on the overall performance of the enterprise.
While low-level KPI's may focus on internal things like employees in departments such as sales,
marketing, et Next, so this is a really important question when to use a KPI.
So KPI is mainly answer two questions.
A, what am I ahead or behind on?
This specifically refers to a number which is your target.
And secondly, how far ahead or behind am I?
So this represents a trend which is related to the target.
Since a KPI is based on a specific measure, it is designed to help you evaluate a current value and a status of the metric.
So therefore,
when we ask who do you require for a KPI, it basically requires a base measure that evaluates to a value and a target measure.
It also requires a threshold or a goal which the target is set.
So currently, a KPI data set in Power BI needs to contain goal values for a KPI.
So if your data set does not contain one, don't worry.
You can create goals by adding an Excel sheet with goals to your data model or in a PBI file.
So this is the next segment.
I'm sure most of you were waiting for this till now.
So, how would you use your KPI visualization?
So, for that, we need to open our Power BI Desktop.
So, we'll be creating a KPI that measures the progress we've made towards a certain
A lot of the people will directly start with a KPI,
but I personally find it more comfortable to start with a column graph and then change it into a KPI.
So before we start, let's import some data.
Here I have an Excel sheet with KPI appropriate data.
So this is what the preview of my data looks like.
We've got an actual sales column and a target sales column.
month-wise, here we've got the Jan to December month numbered accordingly, and here we have the fiscal month.
For those who don't know, fiscal month is basically months arranged according to the financial year of a country.
Here it is April to March, hence I've started with one being April and So let's get back to our charts.
So as I said, I'm going to start with a column chart here.
We're just going to drag and drop values.
Like I'm just going to take the month and drop it into the graph and then take the actual drop it into the graph.
Now the thing is Power BI Desktop is actually smart enough that it knows what column to take as what parameter
So now we are going to change it into a KPI Now this is my KPI icon.
We're gonna be using this.
Let's select the KPI icon and there we have it now to turn it into an actual KPI we must have a target so
let's take the target sales and put it in the target goals field so this is what
a KPI is mainly supposed to show here this is a number that I'm ahead or behind
on and this is the trend now looking at it this way you might not
but I assure you there is a problem with this for that I'll have to use the table using the table is as easy as using any other visualization here we just take the month drop
it the actual sales and the target sales per month I'll be going to the formatting pin here just increase the size by a little bit go to the grid.
and we increase the text size so you can see it properly then as you can see the
months are ordered alphabetically so this has to be changed so what I'm going to do is I'm going to select the month tab here,
go up to modeling and here you can see an option which is sort by column.
Here you can either change this to fiscal month or month number.
I'm simply going to choose the month number here there.
See how our target has changed.
Optionally you can also format this by choosing this paint roller looking icon right here, which is the formatting panes icon.
Now, here we have the indicator, which controls the indicator display units and the decimal places.
Next, we have the trend axis.
When it is set on, the trend axis is displayed in the background of the KPI.
Next, we have the goals.
When set on, the visual displays the goal and the distance from the goal.
Next, we have color coding.
Suppose company follows a certain color palette.
This is where you can change the colors to match your color palette.
Here you can also choose the direction suppose high is good or low is good.
For example, if it is something like earning versus wait time, typically a higher value for the earnings is better.
Suppose it is something like a defaulters graph, then essentially a lower value is better.
So you can change the color settings accordingly.
Here good colors green, bad colors red, neutral colors yellow.
So let's get back to our graph.
Now that you know how this tool basically works you can do some really smart things with it like you can create a measure in the model to return a string
So I'll be calling this progress.
Now you can see the progress column is added here.
What I will do is I'll be taking a card and I'll be adding the progress to its field here.
So it's basically going to return the progress.
Notice this here.
So this is a lifestyle and it'll keep changing our data set.
You can also do a bunch of other interesting things.
For example,
instead of a single KPI,
you can use a multi KPI for that all you must do is go to the home tab here and on the ribbon,
you can see something click on it.
Search for something called Power KPI.
You can see the icon appear here with other icons on your visualization pane.
You can use it like you use any other visualization here.
So you can just drag and drop different values.
on your values, your axis has to be a certain date.
It does not matter what date, but it can be a period or a month, but it has to have a date.
Then in your values field, you can just drop actual and target.
There you go.
This also you can format using this This also you can format using your formatting pane.
Like you can go and change your layout.
You can change the title.
Suppose now it says actual and target by month.
You can just rename it to KPI.
There are a bunch of other options you can play around with.
Let's move on to something else.
Another thing you can do is you can actually get a custom KPI from the marketplace again.
You can just go to the KPI option right at the bottom somewhere.
You will have something called the KPI indicator it will take some time to load.
You can also use this like any other visualization on your visualization pane, like so.
You can use it like you use any other KPI tool here.
As you can see, this has vibrant colors and it shows the graph in a real world.
with these dots and indicators what you can also do is you can go to this formatting pane and you can change the graph.
You can go here to KPI general and choose a chart type.
Right now we have a line chart then we have a line no marker where those indicators are gone.
Next we can use a bar chart as well.
well, you can choose a banding type where the increasing value is better decreasing
value is better or the closer is better like the increasing and decreasing I had explained earlier
in the KBI chart the closer is better option can be used where you are testing medicines
and chemicals here right now we have increasing is better you can change the
You can change the colors like you did in the previous charts.
Let me get a slicer here.
Now I'll go to the formatting pane and in the selection control,
I'm going to turn off the single select so I can choose multiple things on the slicer.
As you can see,
you can use it like any of the charts on your power BI, all three of them are interacting the same way.
With that, I think we've covered the KPI.
out.
A dashboard in Power BI basically is a single page wherein you have all your visualizations with respect to a specific requirement present day.
Now this dashboard could be with respect to different domains.
Now let's see if it's a dashboard then what would happen is you would have the details of the employees you'd have the ratio
of the employees you'd have number of employees that have come in today you'd see the number of
requests that have come to VHR and so forth okay now here what you're seeing basically is a
marketing dashboard now it basically is a company based marketing dashboard where you see the different
opportunities opportunity count the revenue and so forth so to be very short a dashboard is just
one single page of visualization which tells you a The story is to meet the end user's requirement.
So it's up to the user who's creating this dashboard to customize the story as per their choice and make a happy ending.
Now, there is always the ambiguity as to how a dashboard is different from a report, so let's talk about that next.
Now, as I had mentioned earlier, a dashboard basically is just one page canvas here, which
tells you the complete story that you want to know.
However, a report can contain more than one single page.
What happens here is a report is way more detailed, way more inside it and way more specific with respect to the requirements.
So, let's say if you want to have a complete analysis, then you would go for a report rather than a dashboard.
Now here again,
when you talk about the data sources that a dashboard can take since a dashboard is a combination of different visuals,
this basically can come from different types of reports.
Thereby, you can also integrate your data from different sources as well.
But when you're talking about a report, here you're mainly working with one single dataset that's it.
report purpose.
So let's say you're going to create a marketing report, then you're going to take just the marketing data.
But let's say when you're looking at an organizational data,
there you're going have data from the marketing, you're going to have marketing data from the sales finance and so far.
Then you have the option of pinning.
Now, what do I mean by pinning?
Basically, it means that you are adding or copying a specific visual.
Now, in terms of a dashboard, you can pin an existing tile from a current dashboard to any dashboard.
That is, if it's already present in the current dashboard, then you can put it to any other dashboard.
However, in case of a report, you have a broader opportunity where you can pin it to any dashboard as such.
If you want, you can pin the entire page of the code as well.
Are guys clear here?
Good.
Now apart from that, let's see if you want to filter or modify your data to get better insights.
Then what happens is this cannot be done with respect to it.
dashboard.
In your dashboard, the data is already fed.
So filtering and slicing it there would not be possible.
However, when you're working with a report, here you can basically filter your data, you can highlight it, you can slice the Raspberry
requirement, you can join your data, you can separate them, you can group them.
All this can be done on a report level.
However, when it comes on a dashboard level, it basically cannot be done.
So you've achieved this slicing of filtering on the report level,
definitely it will be applicable But however, it cannot be directly implemented on the dashboard.
Then you have the option of alerts.
That is when a certain condition is met, then you can get an alert from that.
This could be on your mobile application.
That is a PowerWave mobile application.
You can get a notification or let's say this could come to you as an email.
So this again can be set on the dashboard itself.
So let's say you're comparing multiple parameters.
Let's say at one given point your sales and profit values have changed.
It has come down below a certain value.
Then what happens is as an organizational person, you need to take the responsible measures.
So this can be very useful when you're monitoring your data on a real time basis as well.
And in cases when your work.
port to differentiate with respect to different dashboards.
You can set them as feature dashboards.
Now feature dashboards basically are those dashboards which are highlighted from the rest.
However, in case of the report it cannot be set up.
as such.
And then going on from there, you can also use natural language query.
Now what exactly is a natural language query we'll be looking at in the later part of the session.
But to give you a simple idea or an understanding here,
what it basically refers to is that you can directly feed your queries in English itself.
You don't need to write them as a SQL statement or anything like that.
So let's say you want to identify which is my top selling store.
Then definitely you can just write that and your Power BI dashboard.
it and give you the output in form of a visual.
Now don't worry, we'll be seeing that as part of our demonstration as well later on.
So I hope you guys are quite interested.
Now apart from that on a dashboard level, you cannot change the visual representation of a certain form of visual.
That is, let's say if you're using a line chart to denote something, then as per your requirement, not change it in a dashboard.
So you need to go back to your report then you need to modify it there and then you need to pin this updated wishbone.
So what happens is it gets updated in the dashboard as well,
but when you directly modify it in the report, it does not get reflected.
So you need to rewind this updated which will as well.
Now when you come down to the customization capability of both, a dashboard basically uses visualizations in form of a read-only method here.
So any changes that you make are basically with respect to the changing in names.
You can either link them, you can resize them, rearrange them and so forth in the dashboard.
However, in our report, here you have complete write with the as well as the data that you're working on.
So you can modify it, change it as for your requirements.
It's completely left to you as to how you can work around with them.
So I guys clear with respect to both how the report is different from a dashboard.
Okay, I seem to have a question here.
Do we need to reports for dashboards?
Definitely you do because it's from the reports that you will be going on to create a dashboard.
So to put it more simply reports give you a complete idea.
Whereas a dashboard will give you a complete overview.
So based on your comments, you can use whichever nature necessary.
So moving forward, let's look at the data set that will be working with today.
So we are going to work with a superstar data set of a United States organization, which is kind of to 2016.
Now, I'll be showing you the data set as just a minute.
So this is your complete data set.
You have basically the date of order,
you have shipping date,
the ship mode,
the customer ID,
customer name,
which segment it was from postal code,
city, state, country, apart from that you have region, market, the product ID, the category, subcategory and so forth.
Okay.
So I hope you've got a general idea of what the data is.
Okay, that's great.
So what we need to do is we need to mainly achieve four insights from these data First what we need to identify is the overall performance.
That is we need to identify the sales and profit of our complete superstore Okay apart from that
We'll also try to identify the performance in different regions and we'll see which are profitable regions and which are non profitable regions
Then what we'll do is we'll try to identify the performance of each state as a whole.
Okay, now which state is going to show you the opportunity of growing which is basically
pulling your market down and which is something that you can look forward to in investment or marketing as well.
Okay.
After that you have inside three where we'll be seeing the performance of different segments
involved in the superstore and how do they contribute as well.
Finally, what we'll do is we'll try to understand these revenue generated by each category of product.
Now, there are different categories.
will see which category is hampering our growth and which is feeding our growth as well.
Okay, I guess clear and here the first thing we need to do is that we need to import the data.
Now for that you have this option here called get data.
Click on get data.
And you have the most common data types here.
You have Excel Power BI service, SQL Server, Analytical service.
So for seeing the complete list, click on More.
And here you have the complete list of different data sources that you can connect with respect to our Power BI Desktop.
Now, we're going to work with an Excel file.
So let me just click on Excel, and it's going to ask me the location.
So let me just go down to Global Superstore.
and it's establishing a connection to the data set.
Okay, so since my data set has multiple pages, it shows me the different pages as well.
You will have orders, you have people and you have return.
Once you are on a sheet,
it extracts about 200 rows and gives you an overview with respect to what is present in your data set.
Okay, this basically helps you verify what data set you're working with and is it a valid data set or not.
So since we need only orders detail, let me just click on orders and then click on load.
So now you can see successfully the data has been loaded because you can find the options of fields here.
Okay.
So this basically is with respect to the orders table.
So let's say if you work with multiple tables,
each one of the tables as well as all the columns present in those tables would be highlighted here.
Okay.
So this is where we'll be working our way now to get a more insight with respect to your data.
You can go to the data workspace and here you have the complete
So this is your complete data set present here and you get to see whatever data is present and if you wish to perform any operation on this
So let's see if you want to modify this data then you can click on edit queries option here and you get the query editor
So this becomes the workspace where you can perform all your different operations on your actual data set now by default
Power BI does perform certain amount of operations on your data while it's loading itself.
So these steps can be viewed here Okay,
so any operation that you don't want Power BI to do can be removed here or let's say if you are performing any operation Then that also can be removed from this option here.
So every step that you perform gets added to this applied step option So for now let me just close this and let's go back to our
So first, what we'll be doing is we'll be trying to create a sample visuals, okay?
So this basically will give you a feel of how power we have works.
So what we'll be doing is let me call it profit leaders here.
So to rename any view report, you can just double click there and let's call it profit leaders.
Okay, now what I'll be doing is I'm going to create a combination of my different states
as well as the profits that each of them have bought.
So let me just bring in the states, okay, so this basically is going to create a default visualization.
Okay, and since it's states what it's doing is trying to put it on a map wishbook.
Okay, so for that what I'll do is I'll add in the profit details.
as well.
Now when I add the details of profit, what happens is it sees the difference with respect to the size.
So let me just filter a more.
I'll add in details with respect to the country as well.
So this basically will filter out all the data.
So let me just bring this up.
And here you can see the profit across different states that I am making in different countries as well, okay?
So this basically is a geographical representation of all the profit that I'm making across the globe, okay?
Now me show you how to filter out the data as well, okay?
Now you come down in your fields option, you have this option called filters.
Now in filters, if you click on country, okay, you can see all the countries present here.
Now if I just select one specific country, so let's say let me take United States, okay?
Then what happens is my complete visualization changes with respect to United States alone.
Okay, so here basically it becomes with respect to United States alone.
Okay, so all the profits that you're seeing right now is with respect to the different states in United States
Okay, so if you just go down over them, you can see the different profits And you can see some states are negative.
Okay.
So what happens is I need to identify which are my positive states and which are my negative states.
So what we'll do is we'll try to change the colors with respect to which they are represented.
So for that go to the next field that is the format field.
Okay.
Now here you have the option of data colors.
Now the same size with respect to the profit scene can also be represented in a different
map based system also that's called field map system where and what actually happens
is that the color intensity of each of the states is with respect to the value of profit that they make.
Now let's say if you want to variate the value to identify the regions which make profit with format tab here under
data colors option you have something on us divergence.
When you turn on divergence what basically happens is there's a variation with respect to the color.
So let me just change this color setup as a whole because using right now.
So I'll make the minimum as light green.
I'll make the middle as the medium green and finally the maximum as dark green.
So this basically helps me get a better idea with respect to this as well.
Now let's say if you want to go more detail let's say the minimum or let's say the center
okay I'll set it as zero okay that means states which are not giving me negative profits are
center okay states below that should get let's say I'll give them a blue color okay
center let me give it a different color let me see yellow and the maximum profit making states are angry So,
here it starts from blue and when you look at Texas it has a complete negative value.
These my mid-level profit-making state, but whereas California is giving me one of the highest profits similar to New York as well.
Okay, so you can see the variation with respect to the color and how we have not related with respect to that.
So I hope you guys have got a simple feel of how to create a visual with respect to Power BI.
Okay, I have another question here.
It's how do you know which visual to work with?
Okay, so by default you get multiple date kinds of visuals present in Power BI itself and there are a lot
of custom visuals as per.
So it is completely left to you as a representative as to what a form of visual that you want to represent it.
Certain data that may be represented in a pie chart would be very helpful.
But when I bring it back to a bar chart or a line chart, it may not give me a clear cut picture.
So it's finally in your hands to decide which kind of visuals to use.
Are you clear?
Okay, so let's go back to our presentation and start with
the Now the first thing we need to identify is the overall trend with respect to our sales
and profit as well as get an insight with respect to the different regions and identify the regions which are profitable and non-profit.
So for this let me go back to our Power BI and let's begin by creating our first insight.
So let me begin by creating a new page for this report.
So click on this option plus here to create a new page, okay.
And let me call it overall performance.
So double click and then we set.
Now what we need to do is that we need to identify the sales and profit.
Okay, so for that drag sales from here, drop it on your workspace and similarly drag profits.
Okay.
By default what is happening is you are creating a cluster column chapter.
Now you can change it as per your requirement.
So let's say you want to create a line chart.
So the sales and profit data gets converted to a line chart here,
but since we don't have a second parameter or the axis based on which these values have to be
mapped It's just giving me two points.
Now.
This is a sum of sales and sum of profit that I have made on a given interval So for that what I'll do is that I bring in the order date.
That is the date on which it was ordered Okay, so when I said order date here, you get different parameters here.
You have your water month and day.
So let me remove here I'll make remove month and day and I'll make it in terms of court.
So it is giving me in a reverse order that is with respect to the difference.
So you can see here it is giving me a descending order as to the sales and profit.
Now this is basically because it is getting sorted with respect to the quarter to change that just click on three dots here.
Okay, then you can see it by default it has set sort by quarter.
So you can change it sort by sales or sort by profit.
So you can see here by default it is giving me quarter four quarter three quarter two and quarter one.
Okay, so I want it in ascending order.
This is something that is set by default.
So to change that just click on three dots present here.
Okay, you can see it is following a descending order.
So I want to make it in ascending order.
So just click it again and then you can see it changes into an ascending order.
Well, it's quarter one quarter two quarter three quarter four.
Okay, so the size seems a bit small.
So let me just increase that as well.
So let me make it empty.
Similarly, let me increase here as well on the y-axis.
So this is basically how you can increase the size of the font that are present in this way to the different axis.
So I hope this is a more clear.
So you can see with respect to quarter one,
my sales was close to 2 million where in I had made a profit almost about 240,000 dollars.
But when I went to quarter two, it increased my sales reached about 3 million and my profit equally increased to 325 million.
Finally, by third quarter, I had a sale close to 3.5 million but a profit at the same
time was more, it was about 400,000 dollars.
Finally, in my fourth quarter four, I had a sale close to 4.3 million and at the same
time, a profit of Okay, so let me just increase the legend size as well because this is your legend.
So it helps you identify which is my sales line, which is my profit line as well.
So here again,
let me make it let's say 20 itself and let's say if you want to change the color of the line as well,
you can do that in the data color option here.
So sales can let's say if you want to give it a blue and for profit.
I'll go with red.
Okay, so this is a blue and red combination that is happening here.
Okay, now one thing you need to understand is this is a combination of the different quarters data.
This data as I had mentioned is a collection of number of sales from 2011 to 2015.
Okay, so here what happens is all the dates get grouped up with respect to different quarters.
So this value that you see,
okay, this basically is a summation of all the sales and profit you have made in the first quarter for the year 2011, 2012, 13, 14 and 15.
Okay, so this is a complete combination with respect to that sale.
Okay.
That's the only reason that you have four points as well.
But if this was not happening, then you would have 20 points rather than four points.
But now what is happening is it's combining the different quarters, then it is helping me understand what is happening exactly.
Now, let's say if you want to filter this data a little more.
So let's come back to our field stamp.
And here I come down, you can see here the different options already present here.
You can see with respect to the different quarters, you can see with respect to different profit as well as sales.
Now let me bring in something interesting here.
So let me bring in my category here as well.
So just drag and drop category here and it gets added.
So let's say I want with respect to only furniture.
So if you select furniture, okay, the visualization completely changes.
So earlier when I was making 2 million sales, now it's only $670,000.
These are actual sales are not and my profit is close to $50,410.
So again, this basically radiates because this is the details with respect to my furniture.
Now let's say if I remove furniture and add office supplies, then it slightly changes with respect.
So I guess we have with respect to how our data visualization is created and how we've achieved the first insight any doubt with respect to that.
Now if you remember this insight is not complete because we need to identify this with respect to the different regions as well.
So what we'll do is come back to our filter option.
Let me just select all.
Minimize this and let me add in the region option from here to my visual level filter.
Okay, so what happens here is that I can see all the different regions present Okay, so here these are the different regions.
You have Canada, Caribbean, Central Africa, Central America, Central Asia, Central US, Eastern Asia, Eastern Europe, and so forth.
So you have different regions as well.
So what we'll be doing is, so let's say I want to see it with respect to Eastern Asia.
When I click on Eastern Asia, the visualization takes a change again here.
So with respect to Eastern Asia, I had made a 150,000 words sale and I had made just a profit of 30,000.
Although it grew in my fourth quarter to almost 300,000 and 60,000 profit, let me check another region.
Let me come down.
Let me see the eastern US region.
Okay, so here you can see a huge growth my first quarter.
There was just a 65,000 dollars sale.
But when I come to my fourth quarter, it's still 300,000 and my profit is 44,000 only.
Okay, so this helps me identify which are the regions that are not doing well and which are the regions that I can put my interest to our market, my products more.
Okay, so if I again come back here, if I say central America, So you can see here it is not a normal growth.
So I had 150,000 in the first quarter,
second quarter is almost the double, third quarter is close to the second quarter itself, but my fourth quarter is really high.
So this means that there has been a growth in the fourth quarter as well as the second quarter.
So this basically calls in more investigation.
So I need to try to understand why there was not much growth in the third quarter,
but at the same time what led to the growth with respect to these two?
Okay, so I hope this helps you identify what are the insights also that you can receive from these visualizations.
So move forward.
Let's look at the second insight that we need to achieve as part of today's session.
We need to identify the performance of different states.
Okay, so for this, what we'll be doing is we go back to our API and let me create a new page.
I'll rename this state performance, okay.
So here what we're going to do is first we're going to create a visualization and then we're going to give it the input.
So let me select the visualization that is a scatter chart.
Again, you can see it has been added here to this what I'll do is I'll take my sales from
here added to x-axis,
I'll take my profit, add it to my y-axis, and then what I'll do is, I'll add my state details here.
So this basically becomes a scatter plot here.
Okay, now what I'll do is let me come down.
Let me add a filter where in my country is just United States.
So this basically will give me a state wise representation of the different profits I make in United States.
Okay, so this is basically a scatter plot between my profit to my state.
Okay, so you can see here the highest profit to sale ratio is from California when I have
almost 460,000 worth sales and a profit of 80,000.
At the same time, this is followed by New York.
New York has almost close to $311,000 worth sales and it has a profit.
So it's almost close because these regions have a huge difference with respect to sale amount,
but the profit is almost same in New York Okay,
but when you come down to the switch it the next state that is following it is Washington now Washington What is happening is that I have just hundred and thirty eight thousand worth sales and
my profit But when you see this line, right, this line is what you need to concentrate on.
Let me first increase the size of the font here.
So, go back to our filter.
Okay, yeah, this should be Now, if you see here, these are the regions that are giving me negative profit.
That is, I'm wasting my money here.
Even though these regions are bringing in sales, but they're not bringing in any profit.
So if you look at the state of Texas, I have almost 170,000 worth sales coming there.
But my profit is in a negative figure, which is minus 25,000.
That basically tells me that I'm investing way more than making a property.
So become the regions which I need to concentrate more okay that is these are my highest priority states okay apart from that let's see you want to add
a specific trend line here.
So for that you need to come to the analytics tab here.
So trend line basically is the reference line that you can set based on your overall group.
So this is your standard sales-to-profit group.
So anything above this is definitely good for you, but anything below is not really good.
Now, let's say you don't want to try.
Let me remove this, let's come back.
Let's say you want to add an x-axis constant line.
So let me add the value of let's say which are the regions that come below the x-axis value of 100,000.
So these are the regions that I need to improve my sales.
Anything that is on the right side of this definitely is doing good in terms of sales.
But same time, let me add a y-axis constant line.
So let me keep the value of the y constant about 22,000.
Okay, this is basically a rough number.
Okay, so anything below this and the intersection this area, right?
This is supposed to be my highest priority area.
Anything below this definitely requires an attention.
So if I divide them into different coordinates.
right down here, let me call this one, let me call this two, let me call this three, and let me call this four.
Okay, I'm sorry with respect to how it looks odd, but I hope this gives you a general idea here.
Now here what happens is quadrant one does not need any attention because these are my high
priority quadrants and anything here is also bringing me good sales and good profit as well
quadrant two can improve itself it can slowly move to quadrant one so I need to concentrate
more with respect to the sales okay so it is bringing me profit but again I need to increase my sales in this region.
Quadrant 3 is the biggest mess here.
Even it is bringing me good amount of sales, but the profit is negative.
This basically means that I'm throwing away my money in these regions.
as Quarter and four are the regions which are performing moderately okay, but in terms of numbers, they are not really anywhere.
They neither crossing my threshold in number of sales nor are they crossing my threshold in number of profit.
So, this is going to be my second region of interest.
My first priority is going to be the regions in quarter and three and my second priority
is going to be the regions in quarter and two.
Slowly, if I have time, I can try pushing the states in quarter and two to quarter and
one and quarter and one is always above my benchmark.
But this does any attention to this region.
Okay, you can always come up with new ideas to increase them beyond.
Okay, maybe some understanding as to why only these three regions are making this may help you grow with respect to all your quadrants as well.
Okay, so these are just some of the various insights you can get from these.
So, are you guys clear with respect to the insights that we have achieved here?
Any doubts with respect to what I have discussed?
Great.
So, the third insight what we will do is we will try to identify a segment-wise performance.
We will see how each of the segments are doing in the different states.
So, let's go back to our webinar.
Now let me create a new page here and let me bring in the categories here okay so rather than taking
the categories what I'll take is the sub-categories this will give me a broader idea so I'll bring
in the sub-categories okay and I'll add to it the number of sales so this basically is going to be represented in it.
But a table form is not really helpful for me.
So I do is I put it in a clustered bar chart.
So when I do it in this way, it becomes a clustered bar chart.
Give me the different sales made by each of the subcategory.
OK, now what I'll do is I'll read it a little bit.
OK, now that this what I'll do is I'll just bring in the profits.
as well.
Okay, so I'll add the profit into the color saturation option.
Okay, so this you can see has variated.
Now if you go back to your format option, you have data colors here.
Okay, so here let's again enable divergence.
Okay, so let me make it light green.
Let me make it mid green.
So you can see with respect to which are the categories that are giving you profit and which are giving you seats.
So if you see phones are the regions with respect to the maximum sales.
However, copiers are the regions with respect to the maximum.
of.
Okay, so this also gives me a different insight with respect to how these regions are performing.
Okay, so this again can be represented in a different way.
You can also try this out with a pie chart, but this is my suggestion.
So here's a homework for you.
Why don't you try representing the same chart in a different way and it will be interesting for you as well.
So let me just rename this finally once more.
I'll call it segment performance.
Okay, and let's go back to our presentation and let's see the final insight that we need to get now
This basically is to identify the revenue generated by each category of the product.
Okay, and Identifying which category of product is ham pre-made growth.
Let me create a new page So I'll call it revenue generator Okay,
so who can tell me what all the different data that I need for this visualization?
You can take a guess guys.
Okay, yes, so I need the category definitely so let me put in category.
I need my sales, I need my profit.
And finally,
let me also bring the order date, but here what I'll do is in my order date, I'll just take here instead of months.
This will help me understand how the different category of products of function throughout the year.
So what I do is I'll just read it a bit.
Let me take a line chart graph.
So here what happens is you can just see two things that is with respect to furniture office
supplies and technologies the sales to profit ratio.
Okay.
So let me just full screen this now here what happens is you have the option of going to the next level of hierarchy.
So if you just click on this what happens is it becomes sales and profit by here.
Okay.
So you have sales, profit by here.
profit distribution but when you go out what happens is it's the profit made by
each of the categories overall okay now I'll just bring this down So,
here what I'll do is I'll just remove the categories from here and then it just becomes a sales to profit ratio.
Okay.
That is the sales and profit made in different years.
Here what you can do is that you can actually filter it.
So to my visual level filter what I'll do is I'll bring in the categories.
So this basically will help me identify the sales and profit made in each of the year.
So let's say for furniture alone,
this is the sales to profit ratio made in each year 1213 14 and 15 okay similarly with respect to office supplies to get a number let's say furniture and office
supplies So this is a different number and you can work around with respect to them
So this will give you an insight with respect to all the three categories at the same time
You can also compare multiple categories together Okay,
so this rather than applying it directly to the visual can be added as a filter to your visual.
Okay, so with this we've achieved the four major insights that we're trying to achieve at the beginning of the session and we've created multiple reports for this.
Now it's time that we want to create.
Before that what you need to do is that you need to save this file.
So let me save it.
So let me call it my first dashboard.
Okay, so here when I save it, it's going to be saved in form of a dot pbix file,
which is supported file from Power BI.
Now that I have saved it,
it's time that I publish it to my second Power BI interface that is Power BI search that is going to be the interface where we will be creating our dashboard.
So for that, what you need to do is that you need to publish.
So this is one of the major reasons that I had mentioned that you need to sign up for Power BI.
Okay, because this is which is an online browser based interface and that's where we want to create our dashboard.
Once you click on publish.
So you can see it's publishing to my power be a service.
Okay, so here itself use get the notification that test successfully published it to my power beer and click on open power beer dash.
Okay, so you can see here the browser is opening this report.
So you can see here it has been opened as different pages for a report.
Now let's say if you want to edit this report you can do that on power beer service as well.
So just click on edit reports and you have almost the same features present here as you had power in power beer desktop.
But again what I need you to understand is you cannot manipulate the data that is associated to this report.
To do that you need to work it on your power beer desktop not on power beer service.
Okay, so here let's say let me remove this.
So this is profit region with respect to all.
This was the first sample representation that we had created So this also has a next level of hierarchy if you see,
okay, so what is happening here is that it is considering the whole profit of United States rather than individual states.
So if you go to an upper view, it basically becomes this.
So that in turn would be helpful when you're working with different countries.
Okay, so we had set a filter here that the country is just United States.
Okay, now if I remove this, okay, then it sort of becomes a mess.
So if I do that, it happens to be a global level representation.
Okay, so that time it becomes slightly hard for you to identify which.
So that time what happens is you need to see the bigger picture here.
So you can drill down to your official report.
it becomes a representation in terms of a global scale.
So earlier what was being represented as individual states now becomes with respect to different countries.
So now it's time that we begin by creating our first dashboard, okay?
So you have the option here of reports dash.
So you have the option of workspace.
So can work with multiple workspaces.
Okay, so by default you'll be working in my workspace and under that you
have first report that I had created earlier and the present report that you were
working with that is my
So you can also load an Excel workbook here in case if you want to use a workbook for different
visualizations and apart from that you have the data sets associated to the different reports also present here.
Okay.
Now let me show you how to create a dashboard.
Now creating a dashboard is quite easy.
All you need to do is just click on the pin which will option.
Okay.
And it'll ask you whether you need to pin to an existing dashboard or create a new dash.
Now, since there are no existing dashboards, I'm going to create a new dashboard.
I'll call it my first dashboard and I'll pin to it.
Okay.
Similarly, I'll take my state performance, okay, I'll pin it to my dashboard.
Segment performance also goes to my first dashboard.
So you can see here by default, it is showing me it is in dashboard present.
So when you work with multiple dashboards, you can select it accordingly.
Go to Pin, let me take revenue generated also, I'll pin it there.
Okay.
So with this we've pinned all the visuals that we have created to our dashboard.
So let me show you how the dashboard would look like.
So here you can see now there's an entry for my first dashboard.
If you click on that, You have all the full visuals that you had created as part of your dashboard.
So this is what a dashboard would look like in a web view.
But let's say if you want to see it in terms of a phone, that is if in our power.
So just do the same, you just can change it here.
So this is how it would look on the phone application.
So this would again radiate with respect to how it is set on your phone.
The screen size and everything would magnify the image.
But don't worry.
Okay.
Let's go back to the web view.
Now here let's say if you want set it as feature.
So then you have the option of set as feature.
So if you say set as feature.
This becomes your feature dashboard as such.
Then you have the option of favoriteing the dashboard.
So when you're working with multiple dashboards, then it becomes easy for you to manage with it.
Then you have the option of sharing your dashboard.
Now, to do that, you need to upgrade your account to Power BI Pro, which basically would cost you only $10 a month.
They do have a 30-day trial period as well, but since I've completed mine, I presently is being disabled.
So once you do that, you can always share this report to anyone.
Now let's say if you want to reorder this as well, it's quite simple.
It's just a drag and drop operation.
So let me show you one of the most unique feature that Power BI dashboard allows you to.
This basically is your Power BI QA.
So let's say if you want to have any idea with respect to your data.
Let me ask which state has highest state.
So this tells me which state is bringing me the highest sale.
So it's coming from England.
Okay.
And accordingly it has given me a wish with respect to all the states.
That is it's giving me the maximum first and correspondingly it is following a descending order.
Okay.
So let's say which state has the highest profit.
Let's see with respect to profit.
So let's say profit.
Okay.
Now again you can see it is changing here.
So it's England again here but the values have changed.
Okay.
So if I say profit and sales so now it has completely changed it's giving me a Okay,
but this is slightly different from this category that we have created.
This is on a global scale and this basically is an insight that is being achieved through Power BI's Q and A feature.
So for any dashboard that you create, you can use Power BI's Q and A feature.
I you can simply just put across your queries and Power BI will create a data visual and give you a complete insight.
Today we are going to discuss two of the most talked about tools in the business intelligence and data visualization market.
referring to Power BI and Tableau.
But before we get into the details of these two tools,
let's quickly take a look at today's agenda like we always do in all of my sessions.
Well, I would be discussing these two tools based on these parameters that is visualization, cost of ownership, integration, data management and finally functional comparison.
Now for functional comparison, I've again jotted down few parameters and we would be discussing these two tools based on those parameters as well.
So let's not waste any time and get started with this discussion.
Then visualization will it completely boils down to your preference.
So let us take a look at these two tools one by one.
First we have Power BI.
Now, if you're looking for something called as custom visuals then Power BI is a clear winner.
Why?
Because it has opened up its SDK for visualization and that has given you more custom visuals.
Plus it has great drag and drop features.
It has good data import capabilities.
That is why if you're looking for custom visuals any days, Power BI is a winner here.
visualization.
If you're somebody who likes the curated approach or more clean, sleek kind of an approach, you can always go for Tableau.
Why is that?
Because it would give you great drill round features.
It would give you amazing visualizations.
So as far as visualization is concerned, yes, if you ask me for my opinion, I would say Tableau is a clear scenario.
Next we have cost.
So when you take a look at cost, we have to consider something called as initial cost.
Now, if you ask about initial costs, Power BI events, why?
Because it is way cheaper compared to Tableau.
If you compare its desktop user cost, if you compare its web user cost, server node, Power BI is a winner here.
But then this is not the only cost you should take into consideration.
When you talk about business intelligence, you have to consider other costs, costs that can be considered on the longer run.
So are there any other parameters which we can consider for the longer run cost?
Yes, definitely.
And if you compare those parameters like labor costs,
total cost of delivery and all those things, this is where I feel Tableau is or has little more edge compared to Power BI.
Why?
Because on the longer run,
when you compare its labor costs,
its total usage cost and all those things,
even though your initial cost is more,
Tableau gives you more affordable kind of a software when you look at from the longer and perspective or the total cost of ownership perspective.
So if you ask me for my opinion again,
I would be choosing the longer and thing and for now Tableau is a winner here as well.
Third on this list, we have integration.
Now, integration again, it kind of boils down to perspective like the visual agent factor did.
This is because these two serve completely different functionalities.
If you take a look at Power BI, it gives you great integration capabilities.
How it acts more like a Swiss Army knife.
That is it readily integrates with various other tools.
Now it's a Microsoft product and there are various other Microsoft products in the market that various businesses use.
And since Power BI lets you integrate with these two tools,
it kind of has an Azure Tableau because it can integrate with various tools like you have reporting services, Excel, SharePoint and all those things.
So in all when you talk about integration capabilities, yes.
Power BI gives you a lot more options.
Tableau on the other hand.
more of a scalable kind of an approach or a certain like approach where if you're dealing
with a particular defined kind of a problem or you need more curated kind of an approach
then you should go for Tableau which gives you sleek and clean visualizations.
But if your main aim is integration, yes Tableau is a great tool, but Power BI has to win here.
So if you ask me for my vote, yes, it would go for Power BI definitely.
Next we have something called as data management now when you talk about data management,
you have to talk about data shaping data modeling data analytics and all those things.
Let's take a look at those one by one data shaping power bi great table.
It's good, but probably it's great.
Why it has something called as query editor that uses M language and basically it lets you do so many things with ease and you do not have to worry about switching into Excel every now
and then because.
You're probably going to take care of it there and there.
So yes,
it does help you and if you ask about Tableau,
even the people who use Tableau compliancing that this too much to earn for between Excel and Tableau.
If there was a solution for it, it would have been much better.
So when you talk about data shaping power bi events data modeling again power bi has to renew why DAX power pivot and that scale framework basically which it has.
when you talk about data modeling analytics again power way why because power is very fast yes
it does not have as clean and curated approach as tableau but overall data management if you ask
for my vote any day power be I Next on this list, we have something called as functional parameters.
So are those parameters?
Well, these are few of the parameters which I have gone ahead and jotted down.
We have the year of established.
Tableau had a great head start here because it started 10 years prior to Power BI, but Power BI is kind of catching up.
But if you talk about overall organizational approach for a data visualization tool,
Tableau has more experience compared to Power BI, but Power BI is definitely catching up applications.
Now, as I've already mentioned custom visualizations or more open source approach is what you're looking for.
Power BI is your thing more curated and clean approach Tableau.
So if you need something like custom visuals,
dashboards, you can go for Power BI, head of analysis and longer and operations related to data visualization, Tableau users.
Well, as far as my personal experience is concerned, and Tableau is a little difficult to learn when you compare it with Power BI.
Power BI is much easier to learn and it is for the wider applications given the integration capabilities it has.
But that is a personal opinion.
I don't want you to jump into that because people who have used more Tableau might find Tableau easier to use.
So that kind of boils down to your preference really.
If you ask me, I like Power BI more when you talk about Power I have had varying opinions for people as well.
So you are the best judge and you are the one to decide on those things support Tableau wins here clearly.
It has better support compared to Power BI scalability.
Good Power BI is good.
But Tableau is great.
If you talk about applications on the longer end, better scaling Tableau has to win here infrastructure.
Again, both take a completely different approach.
Your Power BI gives you SKAS kind of an approach,
which is software as a service,
whereas Tableau gives you more flexible kind of an approach where you're free to like or not free,
but more flexible kind of an approach basically.
So these are the parameters I felt that we should have discussed.
There are quite a few other parameters where these two tools can be compared.
And as I've already mentioned that these two tools are very neck and neck or very close to each other.
So these are are two two tools.
tools.
talked about those are the hottest tools when you talk about business intelligence and it
would be unfair to say this is a clear winner or that is a clear winner.
It actually boils down to your preference.
The best way for you to decide is to go ahead and use both of these two tools.
Those are readily available to you and very easy to learn.
So you can pick those and decide it on your own.
Which tool is good for you or great for you.
That also depend on the problem statements which you have or which you need to solve.
As far as this video is concerned,
I just wanted to give you a picture as in how do these tools fare based on these parameters.
interview questions.
So the idea is to,
over the next one hour or so, the idea is to walk you through some of the most commonly asked interview questions in Power BI.
So we're going to be focusing on a of conceptual topics,
a lot of theoretical questions and a lot of practical questions and the idea is to not only walk you to the questions,
but also to actually take you through a very,
very good demonstration and just to give you a very good approach and idea on how to answer some other questions.
Okay, so I'll try to connect a lot of these questions
to real life scenarios so that when you're asked a question next time in your interview,
you're not only giving out a theoretical answer, but you're actually able to connect with practical examples and use cases and stuff like that.
So we're going to try to make it as broad as possible.
And obviously,
the idea here is we have picked up a very,
very limited set of questions, but it's a pretty good and some of the most important questions that we have picked up for this person.
So first of all, we have broken it down into a few general subtopics.
So first of all, I'll be focusing on a few general Power BI questions and obviously what is self-service business diligence?
So needless to say Power BI, the very fact is that Power BI is one of the most popular SSBI tools in the marketplace today.
And of you who have worked on other AI tools coming from the world of Tableau or Glixens or Spotfire,
you can also relate to this.
So if you're asked a question, what is self-service BI, I the first approach to take is to explain what is SSBI.
Okay.
Now, any of you, as I said, if you have come from the world right?
If you have worked on tools like SSRS,
Cognos, if come from MSBI world or if you use the business objects,
you typically have used tools where it's not exactly built for end users, right?
So building reports or building any kind of project used to take a lot of time.
Obviously for developers,
it's a very easy tool to use,
but if you think of end users at the end of the day,
If you of the business users or the end users or non-technical people would find it very,
very difficult to use something like say a visual studio to develop MSPSRS reports.
Okay, so that's the world of traditional where development cycles were long and there's a big hindrance, right?
So if business users want answers to certain questions, let's say if a mutual fund manager wants to know.
How has my fund performed over the last 10 years in a very simple line chart that mutual fund manager has to go back and depend on his internal IT team to go back and build
that chart for him.
It could be a very simple line chart.
But as I said, the mutual fund manager may not be that well-versed in a traditional BI to like a necessary.
Okay, so they will be depending on the internal in-house IT team and that.
way the overall process will become pretty slow because the IT team will have their own requirement gathering process,
they'll have their own software development framework which they'll follow and probably a very simple activity which I highly have taken less than
10 seconds to build right on paper,
line chart is so simple but then it could I easily take to three weeks depending on a lot of factors.
So this is exactly where self-service BI comes in and self-service BI is an approach to data analytics that enables business users to filter segment and analyze their data and this is the key
where the key is ready to focus on business users and you can either say business users or you can say end users customers.
So essentially non-technical people who are the real consumers of this particular tool.
Okay.
And what is the benefit of using this?
And if you just compare to this scenario,
just I discussed just a while back now here,
the mutual fund manager can directly use a tool called Power BI and he can directly connect to the data.
He can directly build the line chart all by himself.
Okay.
So obviously the development cycle will be much faster and the business user will be happier because you know, he has control of his data.
he has control of exactly what he's seeing and he's not dependent on any external team.
So it's a win-win approach.
It's a win-win approach because the end user is happy.
The IT team is also happy because sometimes what happens in traditional BI projects,
especially if there is a requirement involved, typically requirement gathering phases as we have all encountered, right?
There are so many iterations that take place, right?
You would require one gathering.
You do one phase of time.
common gathering and then something misses something gets missed and you come back you
build a product and the customer is not happy with the products you go back and build
it again you iterate over it right so those kind of problems can crop up especially in traditional
approaches but that is something that's completely out of the picture in a SS BI approach because
the business user himself or herself is building the report and there is no question on dependency and whatever they want they are building.
So essentially it's a win-win situation.
And as I said,
it's a very easy process and anybody who has basic understanding of data can create reports to build intuitive and shareable dashboards.
So when you answer this question is very important to get the understanding, give the basic understanding of what are the challenges of traditional BI.
What are the challenges of some of the other top BI tools and where does SSPI fit in?
It shouldn't be a.
a very answer just that,
you know, what is SSBI in the Power BI as an SBI tool, but try to be as broad as possible.
Try to give context on where SSBI comes from, why is SSBI important?
And what are some of the other tools?
Remember, Power BI is a very small part of the whole landscape.
The BI landscape is dominated by our tools, like Tableau, like Spotfire, like ClickSense in the Data Visualization SSBI landscape.
And if you talk about SSBI in analytics is also tools like
all tricks which have really revolutionary and you can take
these examples and that will really get to show your depth and breadth of knowledge in the entire bi space.
Okay.
So moving on next question.
What are the parts of Microsoft sales service bi solution?
So as I mentioned the SSBI question in the beginning was purely a very general question.
Now we are being more focused on.
Microsoft stack so we are focused on the entire Microsoft stack and obviously when you talk about the Microsoft stack we are talking about primarily two toolkits
here So one is obviously the excel bi toolkit and the other is the power bi toolkit Okay,
now some of you might be wondering why am I using excel bi toolkit?
So if this question is asked to you in the interview, what do you actually say?
Do you actually mention excel bi do you mention power bi because obviously we are discussing power bi
So it's natural to be talking about Power BI,
but it is also very,
very important that you mention Excel BI because remember Power BI is nothing but an extension of the Excel add-in components, right?
So if any of you have worked in Excel and I'm assuming
all if you have worked in Excel and I don't mean to say working in Excel in a spreadsheet application.
So obviously Excel is a spreadsheet application.
We have all of us that use it, even if you don't, if you say that.
we don't work in Excel, all of us have seen Excel spreadsheets, right?
But what I really mean to say is Excel from the standpoint of some of its add-in components.
Some of it add-ins components like the Power BI components of Excel, like Power Query, Power Pivot, Power View.
And when you think about these components, what are they?
They are actually helping you build BI solutions right with an Excel.
Now I know it comes to this.
surprised for many people initially to believe that Power BI actually came from Excel, but that is the truth.
When you think about the origins of Power BI, when you think about how Power BI came into existence, it came into existence from Excel.
So whatever you have in Power BI is nothing new, right?
So all this components have already been there in Excel for a long time.
Okay.
So when you think of Power BI, Power BI, BI.
what was actually launched as an add-in.
Can you think of our query?
Again, where he was launched way back.
Power is again a very, very integral component of Excel.
So all these components were already existing in Excel.
And all that Power BI desktop did was it repackaged all these components which they now call Power BI desktop.
Okay, but it is very important to understand that they were all already there.
So the obvious question that I.
sometimes faced from candidates is, you know, why did Power BI build a tool like that?
No, why did I build a tool like that?
If Excel had already had this component that why not use Excel and the challenge was that.
And this is I'm sure most of you who have actually worked in these tools would have face similar challenges is in Excel.
You have so many different versions,
you know,
if you especially if you are 2013 2010 version 2016 version and within each version,
there are so many different editions and then the other thing is that these adding tools are not exactly part of Excel.
There are different releases.
You have to separately add them.
You have to install update.
So all those challenges are there.
So if you're migrating if you're moving from one edition to another edition, the compatibility issues crop up.
So all those problems.
use to happen with this particular stack.
In fact,
as I told you,
and as I'll show you in some of the upcoming discussions that all these components, the behavior of these components are exactly the same.
When compare the Excel BI components and when you compare the Power BI components,
that is the Power BI Desktop components, they are exactly the same.
There is no difference at all.
Okay.
Barring of you, very, very minute differences, they borrow more most of the features, right?
However, as I said, the idea of using the Power BI
next up was to package it in one single solution tool and present it to end users, which is just much neater.
It's just much easier to use.
It's an overall, it's a just a good solution.
It's just a clean solution that Microsoft has built.
Okay, so again, coming to the question is very important
that you mentioned about Excel BI because most people will actually go back and say,
okay, Microsoft says service BI to solution will only be Power BI, which is obviously that's Power BI interview.
So you'll actually say only Power BI, but actually it's no.
So you should mention that it actually came from Excel and Excel, by the way, is also a very, very powerful self service BI solution.
Because remember, even without the Power BI components of Excel, even without these Excel components like Power Query, Power BI, Power BI, Power pivot.
Think of a very,
very simple use case where you have a simple spreadsheet application,
a business manager has a very,
very simple spreadsheet application and he has some data on index health spreadsheet and all he wants to do is create a very,
very simple chart out of bit.
Can he not use the charts thing functioning Excel answer is yes, right?
All he will do is he will connect to his Excel data.
He can simply create either a pivot table or he can create a pivot chart and you can analyze that particular data right with an Excel and I'm not even talking
about Power Query Power Pivot and Power View.
I'm talking about simply creating pivot tables and pivot charts in Excel, which you can do without using any of these components, right?
And when I mentioned about that particular use case,
what the business manager is actually doing is that business manager is actually performing self-service BI.
self-service BI right with an Excel, okay?
Hang on, what is Power BI desktop?
Obviously, Power BI desktop is the free desktop application
and practically this question can sound a very,
very generic question and they just want to test out your overall understanding
of the tool that we are obviously going to do much of our development in, which is the Power BI desktop tool.
So it is very important to a few keywords are very, very important.
One is obviously it is a desktop application.
One other keyword that I would really like all of you to use is the client tool.
It is a client tool, okay?
Remember Power BI,
the entire architecture of Power BI is a client-server architecture
where you have the Power BI desktop which is sitting in the middle as a client tool.
So Power BI desktop is where you actually do all your development stuff, okay?
That is where you actually build your reports.
You actually build your build all the cool visuals,
you actually build your model right inside Power BI desktop, and then you publish it onto the cloud, which we call the Power BI service.
So it's a client server architecture.
So mention those words, mention those keywords, mention those terms.
And the other thing that you should also mention is,
and again, as I said, when I asked a question on what is Power BI desktop, just focus a bit on the entire, architecture generally.
Okay, so they may want to know what is the desktop, but if you talk about the service that just
goes to show that you're overall having a pretty good breadth of knowledge and
Just just give them a picture just give them a don't mention about all the components
But just mention about what is the desktop and and the service at least mention about the desktop and the service generally Okay,
and I think this is also a very very key part the second point where we are saying that power be a works Cohesively with the power be a service and as I mentioned here
Just just mention the part that this is the client and it's the server
essentially What are the Power BI components and this is again a very very important question and it's more of a follow-up to this initial question
So sometimes they might want to ask a more specific question like what is Power BI desktop and remember when I asked this question
You should focus on desktop and the service
But now this is a much more broad base question where they want to know your detail knowledge on Power BI
What do you understand about the entire ecosystem of our and this is what the complete architecture is all about right?
You can see that it consists of our query power pivot power view.
These are obviously the add-in components Okay,
these are the the power components then you have our map data Power BI Q&A is another revolutionary feature,
natural language querying, which I'll just talk about in a while and finally Power BI service, okay?
So mainly theoretical.
What I would suggest is just give them a picture.
Just give them the names of these eight components and just give them an idea of what each of these components mean, okay?
So if asked a question like this, just focus on power BI again.
When asked a question like this, just focus on power BI give a very, very brief discussion.
The description Power BI probably just probably mentioned Power BI is a self-service BI tool and it has obviously these are the main components.
You have the Power Query, Power Pivot, Power View and all these three are part of Power BI desktop.
So all these three you can just club it as part of Power BI desktop and then your Power Map,
which also is a part of Power BI desktop.
And then finally your data catalog management gateway Power BI Q and A and Power BI Q service, which are obviously the service components.
Okay.
Obviously, there are a few things which keep changing in the overall ecosystem.
So remember, one thing you have to understand in Power BI is that the product is very, very frequently updated.
So for instance, the Power BI desktop gets updated every month.
So every month Power BI desktop,
it shoots out releases every month and the Power BI service which obviously lives on the cloud that shoots out updates like every week or probably multiple times a week
Okay, the new features getting added every single time and the reason why I mentioned this is because of the q1 day feature
Remember q1 day feature for a long time has been on the service and I just mentioned a while by the q1 day feature
Isn't the service it is still in the service but then in the desktop it has very recently been included as a preview feature Okay.
So when you talk about these things,
if you have an understanding of what are the recent updates,
you know, what are the recent releases that are coming from the Power BI stack.
If you keep yourself updated, what is the January updates?
What are the December updates?
And if you understand this,
the length and breadth of everything that's happening in the Power BI landscape,
I think that will just add more value to your responses and the answers to the questions that you give.
So just as an example,
I'm For this particular question,
if you're talking about Power BI Q&A,
one thing I will recommend is just mention that it's a service component,
but also mention that off-lane Power BI has included that as part of the Power BI desktop.
Okay, so now you can actually go to Power BI desktop and you can shoot Q&A features directly from there.
And I know I haven't talked a lot about this yet, but I'll just...
quickly connect to my data here in power bi and as you can see,
I'm just connected to my power bi desktop interface that I've opened.
Okay.
And I can very easily connect to my Excel file.
See how easy it is to connect to an Excel file here from power bi desktop directly.
And I will very easily connect to my Excel file from here.
I'll just load this data.
And now you get a very simple interface from where I can choose the sheet names.
So this is an Excel workbook where I have three worksheets, orders, people and returns.
I'll select the order sheet.
Okay.
The edit is basically for opening up the query editor.
If you open up if you click on edit, it will open up the query editor where you can edit your queries.
Okay.
And this is basically what you're going to get.
You're going to get data in your power pivot model.
And now you can go back and visualize your data.
Okay.
So if I want to remember the example of that fund manager,
whether the fund manager wants to see how much profit he has got,
which based on every day, you can see how easily just fit three clicks.
He can generate a very, very nice line chart.
Okay.
And you can see how easily we can build dashboards in Power BI.
Okay.
And how powerful it is as a service BI to.
Okay.
And just to come back to the question on q1 day feature and by the way,
the preview features are turned on and probably a desktop in this particular site.
You can go to file options and settings, go to options and you can see all the preview features will be listed out here.
Go to preview features and you can see all the preview features listed out here.
You can see I've turned on all the features as Spanish because I don't know Spanish so I can't turn this on, right?
All right.
So these are all the preview features and if you just double click on it,
see something called Q&A is listed out here and you can just double click on this particular thing and you should be able to see that a very,
very familiar Q&A feature which comes up in the service.
I that same thing is now built into the next stop.
Okay.
And I will talk more about the service later on guys,
but just to bring this up as an answer to this question, I just wanted to quickly cover this with all of you.
So you can just double click on this and you can.
Some question like you know sales by Region pretty cool, huh?
I'm just typing it and if I want to see it as a pie chart.
I can just a pie chart.
Okay, so just by typing, I can see exactly what I want to look at and it gives you some suggestions.
It's more of a gives you some suggestions here as you as in when you type the values and you can see for a look at the matrix want to see this as a different kind
of visual.
Let's say as a as a table, it gives me all these options.
Okay, so that's the q1 day feature that is built right inside the Power BI desktop as a preview feature.
And when you answer this question,
the other important thing that I do want to mention here is just giving a brief description about all these components.
Remember guys, Power Query is the ETL component of Power BI desktop.
So that's an ETL component.
So the idea in Power Query is you're taking your data.
You're connected to various sources and you're performing a very, very basic ETL operation there inside Power Query, right?
So the idea is to just perform basic data cleansing operations because remember data from the source is never in the right shape.
It's never in the right format.
Okay, it's what we call dirty data.
So data from the source is always dirty.
So you want to always clean it.
want to perform some basic transformations before you use it for your actual analysis, okay.
So that is the very, very first step in power query.
Okay.
So if you remember what I've done as the very first step in power BI is I connected to my Excel file.
Right.
And the way to open up query editor is you can click on edit queries here or you could have clicked on edit in that particular dialog box.
I got just a while back.
You could have directly clicked on edit there.
Okay.
So click on edit queries here.
And once you do that, it'll open the query editor where you can go ahead and edit your query.
Okay.
You can go ahead and perform basic ETL operations.
Obviously, there's a lot that is here.
We're not going to cover everything,
but at a basic level,
this is just to just for you to understand just to give you some context as to what Okay,
and just a simple example,
you can probably give some simple examples here where let's say you want to,
you're looking at the orders table and you don't want to look at all these data.
So you were removing some columns, let's say here, you can record and remove all these columns.
You don't need all these columns.
So you to remove the ship date,
so you don't care about ship date,
you don't care about customer ID,
and see even if of operation whether it's a remove column or if I do an add column,
not just here, I can also go back and do an add column.
So I can actually just come back here and I can add a column.
I can add a custom column.
It's just like adding a calculated column.
Okay.
So I can add a column called cost and cost is going to be equal to sales minus profit.
Okay.
And that's get added as a column and that again gets added as a step.
So everything in a query is.
is basically a step, okay?
So, mention these keywords that you build queries, query editor and behind the scenes you're creating steps.
So, mention these pieces when you're talking about this particular answer and also mention data types, very, very important part setting data types properly, okay?
You can see what's happening here is when I build that particular column called cost.
So, this becomes loosely typed.
It's not exactly in the correctly typed.
format.
ABC123 basically means that it's not properly typed.
So you have to go back and specifically set the type to decimal number.
Okay.
These are all the data types that are available and probably a desktop.
And so that's it.
That's all I have and what I will do now is once I'm happy with whatever changes I've done,
let's say I've performed all my data cleansing and data transformation activities.
I can go back and close and apply and once I do that,
what will happen is data will get loaded into my power pivot data model.
You can see what happens here.
It is good to get loaded into my power pivot data model.
Okay, so the first step is power query where you will perform the ETL
and the next step is from power query that data goes to power pivot
and power pivot is nothing but an in memory column database where your data gets loaded.
Okay, that's your data model in other words, we call it a data model.
You perform your ETL, you clean your data, and you load your data into the Power BI model.
And from there, you're going to visualize your data using PowerView.
That's the very, very basic process that we have in the Power BI desktop.
The same as we have in Excel.
Even you use the Excel BI components, it'll pretty much be the same thing.
And just to give you a brief glimpse of that, I'll not talk a lot about that.
for it.
I've opened up my Excel.
Remember, I've opened up my Excel, but I also have the add in components loaded.
So I'm using 2016 version of Excel, by the way.
So it's very, very easy to do the same thing in Excel as well.
So you can go to get data.
You can go to file.
You the interface is very similar to what you have in Power BI desktop,
which is why I actually drawn those parallels in the beginning of our discussion where I
said that both these components draw so much from each other okay and connect to that same excel file here
click on import it's connecting i'll get the same interface where i can connect to that particular
worksheet from that workbook it is still established in connection either same interface,
a very, very similar kind of interface opens up just a while back what we saw in Power BI desktop.
It is taking a bit of time to load.
And the idea is that the idea behind showing you this thing is just so that you're mentioning
these things when you ask this question so that you get an idea that,
okay, the same thing is going to be next Excel, Excel, what can do in Power BI desktop.
I'll just quickly go ahead and expand.
it select my order stable from here and you can see the very very similar options are coming
up load edit it will open up the query very similarly how it opened up in my power be
a desktop okay seeking processing my queries the concept of queries is very similar to
what we saw in the tender desktop and you can see it's opening up the query and again
here you can see that it the interface is very very similar to what we saw in power be
a text I remember is very easy to confuse that I'm actually opening the Excel but this
is actually Excel is not probably a desktop okay so I can do the same stuff I performed
just a while back in power be a desktop I can go back and remove my columns I
can do my order date a row ID see the steps are getting added I can add the custom
column go to add column custom column I can do pretty much the same stuff here
okay I can add that cost column which is gonna be sales minus profit click on
okay custom column added and now we can go back to home and say Okay,
and when you're closing and loading,
essentially, what happens is you're loading that whole stuff into your model, okay, and which is nothing but the power pivot.
All right, so it's very, very important that you have this understanding that power BI desktop is not to be learned in isolation.
It's not to be learned in isolation,
but you have an, you should have an appreciation of the fact that behind the scenes is nothing but Excel in action.
Okay, so when you mentioned,
whenever you asked any kind of questions on the entire architecture of power BI or the components of power BI,
it is very,
very important that you link it to Excel and you give them a very,
very concise and holistic answer of what you can
do in power BI desktop and what you can do in Excel and how similar they are overall, okay?
So that's about power query, power view, power pivot, and power map.
Power map is another added component in Excel and essentially power max.
within Power BI desktop as well using the very, very powerful mapping feature here that you have.
You can build some very,
very cool maps directly from the Power BI desktop and essentially you can do some pretty cool stuff in the Power Maps feature as well that you have within the Excel add-ins.
We have data catalog data catalog is basically Azure data catalog.
So it's pretty helpful for connecting to different sources management of different sources your data and management gateway
which is used for connecting to on-premises data and other very very essential component with something that you set up in the service level.
So if you go to the Power BI service basically so whatever you see I have right now is what I call the power BI service.
So remember guys I talked about this and they need.
beginning of the session where I say this is my client development tool.
Okay, this is probably a desktop.
It's a development tool.
This is where I build my stuff and after I build my stuff,
I publish it onto what I call the service and just to give you a quick demonstration of that.
Just to give you a very, very quick demonstration of that.
I'll build a very, very simple line chart here and I'll just ahead and name this as I just.
give it a name.
Okay, probably I'll go ahead and name it as demo.
I'll save it and call it demo.
That's my demo Power BI report.
Remember what Power BI reports get saved with the file name extension of PBIX.
That's another very important interview question.
They tend to ask you what are the different extension types and it's not only with Power BI files.
They something called template file source, which I call Power BI template file.
This PBIT.
Remember these two types of extensions, PBIX stands for the Power BI file.
That is the Power BI analysis file and PBIT stands for the Power BI template file.
Okay, so I'll save it for the name demo.
Click on save.
Remember to publish it is very important that you're signed in and once you're signed in,
once everything else is set up, you can just go back and save it in your workspace.
And once you've done that, you can just go to the and check that your stuff has been published.
Something very similar will be visible on your service and you can look at the report section where you can see that has been published.
This is the cloud environment.
This is my development tool.
This is my development environment.
That's my client.
This is the cloud environment which is my server.
I published my stuff here.
And now all my end users can view my reports and dashboards from here.
From here, I can further create what I call dashboards.
And then I can go to my dashboard and say share my dashboard.
I can go to share.
And then I'll mention with whom all I want to share my dashboard
and give the email IDs of all the folks I want to share my dashboard.
one very important thing to remember that is Power BI.
You only share stuff with everyone in your organization.
So for instance,
if you look at this link,
this is where you can simply go and specifically give access to all you want to share it with us.
Try to start typing the email addresses that you want to send them and automatically they'll receive email notifications.
or they will basically get notifications on their Power BI service interface, which is basically the tab here.
It's a notification tab that you basically have here, okay?
So very easy, very simple, very intuitive process overall.
And just mention that, mention these pieces when you're talking about it.
And again,
it just goes to show the overall depth of knowledge that you have in the tool
if you mention all these different components taken together while you answer these questions, okay?
What are the sources that Power BI can connect to?
Actually, there is infinite.
I believe there are a lot of sources Power BI can connect to.
If you go to get data, this is only from the desktop.
From the desktop, it can connect to a wide range of sources.
If go to more, you can just take a look at the available sources that Power BI can connect to.
We saw an example with Excel,
but you can connect to files,
on databases,
ton of databases that you have,
okay, and if you don't see anything here, you can obviously set up an old DVC connection, very easy to set up an old DVC connection
also.
And services,
you know,
this list just keeps,
keeps growing Azure services and it's pretty cool list of services and sources and connect to, okay, now the very interesting option is web data.
Just to give an example on this,
you can very easily connect to web data and I'll take an example of money control,
okay, probably we can go online and I'll search for the website money control, okay.
And this is again to highlight to you how easy it is to connect to web data in Power BI, okay?
So typically it might involve writing a lot of scripts.
We just want to quickly connect to our website and take some tabular data from there.
You can skip all the scripting, all that stuff and just connect to the data generally, okay?
So I'll go ahead and copy that link and paste it, click on okay.
And once you do that, it will try to establish a connection with money control.
It does take a bit of time initially.
And once it does,
what it will do is it'll go to the website and it'll try to look at the website and remember everything's underlying based on HTML.
It'll try to search for tables, anything which is structured as a table.
It'll try to search for that kind of data.
And once it finds it will present to you list of all those available tables.
Okay.
So I'm going to go ahead and just type this in once again.
So right as it does take some time depending on the connection that you have.
So I think the site is not working that well right now.
So what it's doing is establishing a connection with that site at this moment
and it'll ideally typically present to you a list of all the tables table or
structures that you have and you can see it takes a bit of time to connect to the site.
And as you can see finally my list has come up the initial case there was some issue with the connection
So we were not getting all the tables and as you can see very nice table present to me all the tables
So it basically looks at entire website and describe that entire website for any HTML structures that it finds and you can see very nicely
I get this bonds here and what is this bonds if I just have to quickly go back and show you in money
control Actually close that website here if I just have to show you quickly what that bonds is so there's a section in that website where I have
Something very similar to this and you can see how easily I'm able to get a very nice table
go to WebView where you get that website kind of view or you can directly select
your data from here just click on bonds and you can just go ahead and load this
table directly into your website 10 to your power be a desktop file okay
It is loading it further takes some time because remember it is taking that
it is straight from your website so it does take a bit of time just so I'm
only control site is opened up here and if you just go down you can just take a
look at this whole piece here that's the bond section that got loaded okay so
very is nicely you can see these are double the structures you know the
indexes global markets okay you have a section on bond you have a on
currencies and Power BI when it looks at that web data it is able to very easily
scraped the data it's very easily able to select that data from here because these are already HTML tables.
Okay, so mention some of those examples when you talk about this particular question of what it can connect to.
It can connect to a wide variety of sources.
And that is only in the desktop.
If you talk about the service, you can go to get data and the service.
Okay, and you have a further lots of services you can connect to from here.
So just as an example, you can choose content packs.
You can obviously connect to content packs, which are nothing but pre built and pre packaged dashboards.
We like to call it.
Okay.
In other words, another name for content packs is what we call apps.
So nowadays apps are a replacement for content packs.
You can think of it.
And they're nothing but pre packaged.
It's like packaged dashboards like packages, okay, you know, where you have already built a dashboard where you have already built.
as a dashboard, the model everything and the end users are consuming it as an example.
What really like to show you here is something with Bitcoin.
Okay.
So something with Bing Maps is an example.
So let's look at Bing Maps.
Let's see if I can find Bing Maps here.
Yeah, I have Bing Maps here.
So what I can do is I can just get it.
Now it is just like installing an app from Google Play Store or just installing an app.
Okay.
And you can see what I'm trying to do is I'm trying to consume that app.
Okay.
It's like a prepackaged stuff.
All the data says say everything is built in and I have to enter a parameter.
So Microsoft is asking me for a parameter.
So I'll just type in Bitcoin just to see what is the search activity in Bitcoin.
I just for those of you don't know Bing is a search engine by Microsoft.
Okay.
It's a search engine just like Google.
Click on add and you can see automatically that dashboard got added.
I didn't create it, but that is something that got automatically created.
Okay.
And that's the reason why I say these are content packs which are nothing but
prepackaged dashboards which are already built in and now we can actually see that how people are
searching from where people are searching the most and obviously us people are searching for Bitcoin the most.
Okay.
See some news mentioned here.
Okay.
of languages, interestingly, German and French are also pretty high, okay?
See the change week over week change,
how many more people are viewing and you also get a pretty good line chart here where it actually shows you the activity, okay?
The activity is the last week and pretty interesting.
I mean, on this particular day, there was a big spike.
You can see that 24,000, 37 people actually search for it and you know, story gradually people have lost interest.
It seems like, okay, not many people are actually viewing it now.
So anyways, the idea is to just highlight that there is something called content packs.
And remember there are two places where you can connect to data.
One is from the desktop and the other is from the service, okay?
From the service also,
you get a very robust set of options to connect to data where you not only can connect to files,
databases can connect to files from here.
One right.
and you can also connect on to services and further you can also connect on to organizational content packs.
If you're working for an organization and you can also connect on to any organizational content packs that people in your organization have developed.
Okay, so services are basically content from online services, typical online services like being Salesforce and all that.
Okay, but then organization is only specific to your organization.
So mention this piece as well when you talk about the sources you can connect to.
What are the building blocks of Power BI?
I this is something I've already talked quite a lot about,
but when you ask this question, again, as I said, mention give a very, very broad based view of the building blocks.
And I think I'll really appreciate if you're answering this question as an interview,
definitely they'll appreciate it if you're giving them a holistic view of the entire architecture, right?
So talk about Power Query,
talk a little bit about Excel, talk about those main components, which we call Power Query, Power Pivot, Power and Power View.
Talk about those components and then come to the building blocks.
The building blocks obviously are the data sets, the visualizations, the dashboards.
which are some of the most fundamental things that you have in Power BI.
Just to give you a brief context about what this stuff is.
So when you build your stuff in Power BI, desktop obviously you're working with three main tabs primarily.
That is obviously the query editor is where you did your ETL you loaded your data
into the model and then you'll come to the data tab data tab is where you can
actually view your model tables okay so right now I have only two tables one is
the order stable and one is the bonds table so you can actually view your
tables over at the data tab the relationship tab is again where you can
quickly take a look at your underlying table relationships and finally the
reports tab is where you can take a look at your report whatever reports you're
building okay and once you publish this stuff then you come to the service and the service you basically We really data sets,
obviously, the underlying model and data along with
that, which you've exported out or published in a service
that's your data sets, the reports folder, and from the reports, you build dashboards, those dashboards folder.
And have a component called workbooks, which you can mention.
And in dashboards, you further have what we call tiles.
Okay, these are called tiles in the dashboard.
Okay, as you can see, these are all tiles.
Okay, so for instance, created only a single line chart.
If you look at this particular thing that I published, I created only a single line chart.
Now, I can opt to add one more visual to my report.
So I can do is I can look at my report here.
And another beautiful thing about the service and again,
something that all of you can mention here is the editing capabilities in the service that Power BI gives you.
So you edit a report straight within the service.
So the amazing feature where.
You get a very, very desktop-like interface right within the service.
Remember, however, when you mention this part, do mention that you can't build models and you can't do query editing within the service.
You can only edit reports, okay?
So you can only edit reports and you can only work on the visualization layer within the service, right?
And here I can opt to build a bar chart where I can look at sales in category.
Let's say I want to stack this by segment and I can do that and you can see how automatically
Power BI is able to fit these things into the respective components and again the it
just goes to show the SSBI capabilities of self service BI capabilities of Power BI.
So if a novice user if a business user is working on it and they have absolutely no clue
you know what to put which where to put which component you can simply click on
the and Power BI will automatically put them in the respective sections.
Okay.
This again goes to show the self-service capabilities of Power BI and I can create multiple pages of course.
Okay.
I another page where I can build a simple tree map here where I'm showing the categories of category and I'm showing the profits now and I can build
a very simple tree map here.
And if I want to saturated colors, I can further add some color saturations.
Okay.
I can further add some color saturation.
Let's say I'm going to saturate this by sales.
I can further saturate this color.
Okay.
And now I can go back and and just remember this the report that I edited.
So I need to go back and save it.
So I need to go back and save this report.
And once I do that will be directly saved within the service itself.
Okay.
It will be saved in the service itself.
Now one of the questions that people might ask you at times is they might ask you
that can I so now that I have edited the report in the service,
can I go ahead and download the report and answer is absolutely yes.
Again, remember it's a preview feature previously.
It not there.
This feature got included after a lot of requests by users.
It was a major roadblock generally in the development process, but it's a very, very useful option that you have here and now.
You can actually go and download a copy of the report in the in your local machine.
Okay, very useful feature You can download it here.
You can also export this to PowerPoint review another very useful feature that you have in the service You can actually export it to a PowerPoint.
Okay.
So remember it won't be as interactive.
It won't be an interactive presentation Obviously,
but you'll still get a pretty cool view of you know an introductory page will be created and that visuals will be there.
So it's a pretty decent kind of interface that you will get.
Okay.
So that's another feature that you actually get in the service.
And once you have built that report, you can go back and obviously pin that to your existing dashboard.
So my dashboard is going to be demo dashboard,
which I built and now in my existing dashboard of demo, if you remember, I now have two visuals.
Okay.
It says Power BI.
is ready to download.
And now we can see these are the types that I have basically.
Okay, so hope you all follow this particular discussion where I talked about what are the building blocks in Power BI.
Obviously you'll mention a bit about the
components and then you will talk a little bit about what are the primary building blocks which are obviously data sets,
visuals, reports, dashboards and tiles.
Okay, data sets, visuals using visuals you create reports,
using reports you create dashboards and basically using dashboards essentially the tiles are part of the dashboards.
Using reports you basically create tiles and using tiles you create dashboards.
Okay, what are the different types of filters in Power BI reports visual filters page level filters report level filters and real true filters now the very very important question
So typically the way to answer this question is to go back and take its scenarios now.
What are the scenarios where you will implement this type of filter So visual level filters obviously is present only the visual level.
So it is only you can think of it like a report level filter It's present only in a particular type of report particular type of chart
Okay, so when you come back here and come back to the desktop and if you take a at it, you will see that line chart that I built here
Go to the filters panel and you can see all the visual level filters are applied by default Okay,
and these are going to be by default all the fields that are already a part of the report Okay,
so these are so if you place now the chart here if you place a bar chart here for instance So whatever you do in that line chart will not affect that bar chart.
Okay, so if I take a bar chart here and I say region wise sales and now if I go and filter that region so remember if I click on the region
filter here I will have a region filter here for the bar chart and if I go and change that region
filter to central it will not affect the line chart because as I said it's a visual level
filter so visual level filters are in short they are specific only to the visual then I
page double filters I can go back and drag and drop that region
And now if I go back and change central if I filtered into central both will be affected.
Okay, make it east both will be affected Okay, so that's why we call it a page table filter all the reports within the same page will
be filter.
Then I can have something.
But remember, if I have a report on a different page, it will not be filtered.
So in a different page, if I go back and place a region and sales, see how it will not be filtered.
Okay.
So although I'm filtering by central and east here in the page two, I'm not filtering by central days because that's a page level filter.
Okay.
Finally, if you want to implement that, you can implement something called a report level filter.
Okay.
I can remove the page level filter and then I can take the region in the report level filter,
which means all the pages in that report will basically have that filter.
And here I can go back and include central and west.
first page obviously it will be filtered but because the reportable filter if you go to
the second page also you can see that in this particular section it will be highlighted
okay it will also be filtered here and reportable filter will appear in this page also
if I get a third page it will be here fourth page it will be here so that is what we call a reportable filter.
Okay, and finally we call it a drill-through feature which you can use a drill-through filter to basically work on drill-through reports
So what you can do is let's say you have a scenario where you have categories
So instead of region if I let's say if I have categories here and I can build another use case in page two
Let's say I have a scenario where I have subcategories Okay,
so here I'm having subcategories and categories, of course, let's say I have categories subcategories and I'm looking at category and subcategory by sales.
Okay, very simple report time.
I'm looking at it's a detailed view.
I'm looking at so I can configure a drill true filter.
So the idea is that if I right click on my particular category, I should be able to see subcategories of only that particular category.
So in that particular case, I can go back and configure drill true.
And again,
you can mention where,
let's say,
the use case could be that if I want to click on a particular report,
I want to navigate to another page and I want to see subcategories in that particular page.
Those are scenarios where you will basically quite in configure filter reports.
Well, one other thing that I must mention to all of you here is there will be certain use case there be certain scenarios where people will
I mean I did mention the clause drill to reports remember.
Okay.
Just to quickly mention how again But just to clarify how you configure it remember one thing you must must remember is the filter is something that you don't place here
Okay, the filter should always be placed in the page where you're configuring it
So if you're configuring this page the category filter should be placed here.
Okay.
Remember.
I'm filtering by category Right?
What I trying to do?
I'll click on a category here and based on that I'll select a subcategory here So the idea is that you're gonna filter all
this particular page so you should always place category in the drill truth section in this
particular page and now when you go to page one and if you right click on the technology
you will see an option for drill through okay you will see an option for drill to page two
and when you do that you will see only technology getting filtered and once that happens you
will automatically see that in the drill true filter section category a technology is automatically filtered Remember guys, it's a very recent inclusion.
It's not exactly, again, real true filters typically was not part of the filters panel even a few months back.
It's again,
it's again,
very recently and again a new feature,
but it's always a very,
very useful feature because drill through reports is something that if you have worked across other tools,
enterprise reporting tools,
it's a very common feature where you click on
particular item and you want to go to another page where you're filtered with that particular item.
Okay, and this is a very, very useful case, right?
It's very, very useful scenario where it enables you to do that, right?
So you can not only configure stuff in one page, but you can actually configure stuff in another page.
Another very common scenario, another very common use case for this could be that there could be initial introduction page, right?
the initial introduction page could be like, you know, you're showing the category, so you're showing all category related information.
So you're showing technology and you're basically showing all that all all the categories right the categories are furniture office supplies technology
So everything about the categories you're showing and then your page two and the page two will actually consist of everything about the subcategories Right.
It is the subcategory page.
It is a subcategory page for the category that you have selected and page three could be the product page For the product that you've selected from the subcategory essentially from the subcategory that you've selected Okay,
so I can further build another page.
I can further build another page and guys remember this clean or be a table Okay, this could be anything else.
This could also and by the way, you don't need to have category also This could be anything else this could be Okay.
I'm just giving a table as an example, but this could be any kind of visual.
It could be one visual.
It could be multiple visuals.
You could also have a pie chart here.
Okay.
I can do a control C control B.
I can build a pie chart here and you could build any kind of visual.
But remember the concept is that whatever subcategories you see, so I'll call this a subcategory page and that is basically my.
initial category page and it can further build one more page called the product page and the
product pages where I'll probably have some detailed information or probably I can build
a bar chart here also where I'll get my product IDs or my product names in the values axis sales
Probably go back and sort this data, sort by sales.
So showing my highest sales up on top.
Okay, and here I'm going to go ahead and sort this information by subcategory.
So I can go back and sort this by subcategory.
So what I'll do is I'll take the subcategory and put it in the filter filters here.
Okay, so now remember category.
This page I have no filters.
So because that's my main page, nothing should be applied.
Everything about my categories in the subcategory page where I have everything about my sales.
see I've selected a category.
So that is filtered out.
But then further I can go back and select my product from here.
Okay.
So the idea is that you will navigate from here.
So you want to go to drill through you want to go to subcategory.
Okay.
So I'm looking at a particular category.
I want to know more about that category.
So go to subcategory.
Show me more about furniture.
Now whatever you're seeing right now is about furniture, right?
You're seeing all the, you know, all top subcategories in furniture.
You're all the top subcategories in furniture now.
Okay.
Interesting.
Do you see furnishings are not done well at well.
Okay.
So furnishings is kind of a lag.
Do you want to see what's happening in furnishing?
So you want to play on that and now you want to go to drill through in product.
You want to see all the products of that particular subcategory.
And that's how you get to see all the products of that particular subcategory.
So that's the idea of drill two filters.
Again, it's a very, very important concept, very, very powerful feature in Power BI, and it's very important that you mentioned that.
So those are the main types of filters that you have.
Content packs and apps, I think I covered that already.
So, just skip to that right now.
DAX is a very, important part, guys.
And obviously DAX is a functional language, very important piece in the overall Power BI desktop stack.
Remember in Power BI,
whenever you talk about Power BI and something that you guys will anyways mention when you talk about the components,
obviously you guys talked about Power Query.
There was an instance where I actually wrote a custom column.
I actually added a custom column a while back.
Okay.
Remember that cost column I added where I write a very simple expression called sales minus profit.
So the language behind the scenes that's being written is called M code.
Okay.
So this called M code.
So power query is equivalent to M.
Whereas power pivot is equivalent to tax.
Okay.
The underlying language that you use in power pivot is tax.
Whereas the underlying language that you write in power query is essentially called M
and just to give you a very very brief labor of M what it is.
And if you go to edit queries here,
remember all these queries and think about a combination of steps and
brief idea of what is m go to view go to advanced editor and you can take a look at the M query syntax here.
Okay.
That's the M queries syntax that is highlighted for you here.
So remember this is existing in query editor, whereas power pivot is something Dax is something that exists in power pivot.
So where do you see Dax?
You can go to the modeling tab or you can just enter the modeling from here and go to N a column and you can just start typing
in your DAX queries here.
Okay, we look at more about some of these examples just in a bit, but this is where you actually start typing in DAX.
Okay, at a very simple level, I can say some of sales.
I'm creating a very, very simple calculated column here.
And is how you basically build DAX queries.
Okay, I'll go back and just delete it.
So very important guys, when you asked about DAX, it could be a very generic question, but it's very important just to not be theoretical.
It's very important that you mention a couple of
things like it's a functional language and give some examples of how DAX is related to stuff that you can do in the power pivot,
that is in the data model,
and essentially it helps you add more meaning to your data because your underlying data could be in a certain format,
but there are certain kinds of calculations,
the kind of complex measures that you want to add, which could only be done in the power pivot layer and nowhere else.
Okay.
And we look at some examples of this just in a bit of certain things that you can do only
in the power pivot layer and nowhere else.
You can't do that stuff in the ETL layer.
Okay.
Might surprising,
but I'll just show you some examples of why These are some of the common DAX functions and again,
this kind of goes hand in hand with the ninth question.
So you're asked about DAX,
just some of the functions again,
this goes to show the length and breadth of your overall knowledge of DAX because any kind of job interviews
on Power BI DAX is a integral component because people expect you to be good in DAX, right?
Because it's not all about the visualization,
there is not all about point and click on knowing the underlying features which is fine but people do expect you
to understand the the DAX queries very very well and not only DAX even at some
level the M query language also you need to be good at least at least have a
basic understanding so that if there are errors in order to some debugging at
least you're able to pinpoint on those issues okay filter function it goes
hand-in-hand with calculate basically and this is again a
very component in Power BI desktop and something that you will use a lot and you
know whether you know any other function or not this is one function that you have to
know and have to understand okay and calculate function basically operates in the filter context
I just briefly explained to you what this is and what are some of the use cases behind
using it so what I will do here is I'll create a very simple table
So what I have here is a is an example where let's say I have your okay.
So I have your information I'll take order date and I'll take sales information.
Okay, so I'm seeing a bar chart.
I'll convert this to a table and right now what you're seeing is a very nice tabular information.
Right now what I want to know is a percentage.
Okay, so let's say the scenario is that I need to know.
Okay, right now what I'm seeing is the total sales across the total absolute value of sales.
Right.
But what if I want to quickly figure out the percentage of the total.
So for instance, right now you're looking at quarter wise or maybe another good example will be taking subcategory instead of taking order later.
Okay, so right now what you're seeing is sales across difference of categories
But what if you want to know what is the total percentage of paper sales compared to the
total So right now you're seeing absolute values.
How do you convert it to a percentage and the way to do that is using a calculator function Okay,
so you can't just divide this by total.
So I mean concept.
It is very easy.
Right.
So you'll take this number.
You'll divide by this three will take one zero seven five three two you'll divide by this okay just to expand it
good the focus mode now I hope you can see the numbers correctly now so two zero three four one
two point seven three divided by two two nine seven so you're dividing each of the row values
by the total at every step of the process right to calculate the percentage okay and that
is something you can do using the calculate function here to build a DAX calculated column
here okay there are some lot of cool ways of doing it and again there are some of doing it manually without creating DAX.
So you can go to show value as and you can actually say a percent of grand total.
So this is the easy way of building it.
But remember even when you're doing this behind the scenes power BI is implementing the DAX for you.
Okay.
There is also another revolutionary feature called measures.
So again, there are some lot of new things that you have in power.
is something called quick measures that you can also implement,
but again, remember whenever you're doing any of this stuff behind the scenes, you are implementing DAX, okay?
You have some new quick measures.
If I go to new quick measures, I will see a ton of stuff that I can build in Power BI, okay?
You can just take a look at this now, and you can see a ton of stuff that you can do here, okay?
And again, there are a lot of these things tend to be added from time to time.
new things can to be released you can see time intelligence calculations
totals running totals star rating really cool thing you know star rating
it's an amazing feature so you can actually give a star rating highest value
let's say my highest value is going to be something like 250,000 I'll probably say
okay that's my highest value is by lowest value
I can give the number of stars and I can enter a new quick measure called start rating.
Okay, and now you can see behind the scenes I've actually got DAX.
So whatever you're seeing here right now is actually a pretty complicated version of DAX.
Okay, so whenever you create quick measures or any of these default point and click options behind the scenes power, we are actually building that DAX for you.
Okay, this is actually a pretty complicated bit of DAX that's written and the final output is actually pretty neat.
That's my that's my star rating column.
I've created and you can see that that's the top value five stars here four stars here one star here Sprit relative depending on the type of data and the best part is that it is
actually going to be dynamic and what do I mean by dynamic what I mean by dynamic is that this value will change depending on the underlying data.
So essentially if you take something category.
And you further try to break it by region, that data will actually change.
So now, now your stars will actually adjust based on the underlying generative data.
Okay.
And now you can see nothing is now a five star.
Everything's like, you know, two stars, three stars because the maximum that you've mentioned is no one's reaching that other than the total obviously.
Okay.
So what I wanted to highlight again is that behind the scenes,
although you can do this particular You can solve this particular problem in a very,
very simple manner, but behind the scenes Bobby, I will always write a DAX for you.
So back to the underlying question once again.
So I'm looking at my individual sales values and I want to calculate the percentage of it.
So if I want to write a DAX formula for this,
the way to write it would be to go back and implement it manually using the calculated column option and I'll quickly go ahead and
open up my wizard and fire in a quick bit of DAX here.
So how do I do that?
I'll go to the modeling tab and I'll go to new column,
click on new column because that is actually going to be a sorry, a new measure that's going to be a new measure.
And I'll just in one of the upcoming questions.
I'll talk a little bit about what is a measure and what is a column.
Okay, this is a very, very important difference by the way between the two.
So here is going to be a measure.
I'll create and the measure.
is going to be called, let's say sales percentage.
I'm to it sales percentage and let me quickly type in the measure.
I'm going to take the sum of sales and I'm going to divide it by what?
I'm going to divide it by the calculate of the sum of sales.
I'm going to divide it by the calculate of the sum of sales.
sales.
And here I'm going to apply what I call the filter context.
Okay.
And this is the important part.
This is the key part.
And okay.
So whenever you use the calculate,
calculate basically has two arguments and as you would have observed whenever you start typing any kind of function in tax,
it has a pretty nice kind of auto completion or a help suggestion visa that comes up.
It tells you that, Hey, what are the arguments that I need to enter in calculate?
And it very clearly tells me that the other.
expression, the expression is the stuff I want to calculate, which is obviously going
to be some of sales, that is what I want to calculate.
some of these underlying expression and then I have to get the filter context, okay?
You could get the filter, okay?
Evaluate an expression in a context modified by the filter and this is the important part.
So remember when I am performing this operation, I should ideally not have any filters, right?
Conceptually think about it.
When I'm performing this operation, ideally I should not have any filters.
And that's exactly what you're going to set here in this particular example.
So you're going to set this up in such a way that you don't have any filters.
Let me go ahead and quickly set this up now.
So what I'm going to do is I'm going to go ahead and So when I say all I'm specifically telling Power BI that hey,
no matter what filter context is applied on that particular row, you're ignoring everything.
Okay, and I'm going to take all of orders.
Okay, I'm going to consider all of orders and I'm just going to go ahead and close my panel.
This is here.
Okay.
a missed closing the brackets here.
Let me go ahead and quickly edit the syntax and whenever you make up incorrect syntax,
what happens is it will give you this red squiggly is which will highlight that.
Okay, so the syntax is not right.
As you can see, that's my DAX formula right now and just to clarify once again, calculate the way we are using calculators.
We're calculating some of sales as a first argument and they're calculating some of sales based on the filter context of all orders.
Okay, that is where you'll you'll mention the filter context.
So you're you're saying that I will calculate the total sales irrespective of the.
So I'm taking all of orders so you're dividing individual rows by the total that is the underlying meaning of this okay,
and I can actually go ahead and multiply that with hundred just to add clarity on this whole formula.
So because it's a percentage I'll just multiply that by hundred overall and now I can go ahead and add that piece of code right into my table.
and see the results okay as you can see I was very easily able to achieve that using a very very simple piece of DAX code involving calculate
Okay, so is just one use case of how you use calculate very very important DAX formula calculate and filters and something that gets asked all the time in interviews
Okay, and the best part about this is dynamic.
So see how I've used subcategory here.
If you want to use something else Let's say you want to use a region do that as well.
So I'll take a region,
I'll remove the subcategory and see how that entire formula adjusts automatically depending on the kind of calculation you're using,
okay, the kind of grouping that you're doing.
And you can see the region percentages are highlighted.
Everything adds up to 100%.
It comes up pretty nicely.
overall.
If you don't like region,
if you want to further spread it by segment,
you can go ahead and spread it further by segment and see how again the entire calculation gets dynamically adjusted.
Okay.
And this is again the beauty of measures.
Okay.
This is again the beauty of measures which the very fact that measures the dynamic in nature.
It is dynamic in nature and it will basically adjust every time you change anything in the visuals change anything
filter anything your measures will always recalculate.
Okay.
And measures are always I can.
What are the benefits of using variables in DAX as a variables in DAX are no different from variables in any other programming language.
Obviously, DAX is another kind of functional language.
So variables,
one of the most important use cases is that it can be reused multiple times so as to avoid any kind of duplication or redundancy in your overall code,
right?
There are some other examples that we have on you can specify what are some of the time intelligence functions again,
calculate is something that you can use for this.
So we talked about calculate all in filter and you can obviously use them in multiple ways to go ahead
and create any number of different use cases.
Okay, this is one use case I mentioned, but you can obviously use calculate to create trading X month metrics via DAX.
is a non-standard calendar, for instance, okay?
And you can see what we are doing here as a second step is we are using all again to remove existing filters.
Okay, remember the concept of all is you're still applying
the filter context, but the only difference is in all what you're saying is you're removing all the filters.
Okay, what is the calculated column in Power BI and why would you use them?
And this is the part where I talk a little bit about calculated columns.
And an example that I will show you guys here is related to let's say profit percentage.
So what I will do here is I have a measure that I created here go and delete it.
So I have a cost column what I will do is I'll build a calculated column.
So you build calculated columns is in the new column option you build measures in the new measure option.
So I can build calculated columns is in this option here and column name will be profit percentage.
What is the formula for profit percentage it is going to be cost by sorry profit.
Sorry.
I think I've selected the wrong table.
Okay.
So yeah.
So one problem that happens is and just do I think good that we got this error.
So one thing that happens is when you create the calculate column, remember that you're clicking on the right table.
So basically I'm trying to create a column on this table
and I'm not able to find the profit column because obviously it doesn't exist in my bonds table.
So obviously I'm trying to create a table a column in the wrong table.
Okay.
So I'll go back and read that from here.
So I really the profit percentage should be created in my order stable.
Right.
So I'll go to my order stable.
Just click on it once and that should pretty much be enough to select that table and click on new column.
Now click on new column and a column is now she can see that that's not a part of my orders table.
Okay.
By default come with the column name of column,
but I can absolutely go and change it to something like profit percentage and profit percentage going to be profit and now as I start typing
profit you can see automatically profit will come up orders of profit as a basic syntax that is applied.
Right.
So orders my table name and profit is the
So that is profit by cost that's my presentation profit person right so profit by CP in 200
Everything looks okay up to this point and what I will also do is additionally also create a measure.
Okay.
Additionally, you'll also create a measure conceptually.
There is a lot of difference, but syntax wise, there is very little difference.
So what I will do is I'll create something exactly similar.
Only difference is this time and call it profit percentage measure.
See, all I've done is I've copied pasted an entire formula.
So that's the measure and the only other thing that I have to do for measures is I have to aggregate it.
So that is the only other additional syntax difference in measures that I have to pre-aggregate my fields in measure.
because without aggregations measures have no meaning inherently.
So that is what I'm going to do.
Oh, sorry, one small thing I have to change.
So 100 is basically going to come outside the sum.
That's my measure.
So what I have is a profit percentage calculated column and the profit percentage measure.
You can see that the difference in icons.
Now conceptually, what is the difference?
There is actually a lot.
And as an example, if I show you region.
And if I basically want to know what is my profit percentage across different regions if I just try to see the profit percentage using calculated columns
Look at my number is that even a valid number.
I mean three hundred thousand percentage Which is that the total profit person answer is wrong.
That is absolutely incorrect data.
Okay.
So even if I want to look at this data across in a granular level across different segments, you can see the same problem.
I get very, very big numbers, which is which definitely something wrong with my data.
But the moment I take something like the measure, if the if I take something Everything looks perfect.
Okay.
Things look perfect now.
Okay.
The measure is giving me the right result Whereas the this is giving me the wrong result.
And why is that so and that is basically the key difference the key conceptual difference between a calculated column and a measure.
Okay.
I'll come to the performance aspect when to use what and obviously when you answer this question on measures and calculate columns performance aspect is also very important,
but you should always start with the conceptual difference first.
What is the conceptual difference?
What is actually happening in the model and what is actually happening in the model is when you look at the calculated column.
All that it is doing is it is basically aggregating all the data that is already there in my model,
okay, in other words when you go back to the model, I mean look at the orders table.
If you just scroll over to the right,
you will see that profit percentage is actually calculated and stored for every single row in my model itself.
Okay, so whatever column you are looking at the end here profit percentage is basically a calculated column that has been calculated for every single instance of the row.
Okay, and that is stored in my model.
So all the time doing in the table is I'm aggregating all these values together, which is obviously incorrect, right?
So essentially what it means is if I look at row number
Let's say if I look at row number one and two and if I look at all the 20 rows here,
which are all Western Okay,
essentially what I'm trying to do is I'm trying to add up all these profit percentage values for West Okay,
just doing a group by region and some of profit percentage,
which is obviously incorrect Okay,
it obviously incorrect and if you look at my table,
that's exactly what I'm doing I'm actually doing a some of profit percentage across all those flows.
She's actually incorrect.
Okay, so That is the key problem.
And that is exactly what you need to fix here.
Okay.
And even if you try to do a average of profit person, you might be wondering that.
Okay.
Okay.
So obviously if I try to do a summer profit percentage, I'll get a problem.
So why don't I just go back here and change this to average even by doing this you will get incorrect.
Okay, you will get incorrect output.
Even if you do this, this is not the right way of building it.
Okay, the right way of building this is to actually do it using measures.
Okay, and that is exactly what I want to highlight here.
And the other thing that you would have observed is in measures,
they're actually not stored in the model measures are always calculated ad hoc dynamically on the fly measures are never stored in your model.
Okay.
Your model calculates the value of the calculate column and its store.
basis in the model,
but it never stores the value of your measures measures are always calculated dynamically on the fly Okay,
so if you just look back at this particular example here,
you can see these measures are always recalculated So when I go back and remove the segment those measures are actually getting recalculated on the fly
Okay, so I'm actually them on the fly if I just go back and take something else
Let's say instead of reach if I just go back and take a category, it's actually getting recalculated, okay?
So that is an important thing about measures that you should keep in mind, okay?
And that is the other reason why when you build a measure,
you can just see that in measures because they are not calculated on the fly,
because they're actually not stored in my model, you have to define the aggregations here, okay?
They are calculated dynamically based on whatever filters you apply whatever stuff you do.
They always calculate dynamically and they are responsive to filters.
That is the other important thing.
They are responsive to filters.
Okay.
So for instance,
if I go back and put some filters here, let's say I'm looking at category and I can actually further split by sub categories.
Okay.
And now if I go back and put some filters, let's say I'll go back to region and.
I'll put some page level filters here for region and if I look at the central data,
you will see that now they're responding to filters.
Okay, they're actually going to be responsible filters as well, generally.
Okay, and they're recalculating every time you're changing anything in a data, they're recalculating based on the aggregations that you have defined.
Okay, so couple of key things that you have to keep in mind when you talk about measurements.
a is they are not stored in your model b is they are dynamic they are always calculated
on the fly dynamically as and when you are
And C is obviously there are particularly useful for calculating any kind of data
which is pertaining to percentages or anything that represents any kind of denominator.
Okay, so divisions particularly are a very, very common use case where you will use measures
because divisions are where a calculated columns will fail all the time.
Okay, imagine building something like this in your query level.
Okay, you cannot do it.
You cannot build a visualization data in query layer.
Okay, so for instance, if you try to perform this profit, profit, calculation, row by row, it will not work.
Okay, it not work.
And in this kind of scenarios where you have some kind of percentage of some of denominator,
some kind of division involved,
you should always use measures,
okay, and again measures calculations and the other really really powerful aspect of measures is that and as we have mentioned in the slides here as you can see that
here Calculate columns are not compressed and they consume more memory and the reason why they consume more memory is because as I say
They stored in the model and this is where again measures went out Okay measures win because measures are actually they're not stored anywhere.
Okay.
It's more dynamic.
They're not stored in anywhere they take less storage and but the obvious disadvantage of a measure is that because
they're not stored anywhere because the dynamic because the calculate ad hoc on the fly they
probably put more strain on the resources right so probably take more time to evaluate so
as of today the differences are hardly you know it'll hardly feel it's almost negligible
because of some of the performance improvements that have taken place but generally they'll
is the same performance but still measures to take a little lesser time
comparatively because as I said just put more resources and as you can see
the last point that you mentioned is they can also reduce processing
and refresh performance if applied on large fact tables and can make a model more difficult to maintain our support.
Okay, given that the calculated column is not present in the source system.
That is another important point you should keep in mind power pivot.
I think we have spent a lot of time already on power pivot discussing the components at a very high level already.
But when you ask the question on what is power pivot is very very important to link it with DAX.
So again, it is a it is a discussion on one of the components of power BI.
So I think it would be really appreciated if you all if you could just spend like, you know, a minute to go over what?
talk about the self-service aspect,
talk a little bit about some of the components of Power BI
and then come to talk about Power BI and the fact that it's the modeling layer,
the fact that you have that x-velocity in memory,
qualitative ways that you're maintaining and essentially that's the layer that you actually build DAX, calculate columns and DAX measures in Power BI, okay?
So mention those pieces along with that.
Another thing that I wanted to mention is the data model quickly data model obviously is the model that you that you are building after after your performing the query editing and all that stuff the
place where you're loading all your stuff into is what we call data model and data model consists of tables and obviously tables are consisting of columns and rows and essentially you have relationships.
Okay.
These are the key aspects of a data model and just to briefly explain this whole piece to you and what I'll do here is I will be connecting to my Excel once again and I'll
just be bringing in one more table.
So what I'll be doing here is I'll be quickly going ahead and connecting to my Excel and
bringing in one more table called returns just to demonstrate to you guys what is a relationship
and how to quickly build a relationship and you can see the same process and the only
difference is that I'm going to bring in these two tables people and returns together.
Okay.
I'm just going to directly load them.
You see processing queries and I've loaded that into my model right now.
It is loading my data.
So this is a step where I'm loading my data,
creating connection, loading data to model, you can see these steps and I have to make a small change.
Actually, I go to edit queries and my data is actually not in the right shape.
So I need to go back to returns and it's a quick thing.
I'll change here.
I'll just go back and make this first row as headers because you see headers are not in the first row,
very, very simple ETL operation I'm going to perform.
Go back to people and convert that to first first first headers.
I'm not really using bonds.
So I'll just go back and, you know, go back and remove bonds.
Delete their points and close and apply this.
Okay, so what I have is the end result is I have three tables and remember in
query teacher they're called queries for the moment you load that into the model that is power paper we call them tables.
Okay, so everything is a table now and if you go back to the table tab you see the three tables that are present here obviously orders people and returns and
if you to the relationships tab you can actually see the relationships.
Okay, so probably I will do a pretty decent job in automatically creating relationships based on columns.
names.
So if there are two similar column names, probably will automatically relate them, but then it will not do a very good job always.
Okay.
So for instance here,
you can see region and region automatically related to double click on the relationships tab and you can see the relationship region
and related can see the types of relationships cardinality many to one one
to one one too many typical standard relationship types and it's an active relationship that's something you can turn on and off.
and you can also ecc but returns it was not able to relate that it's going to manually do it order ID just it's as simple as a drag and
Drop and now you've related orders and returns as it is very very important to build relationships
and you know it's always a consideration that you have to always have to decide
whether you want to keep everything in one table or you want to go back and
keep things in separate tables as it's always a very very key question that you
might get so at what level do you decide should you normalize further at what
level do you decide that so that that's a very tricky question and I think
there's no easy answer to that but it really depends just like so many other things it
really depends on a lot of different scenarios because you can decide to
have everything in one table so for instance if you look at this particular use case
you can decide to have orders people and regions returns all in one
single table okay you can just go to
queries you can just join all these three together and have everything in one
denormalize table you know things look things are good but the obvious disadvantage that it's going to be a big table.
It's going to take a lot of space and that's not a good thing.
Okay.
And on the on the contrary,
you can keep them in separate tables, but the obvious problem will be that, you know, because the relationships are there.
Whenever you're trying to query it across,
you know,
when you try to visualize something across three different tables,
it's going to take a little bit more time compared to when you're trying to do it in one table.
Okay.
So there are obvious pros and cons in both approaches, but it's important to understand the relationship are very, very important.
And mostly in Power BI,
as I said,
you mostly have these normalized scenarios where you will typically have a lot of these split out tables and you will typically have to relationships.
Okay.
And why is relationships important?
Because without relationships, you cannot visualize data across multiple tables.
So for instance, your orders here, people here returns here.
And if you go to my visualization layer now,
I can opt to see sales by the And you can see sales have selected from the orders table.
I go to the people table.
I can visualize that by person.
Okay.
I can actually visualize that by person.
Remember I have selected fields from two completely different tables and I can actually filter that whole stuff.
Okay.
Pretty cool.
Isn't it?
And this is something I could not have done this if I have not related the tables.
Okay.
So you can see the visualization that I have here.
I have selected fields across four, three different tables and this was possible only because I've related the tables.
If I've not related the tables,
for instance,
if I had not related my tables,
if I just go back and remove the relationship here,
if I make them independent tables without any relationship,
you can see that it'll give the same values at times,
we will give an error like this and at times, it'll actually go back and say, give the same.
value.
Okay.
So that's an important thing to remember and understand exactly why relationships are required and these are some scenarios that you should actually point out when you talk
about the data model and what is popular and when you talk more about relationships.
Okay.
So the X velocity in memory analytics engine is something I talked about.
That's basically the underlying engine behind power pivot.
That's something that drives power pivot.
It can handle a huge amount of data because it is nothing but a column database.
Okay, it stores data in column databases and column database says some of you may know is a very special kind of database which
is optimized for storing huge amounts of data and overall data access is very very fast in the column database.
It doesn't maintain data in a typical relational database format where you know data is stored in the form of rows and columns.
Typically, the way we understand it, but column database is a very, very special way of storing data and I'll
get into all of you to go look at it more.
And if you can stress a little bit more on column database when you talk about this particular concept,
just just spend a little bit of time talking about what is the column database because as I said,
everything about power pivot and the in memory engine of power pivot is based on column database.
So, talk about.
the following mentioned a bit about what it is.
And if you can get into those aspects of why data is fast, why access is fast, nothing like it, but it's not required.
And getting too much of the advanced aspects, but that's not required, but it's an optional thing that you can take a call on.
Our relationships, obviously, there's an option that you can set.
You either have one active relationship, you have multiple active relationships.
Obviously, if you go to the relationship types
here, one very, very common use case of this would be order dates,
the date,
the date,
think, okay, you can have order date, ship date, due date,
and a very, very common use case of this is especially if you're dealing with role-playing dimensions.
And this is a very, very common scenario that you should mention, role-playing dimensions.
It's a that you should mention where you can only have one active relationship, okay?
It's very, very, very, very, very, very, which role-playing dimension you can only have one active relationships.
Okay, and how do you make a relationship inactive?
So we talked about that already.
Let's say region and region related here.
It's a solid line actually double-click on that and you can make that relationship inactive click on okay and that is an active relationship.
Power Query,
I think I spent a lot of time on this already,
so needless to say when you talk about Power Query,
mention a couple of key terms,
ETL tool,
Shaping, Glue, of very, very important pieces and also mention the M query bit that every query is a combination
of steps and you can build multiple queries and ultimately the underlying layer you're writing M code okay query folding is actually another very,
very important feature in power BI and obviously it's more of a performance enhancement question that that can get asked very,
very important question actually that that sometimes tends to get asked here from a performance
optimization standpoint and query folding basically The kind of operations that you can perform at the source get transferred to the source.
Okay, so at a very basic level You can see there's something called view native query.
There will be something called view native query that you will see So if I just quickly go ahead and and get some data from SQL server here real quick
I'll try to pull it up data from SQL server.
I think this is also the first time that you are seeing how I'm connected to a SQL server So given dot as my server name
and I'll try to connect down to my local instance.
I have a database called adventure works as a demo.
So I'll connect to my adventure works database and here I'll go and connect to,
let's say, dim customer or let me go back and connect to dim product, connect to dim product, click on okay.
And there goes my SQL server table.
And if I try to add in some columns here, let's say what I'll do is I'll try to remove all this stuff.
Okay, try to remove all this stuff that I have here, remove columns.
Obviously, these are things that's going to work as expected.
Just like as we understand steps are going to be created, I can go back and remove all this stuff from here.
Okay.
I need this.
So I've kept only three columns and what I can do here is further thing I can actually add columns, whatever.
And the idea here is to show you something called view native.
query.
And what happens here is that I've actually got data into Power BI desktop now.
Okay.
You remember I connected to my SQL server.
I got it into Power BI desktop and after that, whatever transformation I performed here, I performed this within Power BI desktop.
Okay.
So whatever transformation I did,
I did it within Power BI desktop, but using query folding Power BI will actually transfer that operation to my source.
So what is my source?
My source is my SQL server.
So instead of performing this operation within Power BI, from this in memory inside Power BI, I will be performing this in database.
Okay, so you're transferring in memory operation to an in database operation.
Now that stuff will be performed in your database that is in your SQL server and the result tent will be returned.
So if you just go to right click on that and go to view native query,
you can see that this is the result and stuff that will be returned from my underlying table from my database.
Okay, it's a very powerful feature.
And what it means is that when you're actually getting the return to Power BI, You're not going to get all those 50 columns, right?
If you need only three columns, then Power BI will bring in only those three columns.
And as I said, it's a very, very powerful feature.
And not only works with removing columns, you can actually go back and remove columns.
And you can see that at this layer also, if you to view native query, it actually gets renamed as alias gets applied.
Okay, so let me give some different name here,
like color name, let's say English product, I'm going to call it, let's just going to call it.
Okay, and I'm going to call it Product ID.
So actually renamed all my columns here and as we understand in as part of the SQL language when you rename a column,
all you're doing is actually applying an alias and again,
as you can see,
although this step I'm performing within Power BI desktop,
you know, Power BI desktop will just go back and offload that operation or transfer that operation back to the source is the SQL server.
And if you go to view native query, you can actually check that.
Okay.
And again, it's a very, very important piece that you should keep in mind.
If you try to do a split here, let's say, Split by D demitter.
Okay, and say I want to split this by a space and click on okay and see how I split that stuff and if I right click on this now, you will see that
view native query disabled because in the native SQL language in native SQL server, this operation is not supported.
So up to a certain point you can do query folding, but beyond a certain point you can't do query folding.
That is another important thing to keep in mind.
So if you perform any transformation in query editor, you know, for which query folding is not possible.
Obviously view native query would be great out in that case.
I mean, obviously that means that at the native database layer that particular operation is not supported.
So that's another important thing to keep in mind.
Okay.
What are some of the common transformations are the very,
very important question that might get asked at times because just to test your basic level of knowledge,
changing data types, very fundamental thing will, you'll do it always, all the time.
You know,
another thing that I will add is adding header rows, you want to basically modify your header rows, filtering rows, not very useful thing.
to excel data.
Some of CSV sources, you know, initial few rows might just be, you know, gibberish.
It might just have merge sales.
I'm going to email some kind of introduction headers, all those stuff.
And you'll always want to filter out rows initially columns such an important thing.
You don't want to see all those columns, right?
If you have 50 columns, you don't really care about just take out three or four columns out of it.
Very important option grouping,
aggregation, again very important splitting another very common kind of transformation that you might want to use
okay some t-limiter some characters based on which were split the columns out subset something out
so very very important option over adding new columns needless to say it's something that you do all that time, right?
Can SQL and Power Query be used together?
I I just answered this question in this example here.
Answer is absolute yes, you can.
And the best part is you don't have to,
you know, obviously you can do it is in the graphical user interface or you can directly go and type out your query.
You can straight away type out your SQL query here more customized to my SQL query across multiple.
What are query parameters query parameters are run again a very very important topic in power bi and query parameters the whole idea is that they are very similar to filters,
but there are more dynamic sort of filters and the way you said them is using the parameters wizard here.
I will go back to my orders query type go to manage parameters and set up a new parameter.
So I can actually define a parameter like this.
It's a region parameter type is going to be text type.
Okay, suggested values is going to be let's say I'm going to give a list of values
and when you say any basically you get a you get a text box
and if you give a list of values you get a drop down.
That's the only difference.
I'll show you both the examples.
Okay, current value is a default that you can give actually else and go back and put it as a central.
So as my region parameter have set up here and now what I can do is I can go to my order
stable and see how I can filter by region here in the query that I can go here and filter by region.
I can directly type out a value here instead of doing this what I can do is I can go to text filters.
I can say equals and here I can go back and select the specific parameter type.
Okay.
And this is really the amazing thing about parameter.
So now instead of saying equals to a particular region instead of typing out a value here,
I can go back and say it is equal to the parameter region.
Okay.
So now that filter is actually equal to that parameter region.
Okay.
And what is that parameter region equal to remember that parameter region equal to was set a central.
So now that central parameter is set to central and that is why it is filtered on central.
Okay.
If you go to the region,
it is see this filter on central right now and it's very easy to change it by the way,
go to edit parameters, go to edit here and see that.
the list type was any.
It actually set as a text box and actually go back and set it as something else as a East and now you can see it's automatically going to
change it to East.
Okay, that's the real beauty of using parameters generally.
You just need to back and click on that interface once to for it to reflect just to refresh it once for it to reflect
And now you can see it's reflecting as easy if you go back and set this as West.
Let's say go back and set this as West And if I go back to orders now,
you should be able to see that now the entire thing is automatically Filter to West so you can see how dynamic the whole thing is and remember this is not only at the query
written level,
even the visualization layer, you know, when you're using parameters, when you've actually loaded this query in, you can straight away go to edit parameters.
So something very similar to what we are seeing here.
You will see in the visualization layer also, you will see in the modeling tab.
Sorry here.
You will see the edit parameter section coming up here where you can go back and directly modify your parameters straight away from
the And one other important thing is the other use case where this is actually required and the question that we're asking here is Power BI templates,
the other thing that you should all mention.
What is the use case of this?
As I mentioned,
the use case of this is one very common use case of this is you don't want to load all your data, right?
So if you have data for all the four different regions as central east, west, and you don't want to load all your data, right?
You have hundreds and thousands of rows of data, but you don't really care about all your data, right?
If want to look at data, right?
Then you want to select West you want to sit down before East you want to sit on the East, right?
But what you don't want to do is you don't want to select all your data
You want to get all your return to Power BI desktop and then you want to filter it.
Okay.
There are two options, right?
One is to include all your data, load all your data and then put a filter.
The other option is first you give the user a prompt and based on whatever option they select in the prompt,
you go back and load your data.
And the second option is obviously the parameter approach
which is preferred any day
because you're loading in only the requisite amount
You're not loading in any additional data and the best part about the second approach is again As I said,
you're loading in only the amount of that requires obviously the amount of data that you're loading in is very very less and Obviously the performance will be much better.
So another common use has is the stage right?
So if you have data worth for the last 10 years and you don't care about that 10 years data Right?
So you care about only data for the last one month
So you always give people a filter option or in this case when I say filters
I mean parameters right so you can figure filters with parameters and now get people get a prompt when they open up the dash
for they get a prompt and when they want to view the dashboard they will actually have to select the rate.
You'll actually go to the prompt select the rate and now depending on what they enter data will be filtered
and from the source only that data for the last three months will be picked up.
Okay, so that's the use case of parameters and how parameters are different from the normals standard on filters.
Flitch language is in power query we talked about it already m code and it's
very important to also mention a bit of background when you ask this question
just talk a little bit about the background of what is power query
why do we need power query event power pivot can import data from
mostly sources and I think this also has been answered before I've talked about this
as well previously but it's also important when you get this question to
give some context on what is power query and what is power pivot and essentially mention the part that although yes,
you can get data in Power Pivot,
but it's also very, very important that you address the part that it is Power Query that is an ETL tool, okay?
Power Pivot is not an ETL tool.
Power Pivot is only used for loading data into the model.
That's it.
I mean, essentially, Power Pivot can only be used for performing calculations and analysis, okay?
The whole point of Power Pivot is analytics, okay?
It is not for data-clinzing, okay?
that is not power to bits rule.
So are two add-in components.
The two tools have two very different rules and that is the thing that you will bring out in this particular question.
Power Maps obviously is the mapping interface of Power BI.
So I'll just go to briefly focus a bit on this particular piece.
Obviously maps are extremely powerful in Power BI desktop and if you just go to the map section one very very important thing guys
You have to keep in mind is when you're configured especially working at maps is to set the correct geographical type
So if you go to modeling you have data category something called data category
It is very very important that you when you inherently have data like city
It is very important that you go to data category and set this as
Extremely important that especially when you're working with map data you have to set this geographical data type and when you do that
You will see a small globe sign will come up.
Okay, so now it signifies that city is actually of a data category city Which is a geographical type and a small globe sign will come out do the same for country as well
To the same thing for country I'll do the same thing for postal code and see how postal code is actually summarized.
So this is another thing that will happen from time to time in Power BI, which you have to fix in modeling.
So sometimes something will be summarized.
So obviously, postal code is something I don't want to summarize.
So I'll actually go to don't summarize because if I give you two postal codes 5,
6, 0, 1, 0, 2 and 5, 6, 0, 1, 0, 3, you can't I mean it makes no sense to say the average postal
code is 5,
6, 0, 1, 0, 2, 0, 2, 0, 2, 0, 0, 0, 0, 0, 0, 0, 0, 0, Go to postal code and set it to postal code as a geographical type.
See the groups I will come up and finally the only thing that's remaining here is state.
I go to state and set a type here as state.
And once that is done,
I will go ahead and Okay,
you can see that it says apply changes and the problem here is that I've actually made some changes to Madeline query if it's something else.
Okay.
I know if I can load it right now.
So I'll directly straight away go to the maps and there are two kinds of maps mainly in the desktop interface.
Obviously there are a lot of custom visuals that you guys have.
So just to give you a sneak peek into custom visuals real quick and we're going to spend a lot of time on that just to give you a sneak peek on this whole piece.
I have a lot of custom visual maps where you can.
Do a lot of cool things, but at a basic level, obviously, you have only the kind of maps that you're seeing here right now.
I'm going to quickly go ahead and close that query because I'm getting to my SQL server.
It is creating a bit of issue.
Okay.
So next step is I'm going to ahead and quickly replicate and just kind of repeat what I just don't know.
Someone quickly go ahead and set this to city.
Set my state to the state type.
Set my postal code.
And finally, I go to set my country.
Okay, so the idea again as if I wanted to highlight here is.
The concept of setting geographical types correctly because this again when you when you try to visualize this data finally in maps
I just ensures that you're that you're more accurately able to represent
data Remember power be able to a pretty good job in understanding exactly what a particular thing is
So if it's a city name if it's Delhi Bangalore Mumbai power be I knows it is a city So you know,
I have to specifically tell it's a city But remember if it's something that probably is not able to perfectly recognize in certain cases
It will be very very useful because remember the column names are not always going to be exactly what you're seeing right
now Although it's recommended that you use proper column names,
but that may not be the case and again the underlying data may not be exactly what power be able to recognize So those kind of scenarios is very helpful if you set data category security Remember,
even if you don't set it properly, probably will still display your data properly.
Okay, but then there are certain not come accurately.
Okay.
And I can go ahead and represent this in a map.
And all I have to do is just take my country,
I'll take my state,
I'll look at my city and see how probably I was not able to do it the best way all the time.
So sometimes it will fail and I'll represent this data based on sales.
So is basically going to be my size, let's say.
I can further to a color saturation based on sales, okay?
I can look at this data across and now you're seeing, right?
And you might be wondering what's exactly happening.
Why am I only seeing, you know, Western United States and the reason is because if you remember, I have filters applied.
So, so the underlying model that I've taken, I have filters applied based on parameters.
Okay.
And again, that is something I can further configure within Power BI.
Okay.
Just to quickly clarify this once again, where do you set the buried parameters?
Remember parameters is something I applied just a while back and you can further look at the visualization layer.
You can further go to edit queries, go to edit parameters in order to open edit queries.
Let's go back here and say this as East or West or Central, whatever you want to set.
