Hey everyone, welcome to a PowerBI full course by simply learn. PowerBI is everywhere today. From startups to large companies, everyone is looking for people who can turn simple data into smart decisions. And guess what? Learning PowerBI could be your key to great career in data and business analytics. So here's what we'll cover step by step. First, we'll explain what business intelligence really means. Then, you'll know how to create your first PowerBI dashboard within minutes. We'll also show you how to clean and shape your data using Power Query and understand star schemas. You'll also get hands-on
experience with tax formulas and learn how to make your dashboard smarter using PowerBI with AI. Plus, we have got top 10 business analytics skills, key business intelligence terms every analyst should know. So let's get started and by the end you'll be able to Turn raw data into valuable insights. Also just a very quick information guys if you're interested in becoming a business analyst simply learn offers the perfect certification to help you achieve your goals. Now with this course you will learn how to solve complex business problems and then you'll gain essential skills all aligned with
IIBA babok V3 standards which includes CBA and CCBA certifications and then you'll also earn valuable certifications from IBM attend master classes with IBM experts and gets hands-on experience with real world projects and Harvard business case studies plus master tools like Tableau, Excel, SQL and even cuttingedge generative AI tools. tools like Chad, GPT, Dali 2, Gemini and Midjourney. So hurry up and enroll now and find the course link in the description box below and in the pin comments. It is no surprise that business analytics and business Intelligence are two of the fastest growing markets in the
world. Organizations today are generating data at a rapid rate. There is a need to use this business data and make smarter decisions. Companies are looking for methods and tools to turn business data into actionable insights. This is where business analytics and intelligence can help play a critical role. Business analytics is the process of collecting, analyzing and drawing valuable Conclusions from vast volumes of data available. It helps to improve business performance through fact-based decision making. On the other hand, business intelligence or BI is a technology that enables data preparation, data mining, data management, and data visualization.
It allows you to analyze data with queries and create reports and dashboards with the help of charts and graphs to be used by business leaders. Business analytics and business Intelligence together create capabilities for companies to compete in the market effectively. Consider this example. Suppose you sell homemade chocolates through an online store. Business intelligence provides meaningful insights into the past and current state of your business. BI tells you that sales of your milk chocolate have spiked up in Texas the past 2 weeks. So, you decide to manufacture more milk chocolates to keep up with Demand. Business
analytics asks, "Why did sales of milk chocolate spike up in Texas?" By scrutinizing your website data, you learned that most traffic has come from a post by a food blogger based in Texas who liked your milk chocolate. This insight helps you decide to send complimentary chocolates to a few other well-known food bloggers throughout the United States. Using cuttingedge business analytics and intelligence tools such as Microsoft Excel, SQL, PowerBI, and Tableau can enhance customer experience, improve efficiency, conduct competitor analysis, and accelerate growth. With this basic knowledge of business analytics and business intelligence, let's look at the
topics we'll be covering in this video. We will start with learning the need for business analytics and the responsibilities of a business analyst. Then we will look at the top business analytics skills and learn about Business analytics and intelligence with Microsoft Excel. After that we will see the basics of business intelligence and get an idea about agile and scrum methodologies. Next, we will learn to create reports and dashboards in Microsoft Excel and understand in detail how to visualize data with top business intelligence tools such as PowerBI and Tableau. Let's get started. We will understand the
importance of business analyst with a short and interesting Story. So, meet Rob. He runs a cafe in a small town far away. His cafe is one of the oldest and most popular iteries in town. Rob's cafe was usually popular amongst customers and it was doing very well until the onset of the deadly corona virus. Due to COVID 19 like other iteries, Rob was forced to shut down his cafe too. This took a heavy toll on his business and subsequently he lost his customers. None of his customers visited his cafe and this resulted in a huge
Loss for him. He knew he couldn't afford to close his business forever as it would take time for corona virus to be eradicated. But he was lost. This situation was new to him and he didn't know how to reopen his business amidst this pandemic. After a lot of brainstorming, he recollected reading about business analyst. He remembered that business analysts are professionals who enable a change in an organization. He felt like a business analyst could Help him sort out his current business problems. Hence, without wasting any time, he set out to hire a business analyst. He
hired Ted, the business analyst, to help him with his ongoing business problems. Rob entrusted Ted with reopening the business. The first step Ted took was to have a discussion with Rob and understand the business problems and the objectives. That is ideally the first step a business analyst would take. On Discussing with Rob, Ted learned that the business objective was to reopen the business and get at least 80% of the customer base back. In addition to that, Rob also wanted Ted to look for sustainable ways to reopen and continue the business in the long run amidst
the pandemic. Ted studied the case and he came up with a few suggestions that he thought was fit for Rob's business. His first suggestion was to develop an exclusive home delivery app for Rob's Cafe. This way, Ted knew that business will improve as customers prefer home delivery in the current scenario. Next, Ted suggested that Rob has work from home meal boxes added to his menu. Many professionals are working from home currently and having work from home meal boxes would be a good pick for such professionals in the middle of a busy day. Ted's third suggestion
was to bring down the selling cost by providing discount coupons that can be utilized by Customers. Having a discount will enable more customers to order from Rob's cafe. Of course, it was not possible to get the customer base back without any sort of price cuts in the current situation. Finally, Ted suggested that Rob's staff would facilitate home delivery orders. This way, he didn't have to lay off his staff and at the same time get the home delivery running. So, these were a few suggestions given by Ted. Yes, depending on the situation, business analysts can Take
up different approaches. So, after the suggestions were accepted by Rob for the app, Ted began to make sure that the development went well by collaborating with the IT team. Ted became the intermediary between the IT team and Rob. He provided suggestions to the team, checked the app through user interface testing and made sure that the requirements met well. The same applied to the other business requirements and changes as well. Ted held regular Meetings to gauge the progress and also kept Rob in the loop and updated him with the status of the project. Attending regular catch-ups
helped Rob gain an insight into the progress and give his feedback from time to time. Ted made sure that the entire case was well documented. By doing so, he could always refer to the documents in the future with similar cases as well. Ted made presentations that showed Rob the business growth after implementing the Changes and Ted always supported his presentations with data. Rob was very impressed with Ted's business approach. Such an approach not only helped Rob reopen his business amidst the COVID 19, but also helped him get 80% of his customer base back. Ted successfully
brought about a positive change in Rob's business which was highly beneficial. So that is how Ted the business analyst helped Rob make a business sustainable. This was the importance of having a Business analyst in Rob's organization. Don't you think every organization should have a business analyst? Well, yes. Depending on the business domain and the situation, the roles and responsibilities may vary. Let's start off and have a look at a day in the life of a business analyst through a small story. So meet Angela who is working as a business analyst in an application development firm.
Her firm builds applications for clients depending on Their requirements. Our next character is Rob. He is a budding entrepreneur with a vision of setting up his own e-commerce app. He plans on selling several electronic gadgets like phones, laptops, cameras, etc. on his app. So what is Rob's first step? Well, he approaches Angela's firm one day with the vision of creating his e-commerce app. Angela and Rob start talking business and Angela promises to help Rob with his app creation. She assures him That she will look into his business requirements and coordinate with him to get the
app running. Rob is happy about it and agrees to cooperate with Angela regarding all the business requirements from his end. Angela as we know is the business analyst. She starts planning Rob's project and without any delay she embarks on this project's journey. She has a set of planned steps that will help her fulfill Rob's requirements. But what are the steps she takes? Is her Approach going to be effective and quick? Let's find out the answers to these questions. Now, up next, you can learn about Angela's approach that helps her deliver Rob's project smoothly and without
any hassles. These steps that we are going to look at are the typical roles and responsibilities of a business analyst. Here we will understand these roles better with respect to Angela's and Rob's story. First and foremost, Angela understands Rob's business Objectives, problems, and requirements. Without understanding this, do you think Angela will be able to proceed? No. Hence, a business analyst like Angela understands the problems related to Rob's business and comes up with the right solution to achieve the goals of the business. She brainstorms around what is best suited for an e-commerce app focusing on electronic
gadgets. In the next step, Angela gathers all the necessary requirements. Here she Understands Rob's requirements and makes sure that they are on the same page regarding the project and its goals. Both of them together arrive at a stipulated deadline for the project completion. She gathers relevant information based on security of the app, the payment setup to login, cost of the products and style to name a few. Once Angela has an in-depth understanding of Rob's project and gathers all the necessary Requirements, she starts allocating resources by keeping in mind the budget of the project. Here she
recognizes and allocates tasks and resources to the development team. PAS work closely with the development team to design the solution for a problem. Angela ensures that the development team doesn't spend their time understanding Rob's requirements. In this step, along with the development team, she finalizes the software and Tools required to build the project. Angela doesn't rest until the project is delivered to Rob. She continuously monitors the progress and constantly provides her feedback to the development team with respect to the app's layout, design, and other features. She gives them suggestions in order to improve the application.
In the next step, Angela collects feedback of the prototype version of the app from the users. She notes down if the prototype is fine Or if it requires more work. PAS validate if the project is running fine with the help of use acceptance testing. They verify if the solution being worked on is in line with the requirements and ensure that the final product satisfies the user expectations. BAS also assess the functional and non-functional requirements. After collecting feedback, Angela moves to one of her most crucial duties and that is building reports. Data visualization is A key
skill for any BA. In order to gauge the performance of the app and get valuable insights from it, Angela builds reports using various data visualization tools like Tableau, PowerBI and Click View. Reports can be general reports such as detailed reports or it can be dashboard reports such as visualized reports with multi-dimensional analysis based on display of business indicators. It is not uncommon for issues to crop up amidst this entire process. Hence, Angela conducts regular meetings with the development team and Rob to solve problems quickly. Having these meetings will help Rob understand the status of the
project and it will also help the teams proceed in the right path. Throughout this project phase, Angela makes sure to maintain transparency. On completion of the project, Angela documents and presents the project findings to Rob. Generally, business analyst present the project outcomes to The stakeholders and clients along with maintenance reports. Angela notes down all the project learnings and details in a concise manner. This will help her take better decisions in the future and these documents will save her time while implementing the next project. Now that she has completed all her duties and responsibilities with respect
to Rob's project, she is ready to deliver the final e-commerce application to Rob for use. Rob is rest assured that the Application developed by Angela's firm is apt for his business and just what he wanted. Angela's streamlined approach made it easier for the project to be delivered within the stipulated time period. Rob is happy and I'm sure he will come back to Angela's firm for projects in the future. So those responsibilities that we saw Angela carry out are ideally the roles and responsibilities for any business analyst out there. Yes, some may vary Depending on the
company you work for and the project you're working on. Business analyst is a professional who is responsible for bridging the gap between IT and business teams. They use analytics to evaluate processes, determine requirements, deliver datadriven solutions, and generate reports to executives and stakeholders. Business analyst is an individual who is a part of the business operation and Works closely with the technology team to improve the quality of the services being delivered. They also help in assisting in integration and testing of new solutions. Growing a career in a field with high demand such as business analysis could
be a challenge and competition for business analyst positions can be intense. So you should have a clear understanding of the roles and responsibilities of a business analyst. A BA should successfully identify and recognize the organization's business objective. They should understand the business problems and think of a lucrative business solution. They need to understand and collect the business requirements from clients and stakeholders. Allocate the right resources and improve the existing business. Documentation of business findings is another important key role of a business Analyst. BAS interact with the development team to design the solution for solving a
particular problem. They often need to spend a certain amount of time in meetings in order to save the development team from spending their time in understanding the stakeholders requirement. They often give feedback on the layout of a software application as to what all features need to be added and what Functionalities should the application contain and also implement the newly designed features that a business needs. While BAS identify the needs, define the features, write use cases, uncover business rules, and manage issues, they should also gauge the functional and non-functional requirements in a business. Business analysts run
meeting with stakeholders and other authorities. Hence, discussing issues with the client face toface can do wonders and even help In solving problems quickly. They engage with business leaders and users to understand how datadriven changes to products, services, software, and hardware can improve efficiency and add value. They verify and validate if the project is running well with the help of user acceptance testing and the solutions are in line with the client's requirements. They also ensure that the product delivered satisfies the user Requirements. Finally, BAS write documentations and build visualizations to explain all the findings and draw
business insights. They also deliver maintenance reports. They need to develop informative, coherent and usable documents for the success of a project. Business analyst skills are a combination of technical as well as nontechnical skills often referred to as soft skills. The skills for a business Analyst are not only acquired through training but through experience and combined with the ability to understand situations and the motive behind the problem. So let's have a look at the top skills to become a successful business analyst. The first skill we have is understanding the business objective. For a business analyst, it
is important to know the goals and objectives of the business. It is advised that business analyst should have a good knowledge of The business operations in his or her organization. A business analyst should understand the problems related to the business and come up with the right solution. Business analyst should resolve the problems that have been identified and not avoid them. They work on individual actions and tasks that will build towards the achievement of the goals of the business. Objectives of the business can be to expand customer base in order To increase sales, scale up production
so that it is in line with the revenue growth, improve revenue streams through increasing perceived product value or increasing marketing budget according to the revenue. Business analysts should have the natural curiosity and determination to continue learning and figuring out how things fit together. Even as business analysts become managers, it is important to stay in touch with the industry and its changes. The next important skill a business analyst should have is analytical and critical thinking. Now there is a famous quote by Thomas Elva edition which says 5% of the people think 10% of the people think
they think and the other 85% would rather die than think. Business analysts are paid to think. A business analyst would be able to analyze and interpret the client's requirements clearly. Business analysts require good focus in Order to collect and understand the needs of the client. Critical thinking involves evaluating several options before arriving at the desired solution. In certain situations, a stakeholder may give a requirement that's not necessarily tied up to any business value, but rather to their own increased convenience. Applying critical thinking demands not taking all the statements of the stakeholders for granted. Critical thinking
allows the business analyst to Distinguish between requirements that add value to the business and those that should be given a low priority. A business analyst must be creative in order to reach stated goals where resources are limited and the conditions are non ideal. The third important skill for a business analyst is communication and interpersonal skills. Understanding and being properly understood is key to any profession. If You are unable to clearly specify and communicate requirements to any stakeholder, then you may not fully understand the requirements yourself. Being a business analyst is like being multilingual. You have
to speak several different languages while conveying the same message. Business analysts apply communication skills at every point. They use communication and interpersonal skills when the project is launched, while gathering requirements, when Collaborating with stakeholders, and also while validating the final solution. Listening, reading and writing skills are very critical for a business analyst. They should be capable of facilitating meetings. Business analysts use verbal and written communication to convey ideas, concepts, facts, and opinions to a variety of stakeholders. Non-verbal communication skills enable the effective sending and receiving of Messages, but not limited to body movement, posture, facial
expressions, gestures, and eye contact. Effective listening allows the business analyst to accurately understand information that is being communicated verbally. Fourth in our list of skills, we have negotiation and costbenefit analysis. Being a successful business analyst requires working with and interacting with many people. These people include clients, business Leaders, project team members, project stakeholders, vendors, private sector representatives, industry leaders, and so forth. Business analysts negotiate at every turn during the course of a project. At the initial stage of a project, negotiation skills are used to determine what should be included in the vision of the project.
As details emerge, negotiation skills are used by all parties involved to determine which requests become requirements and which Requirements have higher priority. As the project progresses, negotiation skills help to determine the functional design which fulfill the requirements. Technical decisions also require negotiation skills. Business analysts also perform costbenefit analysis to conduct an assessment of the benefits and costs anticipated in a project. When organizations undertake new projects, it is advisable for business analysts that they use costbenefit analysis to Establish whether such projects should be embarked or not. Business analysts should be able to achieve a profitable outcome
for your company while finding a solution for the client that makes them happy. This balancing act demands the ability to influence a mutual solution and maintain professional relationship. Up next, we have our fifth skill that is decision making. The quality of decisions made by business analyst is what matters a lot Because it has a direct impact on the company's business. Thus, it is important for every business analyst to think from all aspects before presenting their decision or strategy. They must be having good problem solving skills as well. Business analyst should have an act to think
out of the box and find a solution to problems. Majorly a business analyst follows five major steps while making a decision. These steps are define the problem, find and Define the alternative approaches, evaluate the alternative approaches, make the decision based on these approaches and test and finally implement the solution. While some may argue that the technical team is responsible for designing the solution, the business analyst still remains instrumental in ensuring that the design conforms to the requirements that have been approved. Now that we have reached halfway through the skills, I would like to ask all
our viewers to please subscribe to our channel and hit the bell icon to never miss an update from simply learn. Moving on, we have another really important business analyst skill that is idea about programming languages. Business analysts should have a good hands-on programming knowledge for performing better and faster analysis of data. Knowledge of R and Python is highly beneficial. Business analysts can Help solve complex problems by writing efficient codes. Both R and Python have a vast collection of libraries and packages for data manipulation, data wrangling, data visualization, and data analytics. Some of these libraries are
numpy, pandas, dlier, tidier, ggplot and mattplot lib. In addition to these, it is good to understand statistical software like SAS and SPSS. Using these programming languages such as Python, R and SAS, you can analyze and visualize Large data sets as well as create machine learning models for making future business predictions. The seventh skill in our list is creation of reports and dashboards. A business analyst should be proficient in using various business intelligence tools for creating reports and dashboards. Reports created by business analysts can be general reports such as detail report, grouped report, cross tab Report,
column report, query report, data entry report etc. Or it can be dashboard reports such as visualized report with multi-dimensional analysis based on display of business indicators. Dashboard reports are developed by business analyst to solve business decision-making problems. Different from the tabular interface of the general report, the dashboard report adopts the canvas-like operation interface. Knowledge of Tableau, PowerBI, and click View are required to make different types of reports depending on the business requirements. Now, the eighth skill in our list of skills is database and SQL. Business analysts often work with data that is structured in nature.
Hence to store and process this data they should have knowledge of relational databases such as Microsoft SQL server, Oracle database, MySQL database as well as NoSQL databases. Also having hands-on experience with SQL is a must for a business analyst to access, retrieve, manipulate and analyze data. So they should be able to write data definition and data manipulation commands such as create, update, delete and insert. Microsoft Excel is the ninth skill in our list. Excel is one of the oldest and most popular and powerful analytics and reporting tool used in the industries For working with data.
Business analysts use Excel to perform various calculations, budget analysis and data analysis to derive meaningful insights and take decisions. They sort, filter, and create pivot tables to summarize the data. Business analysts can also create different charts and graphs using Excel to generate dynamic reports related to a business problem. Business analysts can use Excel to create revenue growth models for new Products based on new customer forecasts. When planning an editorial calendar for a website, business analysts can list out dates and topics in a spreadsheet. When creating a budget for a small product, they can list expense
categories in a spreadsheet. update it monthly and create a chart to show how close the product is to budget across each category. Business analysts can calculate customer discounts based on monthly purchase volume by product. They can even summarize customer revenue by product to find areas where to build strong customer relationships. And finally, in the list of skills, we have documentation and presentation. You could have all the industry experience in the world, but if it's paired with poor business analyst practices, you could be more of a risk to the organization than a business analyst with no
industry experience at all. A business analyst must be able to Document their project learnings and results in a concise and compact form. They should also be confident about presenting their findings and conclusions in front of the stakeholders and clients. Organized documentation will help you communicate technical concepts to nontechnical employees. It is important that a business analyst notes down all the details that they learn from their projects. This will help them take better decisions in the Future. Also, if similar problems arise at a later stage, they can implement the same solution, thereby saving a lot of
time and unwanted problems. While business analysts are generally not responsible for making decisions regarding project solutions, decision-making skills are still important for understanding, gathering, and presenting relevant information to assist decision makers with selecting the optimal solution. With that, we have Covered our top skills for a business analyst. If you have any questions related to the skills that we covered, then please put it in the chat section. Our team will help you solve your queries. Now let me tell you how simple can help you grow your career in business analytics and help you become a business
analyst. So let me search for SimplyLearn here. This is the SimplyLearn website and on the search bar let me look for Business analyst. You can see there are a few courses related to business analyst. So let me open these two courses. So let's go to the first course. So this is the business analyst masters program. Now this program is endorsed education provider is IIBA. Now if you look on the right we have the different courses that will be covered As part of this M's program. So there is introduction to business analysis, certified business analytics professional.
You'll also learn about agile and scrum. There's business analytics with Excel. You'll also get training in SQL. There's Tableau training. And you'll also get to work on business analyst caption projects. If I scroll down now, here are the tools that will be covered as part of this course. So There's Microsoft Excel, Zeta, Tableau, PowerBI, Postgress SQL, then there's Plan Box, target process and others. Here you can see the program advisor. And if I scroll further, this the entire course content we have. These are the different courses that you will be learning in this course. And
after you finish the course, you will receive a certificate which will look similar to this. So please go ahead and enroll to this course if you want to start your career in business analytics. Now let me take you to another program. We have post-graduate program in business analysis. Now this is in partnership with PUI University and endorsed education provider is IIPA. If I scroll down, you can see here the key features of this course. You'll get Purdue postgraduate program certification, alumini association Membership, master classes from Purdue faculties, enrollment in simply learns job assist. There's 170
plus hours of blended learning, 11 plus hands-on projects, custom projects in three domains. If I scroll further on the right, you can see this is the produce certification that you will receive after finishing the course and you will also get the certificate received by international institute of business Analysis that is IIBA. Let's scroll down. You also have the advantage for enrolling to simply learn job assist program. So you will get IM jobs pro membership for 6 months. Rumé assistance and career monitoring. You'll also have interview preparation and career affairs. Now here you can see the
program details. So you'll learn about an introduction to Business analysis, certified business analysis professional, the agile and scrum, business analytics with Excel Tableau training, business analyst capstone project and you also have the opportunity to enroll for some electives. So we have Purdue University business analysis master class. You can also enroll to a PowerBI course and there's agile and scrum foundation. If I scroll down here, you can see the Skills that will be covered as part of this program. So there's business analysis, there's alicitation and collaboration, requirement analysis, planning and monitoring. Let me click on view
more. You have strategy analysis, dashboarding, wireframing, there's data visualization, statistical analysis using Excel, SQL database, there's requirement life cycle management and lots more. So these are the tools that will be covered in this Course. We have Microsoft Excel, Zera, Fogbars, Plan Box, that's Rally, PowerBI, Process SQL, that's version one, target process and others. And now this is the important part. These are the industry related projects that you will get to work on once you enroll to this course. So the first project is canteen ordering system for uni liver. We also have library management system for
Stanford University. There is WhatsApp pay and You can see the description of these projects mentioned below. And you also have hospital management system for Mayo Clinic. There are course advisor for this course. Now they are directly related to Purdue University. I scroll further. This is the learner's profile and how the industry trend has been for business analysts. So please go ahead and enroll to this program if you really want to start your Career or you want to grow your career as a business analyst. Now here is a quick road map that depicts what a fresher
needs to possess to become a business analyst. First they need to have a graduation degree in a related field. Then knowledge of SQL and relational database is very important. Thirdly, a fresher should have good hands-on experience with programming languages and that's a prerequisite. And finally, they need to have good Communication skills to nail the role of a business analyst. Up next, we have the road map that depicts what an experienced professional needs to possess to become a business analyst. Firstly, they should have good knowledge of the domain they're currently working in. Next, they should know
how to write SQL queries. An experienced professional should be good with programming languages. In addition to that, they need to have Good communication and negotiation skills. Finally, they should be good at creating interactive reports using business intelligence tools. In addition to that, having a certification offered by International Institute of Business Analysis such as certified business analysis professional would be highly beneficial. Now, let's see what PowerBI is. PowerBI is a business analytics service provided by Microsoft that lets You visualize your data and share insights. It converts data from different sources to build interactive dashboards and BI
reports. As you can see, we have an Excel data about sales. Using this data, PowerBI helps you build different charts and graphs to visualize the data. Now that we have understood what PowerBI is, let us look at the important features of PowerBI. First is PowerBI desktop. PowerBI desktop is a free software that you can download and It allows you to build reports by accessing data easily. For using PowerBI desktop, you do not need advanced report designing or query skills to build a report. Second, as already discussed, PowerBI supports stream analytics from factory sensors to social
media sources. PowerBI assists in real-time analytics to make timely decisions. Third, support for multiple data sources is one of the major features of PowerBI. You can access various sources of data such as Excel, CSV, SQL server, web files, etc. to create interactive visualizations. And finally, custom visualization. Custom visualization is another vital feature of PowerBI. While dealing with complex data, PowerBI's default standard might not be enough in some cases. In that case, you can access the custom library of visualization that meets your needs. Let us jump into discussing the various components of PowerBI. As you can
see, there are six major components Of PowerBI. Now, let's discuss them one by one. First is Power Query. Power Query is the data transformation and massup engine. It enables you to discover, connect, combine, and refine data sources to meet your analysis need. It can be downloaded as an addin for Excel or can be used as part of PowerBI desktop. Second, we have Power Pivot. Power Pivot is a data modeling technology that lets you create data models. It also allows you to establish Relationships and create calculations. It uses data analysis expression language or DAX to model
simple and complex data. Third, we have Power View. Power View is a technology that is available in Excel, SharePoint, SQL Server, and PowerBI. It lets you create interactive charts, graphs, maps, and other visuals that brings your data to life. Next, we have Power Map. Microsoft's Power Map for Excel and PowerBI is a 3D data visualization tool That lets you map your data and plot more than a million rows of data visually on Bing maps in 3D format from an Excel table or data model in Excel. Then we have PowerBI desktop. PowerBI desktop is a development
tool for Power Query, Power Pot and Power View. With PowerBI desktop you have everything under the same solution and it is easier to develop BI and data analysis experience. Finally, we have Power Q&A. The Q&A feature in PowerBI lets you Explore your data in your own words. It is the fastest way to get an answer from your data using natural language. An example could be what was the total sales last year. Once you have built your data model and deployed that into PowerBI website, then you can ask questions and get answers easily. Now, let's see
what PowerBI service is. PowerBI service is the software as a service part of PowerBI. It is also referred as PowerBI online. To access PowerBI service, you need to log into app.powerbi.com. Now, let me show you that. I'll go to Google. I'll open a new tab and search for app.powerbi.com. It's loading. But this is how the homepage of PowerBI service looks like. I've created some dashboards on it. First, you need to log into app. PowerBI service. You can see I'm logged in. Now under my workspace if I go to dashboard here I have created a Finance
dashboard you can see the different charts and graphs I have prepared and pinned it to the dashboard. So PowerBI service allows you to connect to your data create reports and dashboards and you can also ask questions to your data. Now as you can see in this dashboard we have created some charts and graphs. So this is a tree map. There's a pie chart. There's a bar graph. Below you can see there are line charts and donor charts. It tells You the total sales that were made, the total number of units sold, the sales by product,
sales by country, sales by segment and lots more. One of the key features of PowerBI is creating dashboards from multiple reports and data sets. PowerBI dashboard is a single page visualization to tell a story. The visualizations on a dashboard are generated from multiple reports and each report is based on one data set. A single page dashboard is known as a Canvas. The visualizations you see on the dashboard are called tiles. These tiles are pinned to the dashboard by report designers. Now let me go back to my dashboard. So this is called a canvas and each
of these are called tiles. So on the top you can see we have three tiles. Now let's understand how to create and publish reports in PowerBI dashboards. PowerBI allows you to create different reports on PowerBI desktop. These reports can be published on the PowerBI dashboard using PowerBI service. Here you can see there is a PowerBI report created on PowerBI desktop. If you click on publish, it will take you to the PowerBI service where you can build a dashboard. Here is the button for PowerBI publish. Once you click on PowerBI publish, it will take you to
the dashboard. So this is a single page PowerBI dashboard on PowerBI service. Now let's understand the PowerBI architecture. PowerBI architecture is a Service built on top of Azure. There are multiple data sources that PowerBI can connect to. PowerBI desktop allows you to create reports and data visualizations on the data set. PowerBI gateway is connected to on-remise data sources to get continuous data for reporting and analytics. PowerBI services are basically the cloud services that are used to publish PowerBI reports and data visualizations. Using PowerBI mobile apps, you can stay Connected to their data from anywhere. PowerBI
apps are available for Windows, iOS, and Android platforms. Have you ever wondered how companies use data to make important decisions and improve their operations? Well, that's where business intelligence comes in. Business intelligence, also known as BI, is a set of tools and processes that help organizations analyze data and make informed decisions. It's like a crystal ball that helps businesses see into the Future and make strategic decisions based on the data analysis. But how exactly does BI work? Well, it starts with collecting data from multiple sources like transaction data, customer data, and social media data. This
data is then processed and analyzed using BI tools like dashboards, reports, and analytical models. The insights provided by BI helps organizations identify trends, patterns, and opportunities that they can use to make better decisions And drive growth. So in this video we are going to take you through the core concept of what business intelligence is and why is it so important. But before that if you enjoy watching such videos and find them interesting then please subscribe to our channel because we bring the best videos for you daily. Also hit the bell icon to get notified whenever
we drop a new video. Data present in this world is never ending. So are the jobs for people working on This data to make proper insights. Well, at its core, business intelligence, often referred to as BI, is the process of collecting, analyzing, and transforming raw data into actionable insights for business purposes. It helps organizations make informed decisions, enhance performance, and gain a competitive edge in today's dynamic market. So, let's understand the steps involved in the processing of data. The first step in the business intelligence Process is data collection. Businesses begin by identifying the relevant data
sources for their operations such as internal databases, customer interactions, website analytics, and social media platform. Once identified, the necessary data is extracted from each source which can be in various formats like spreadsheets or databases. Before proceeding, it's important to cleanse the data by removing duplicates, errors, or inconsistencies, ensuring its Accuracy and reliability. The second step is data integration. With the collected data in hand, the next step is data integration. Businesses transform the data into a unified format that is compatible and consistent across different data sources. This transformation involves standardizing data formats, organizing fields, and creating
a unified data structure. The transformed data from various sources is then merged into a central repository or Data warehouse serving as a single source of truth for further analysis and reporting. The third step is data analysis. Businesses select appropriate analysis techniques based on their objectives which can include exploratory data analysis, statistical modeling, data visualization or machine learning algorithms. Using these techniques, businesses uncover patterns, trends and correlation within the data. These insights provide a valuable information About customer behavior, market trends, operational efficiency, product insights and more. To communicate the findings effectively, businesses create reports and visualizations
that present the insights in a clear and understandable format. The fourth step is decision making and action. The final step in the business intelligence journey is turning insights into actions. Armed with the insights from data analysis, businesses make informed decisions. These decisions Can range from optimizing operational processes and refining market strategies to improving customer experience and identifying new business opportunities. Businesses continuously monitor the impact of their decisions and actions using feedback loops and iterative processes to refine strategies, adapt to changing market conditions, and improve overall performance. Have you ever wondered how successful people can be
after opting a post-graduate program in Business analytics? Hear it from our learners who have experienced massive success in their careers. Hi, I'm Gordana Dujik. I live in Raleigh, North Carolina and working as a business analyst at Revature. This is my second job since I completed the postgraduate program in business analytics at Simple Learn with Fiji University in December 2021. My experience with Simple Learn was great. The live classes were my favorite Because they were very interactive and I could connect with instructors. I have many small gaps and short employments in my career. I even have
a huge 2-year career gap due to some health issues. However, I think the dark times are now behind me. It doesn't matter how challenging the path may be. Keep upscaling and one day you will achieve success in life. The next question is why is business intelligence so valuable to businesses? So let's explore its benefits. The first is enhanced decision making by providing accurate and timely insights. BI enables organizations to make informed decisions based on facts rather than assumptions or intuition. This leads to improved efficiency, reduced risks and better outcomes. Increased operational efficiency. Business intelligence streamlines
operations by identifying bottlenecks, optimizing processes and highlighting areas of improvement. This Leads to enhanced productivity, cost savings, and overall efficiency gains. Competitive advantage. In today's competitive landscape, gaining a competitive edge is crucial. BI equips businesses with actionable insights about market trends, customer preferences, and competitors, enabling them to make strategic moves, and stay ahead of the curve. Applications of business intelligence. Retail BI helps retailers analyze customer buying Patterns, optimize inventory levels, and personalize marketing campaigns, ultimately improving sales and customer satisfaction. Healthcare. By leveraging BI, health care providers can analyze patient data, optimize resource allocation, and identify
patterns to enhance medical treatments and patient outcomes. Finance BI enables financial institutions to assess risk, detect fraudulent activities and identify investment Opportunities resulting in improved decision making and financial performance. Let's understand the tools for business intelligence. A number of BI tools are already present in the market. But let's go through the top business intelligence tools. PowerBI. Microsoft's PowerBI offers robust data visualization capabilities, interactive dashboards, and integration with various data sources. It's user-friendly and allows For collaboration and sharing within the organization. Tableau Tableau is renowned for its interactive and intuitive data visualization features. It supports advanced
analytics, custom calculations, and offers seamless integration with multiple data sources. Click view. Clickview provides a comprehensive business intelligence platform with powerful data discovery, visualization, and reporting features. It allows users to explore data from Various angles and uncover hidden insights. Looker Lucer offers a cloud-based platform for data exploration and visualization. It focuses on providing real-time insights and collaborative analytics, enabling teams to work together seamlessly. So, there you have it, folks. Business intelligence is an essential tool that can help businesses succeed in today's marketplace. Let's do some practical hands-on demo with PowerBI. So, this is How the
PowerBI desktop interface looks like. On the left, you have the report view, the data view, and the model view. The report view is where you visualize your data with different charts and graphs to build reports. The data view allows you to view the whole data, while the model view is where you check if there are any relationship between the tables. On the right, you can see the different visualizations that you can build. We'll quickly run through all of These in our demo. So here you can see there's a finance sample data that will help you
draw insights about the sale of products in different countries. We will create a report to visualize different charts and graphs and analyze those sales. So let me go to my PowerBI desktop. First, we'll import our data. So let me go to our get data tab and choose Excel as my data source. I click on Excel. So here is our finance sample data. We'll select sheet one. You can see the data here. Click on it and then select load. This might take some time to load the data. Now, if I go to my data tab, you
can see the entire data set. It has fields such as segment, country in which the sales was made, the name of the product, the units sold, and the sales price and many more. Let's start building our report now. I'll go to my report view. So, first let me create a Text box. Let me resize it. Let me name it as finance dashboard. We'll increase the size of the text. We'll use font consulus. Center it. We'll also add a background to this. We use blue color. Change it to white and increase the size. Now, let me
first show you how you can create a matrix. I'll go to visualizations and click on matrix. Let me resize it. From the data sheet tab, I'll select sales and drag onto values. So you can see the total number of sales that were made. Now let me do some formatting. So I'll go to the format tab, click on column headers. Let's add a background color and let me increase the text size to 20. Similarly, under values, we'll increase the size of the text to 20 as well. We can also click on border and choose the color
of the border. We take as let it be black. So this is a simple matrix that we created which shows the total number of sales That were made. Similarly let me choose matrix once again. Now we'll drag on the units sold onto values. We'll continue with the same drill. Under column headers we'll add a background. This time let's choose some other color. And under values, let's increase the size of the text to 20. Even for the column headers, let's increase the size of the text to 20. Again, we'll switch on border. Me resize a bit.
So here we have two matrix created for our report. The first matrix shows us the total sales that were made. The second matrix shows you the total units that were sold. Now let's move ahead and create a simple bar chart. So under visualization I click on clustered column chart. Under this we'll drag the date column onto axis and the sales onto value. Let me expand it. So it shows you the sales per year. This is the sales that were Made in 2013 and this shows you the sales that were made in 2014. Now there's a
drill down option which gives you more granularity. This depicts the sales by quarter. If I drill down further, you can see this shows you the sales by month. Also, you have some options like sort by and sort by sales. So you can see October month made the highest number of sales. Moving ahead, let me now create a pie chart where we will see the sales by different Segments. Under visualization, I'll click on pie chart. Let me first resize it. Here I'll drag the segment column onto the legend and the sales column onto the values. As
you can see we have the sales made by different segments. Government segment made the highest number of sales with 44.22%. Now let me add a border to both the visualization. I'll click on the pie chart and go to the format tab. I'll Switch on the border. Similarly, for the clustered column chart, I'll go to the format tab and click on border. Now, let me resize a bit. All right. Next, we'll create a very simple table that will depict the total sales made by each product. So, under visualizations, I click on table. Let me bring this
below. So, from the data sheet, I'll first drag product on to values. You can see the different products and then sales just below it. So this depicts the total sales that were made by each product. And finally it displays the total value of the sales that were made. This is same as the one shown here. Now let's do some formatting. Under format tab I'll go to values and increase the text size to 15. Let me expand it. Also under column headers I'll increase the text size to 15. Then let me go and add a border.
Now let me create a map that will show you the sales that were made by each Country. So first let me create a new page and under visualization I'll click on map. Now I'll drag the country column onto location. So you can see we have our map ready and we'll drag sales onto size. You can see the different countries and the sales that they made. If I move the map, you can see the sales made in the Europe region. Let me resize it. I'll add a border to this. Now, let me go ahead and create
a donut chart that will show you the profit by each segment. Under visualizations, I'll click on donor chart. I'll move this to the top. Now from the data sheet I'll add profit onto the values and segment onto the legend. If I expand this you can see government segment made the highest amount of profit with 65.04%. Let me resize this and we'll add a border. Okay. In the final visualization I'll show you how to create a tree map. This tree map will tell you the total amount of sales made by each product. So under visualizations, I'll
click on the tree map. Let me expand it. I'll drag sales onto values and product onto group. So here you can see our tree map and the sales made by each product. You can see now we have our report ready. We have created two separate canvas to visualize our data. Now if you want to change the Color of these bars then simply go to the format tab and under data colors you can choose whichever color you want. In PowerBI desktop you have an option to switch your theme. This will make your dashboard or the report
look more attractive. So now we are under the default mode. Let's try out different themes. That's frontier. It's temperature solar which is a little yellowish. The one which I like is title. I hope this was helpful in making You understand the basics of PowerBI and how it works. You learned the various features and the components of PowerBI and looked at the architecture of PowerBI. Finally, you saw a demo to create a report using finance data set. Now, let's get started by how to install PowerBI in Windows operating system. To install PowerBI, go to Google and
search for PowerBI desktop download. Now click on the first link and then you will be redirected to the official web page of Microsoft PowerBI. Here you have to scroll a little towards bottom and select your language which is English and then click on download. This will give you two options. So based on your processor, select one. My system is 64-bit. So I'll be selecting this one. Now download. The file will be 558 MB. So it might take a little time. Now you can see your Microsoft PowerBI is getting downloaded. So if we go into the
download section, you can clearly see It. So here it is getting downloaded. It might take a little time. Now the file has been downloaded. Just run the file and you can directly go to next. Since the default language is English and in case if you want to select a particular language, you can go to the select language option and select your particular language you're searching for. It might take a while. There you go. So the installation process has been Started. Now you can click on next. And now you can read all the terms and agreements
and click on accept. You can change the location if you want but I'll be keeping it as default. Again if you want to create a shortcut you can on desktop. I'll keep it as it is. Now install. It might take a while. So PowerBI has got successfully installed. You can see the desktop icon there. Double click on it and you can start PowerBI. So firstly, it is open source. If you want to have a licensed version of it, just get the license or buy now option. And for now, let's use the open source version. So
this is the PowerBI window and those are the charts and these are the data connections. If you want to get connected to your data sources, you can also have shortcuts to that. Now what are the steps to connect to data? So now we will go directly into PowerBI and try to import one by one few most commonly And popularly used data sets which are most commonly used uh in a day-to-day activity. Rest of course there are PowerBI supports any number of data sources uh but we will do something practical on the most popular ones. So
let's let's open our PowerBI. Now this is my PowerBI and first I want to show you that how can I import data directly from a web page and import the data. Now it is asking for a URL in order to import data. So what I have Done is I have created a Google Excel sheet with simple data with rows and columns and what I have done is I have shared this uh sheet as publish to web. Okay. So you just need to say publish to the web the link as web page and say done. It's
it's automatically published and say link. So copy the link which you have published on the web. Copy this link and then go back to your Tableau. Paste it link over here and click okay. Now PowerBI will try to establish a Connection with this Google doc sheet because it's published on the web. You need to wait for a while while it is reading. Okay, now it has read one of the HTML tables. So I'll select this one. Now you can see it has it is showing me a preview of the table which is there on my
Google sheet, right? It has 11 rows. So it has all showed all the 11 rows. So now I can go and transform this data because I can see my headers are there Starting from the second row. So there's an opportunity for me to transform the data. So I'll go and transform it so that it looks clean. Okay. So first is I need to remove the first row which is the null row. Remove the top rows. Okay. And then I need to use the first row now as a header. So you just click this option use
first row as headers. That's it. So now if you see my row ID, order ID, order date, ship date, all my Data is now ready. So I can say close and apply. Click apply changes. Now this is an example of web data import. You can go and preview your data right now. Uh the biggest advantage of this data connection is that it's a live data. So for example, I insert another row. Let me change the order ID. Some some I've changed some basic stuff and I It's autos saved. Control S. Now I'll go to my
tableau and I'll refresh. Now you can see as I refreshed my power query editor, I clicked refresh all and I got my new row which is there in the live data. I got that fetched from my Okay, I got that row the row row ID number 12. So I I have to say close and apply. Now you can see the new row, the row number 12 is now available in my new data set in the data set because it's a Live connection. It's a live connection with the webbased Google sheet. Okay. So this is one
important way in which you can import data. Now let's try to import data from a text file. Now I have already prepared a text file called subcategories.txt. Now let me just open it in a notepad. Now it's a very plain simple file tab separated file in which you have product subcategory ID, subcategory name and product category key. So basically to Which product category this particular subproduct belongs to. Right? So, what I'm going to do is I'm going to go back to my get data option and I'm going to select text/ CSV option and I'm going
to select option product subcategories.txt. Okay. So now PowerBI has identified that it's a tab delimited file. It has recognized the headers etc. Right? And I can now directly load this file. Okay. So now once the data is imported In PowerBI, it is like irrelevant to me. It's a composite data in import right. So in my presentation when I'm talking about importing data there are different importing modes, right? Import data import can happen through different ways. Okay. one is direct query mode in which I create a live uh connection to the database which I'll also show
you uh using MySQL and MS SQL server and also you can do a composite mode in which you Can have data imported from Excel plus you can have direct query modes so you can have multiple uh modes to connect and create a composite data model and that's what we are doing right now in our practical so what we are doing over here is one we have imported data from the web second we have imported data from a text table now after doing text now our next task is to import from CSV let's try another one
so now I have imported product subcategory Now I'll import a CSV file so again I'll choose the option text/ CSV and now in this CSV file let me open this CSV file file and show you what is it. So this is a list of all my products, product key, product subcategory key, product uh stock keeping unit etc. A simple CSV file and I'm going to import that. Okay. So now it is identified the delimiter is comma rather than a tab and It has already recognized the headers correctly. So I'll load it. Okay. So now my
products are there. product subcategories are there for product categories. Now what I have done is I have created a Excel mode now. So now Excel I'm using to import my product category. So now I have to click on the option of import data from Excel and I'll say product categories. Select the sheet. Load And now so my products product categories product subcategories do with different uh uh data storage types but still now the data is imported into PowerBI. It is a composite data model. Now another very important data type which you can import is the
PDF also. Right? So what I have done is I have created a PDF called customers. My customers data is lying in a PDF. So what I've done is I've created a PDF which has data for some columns are There like you know customer key, prefix, first name, last name, birth date, marital status, gender, email address, annual income, total children etc etc. So this is the data set which I have created in PDF. So what I'm going to do is I'm going to select PDF now and import customers PDF. And see it has recognized my table
on page one which I'm going to load. Okay. You can rename this as PDF Table. So this basically these are the different type of data types we have imported PDF, Excel, text, CSV and web page. Now let's take a look at another interesting data set which we want to import is the my SQL server data set. So what I have done is I've already installed MySQL server on my local instance and there's already a schema of SQL live tutorial over there and I have certain tables already prepared over there like department employee etc. So my
goal is now to import this data or create a live connection with this data set. Now in order to import my SQL database connection in PowerBI you need to first download a connector MySQL PowerBI connector. So you need to go to this link And then click on download and install the MySQL connector based on the operating system you have. You click on download and install it. After you have done this, go back to PowerBI and then give the IP address of the database. In my case, it's there in this local machine and the schema which
I want to import is SQL live tutorial. So, I'll give the name. Click connect. Okay, now it's connected. So now it is Asking me which particular tables you want to create a connection with. I'm choosing department and employee and I'm just loading them. Okay. So now this is the exact data which is there in the employee and department in MySQL. Okay. So this is one example of how to create connectivity between PowerBI and MySQL. Now I want to do the same thing using SQL server, Microsoft SQL server. So I have also installed Microsoft SQL server
On my machine and I have used the SQL Express. So this is the name of my server. So which I'll copy the server name and go to get data. Select SQL server and for now database is optional. I can say direct query. Click okay. Okay. Now it is showing me what all tables I can import. So in my SQL server tutorial in my SQL server I have I have these three tables customers employee attrition Olympic events. So I Can use probably the customers one which is Now you can see this is the data the customer's
data which is lying in my SQL server. Okay. So I can preview it and load it. So now you can you can preview the data in uh PowerBI that this this is the data. So I can rename is customers from MSSQL and this is from my SQL And okay. So now this is not the only uh data sets you can import. Now if you take a look at the options which PowerBI gave of what different type and variations of data it can it has compatibility to import from. Okay. So we can just take a look
at the categorization on the left hand side first. There are file based like excel, text, XML, JSON is also possible. You Can evenly directly import entire folder and within the folder whatever uh data types of files are there it'll detect it. PDF, park key or even shareepoint folder which is itself a Microsoft uh technology. Then different kind of databases SQL server and MySQL we just saw but it's not only limited to this. You can connect to Microsoft Access, SSAS, Oracle database, IBM DB2, Postgress, uh, Caiase, Terodata and then SAP uh, uh, databases, Amazon, Red Shift,
Impala, Vertica, Snowflake and any number of databases which are there in the market today uh, Amazon etc. Then it also allows you to connect with its own power platforms. PowerBI platforms, data mods, PowerBI data flows, data vers etc. Azure, there are different kind of storage uh mechanisms in Azure and Azure itself is a Microsoft technology. So it has a compatibility with lot of Azure uh based data stoages like Azure SQL database, blob storage, Uh Azure data bricks, right? Azour HD inside Spark. So if you have those kind of services running on your Azour cloud services,
you can even import them over here. Now online services like you know you have ERPs running uh or some data which is shared on the internet if you want to import it uh that is also possible through certain products uh Dynamics 365 Microsoft Exchange online Salesforce Google Analytics Adobe Analytics GitHub Uh LinkedIn sales if you want to do some analysis of some social networking uh you know feeds that also you can import. Then other miscellaneous are also there. Web based, hive, R script, Python script if there's something to import, get data from uh Google sheets
like we saw one example in our video right now. So there are multiple options available. Now once you have imported the data which is relevant to you um in our Subsequent sessions we will see how to create relationships but just giving you a glimpse that whatever data you are importing PowerBI auto detect certain relationships and it'll create for you but then you can go and manually also change. So this is the composite data model which is getting created in the back end while you are importing the data. You can easily go and manage these relationships
either keep them as is you can delete and create new ones manually. So there is no limitation in that. So this is what we have witnessed. We have imported data from different files types, data types and then you know we have tried to once it is imported into uh PowerBI then there is no limitation of how you use it. You can create visualizations across different data sets and then create your standard reports. So this is the example of importing data from web, importing data from a Database, from a PDF and then once you have data
you can shape and combine data, you can basically do what whatever transformation you want to do you want to uh make joins merge the data. So for example, if we go back to our PowerBI and if I go back to my transform data section. Now as I have now different data sets available with me, I have I can do any kind of u you know operation transformation on the data, right? Uh so like I showed you I uh upgraded the Header row because one of the imported data was not showing the header correctly. uh or
this columns like this exact one column is extra. I can remove the column, right? All those transformations, whatever I do in the back end gets captured in the applied steps section, right? This is the customer data. You can create uh you can merge it, you can append it uh you know with other data Set, right? Let's for example, I want to create a merge data set of my categories and subcategories. So I can say mer select these two data sets and say merge queries as new and I can select product categories and product subcategories. Select
product category key on both the side and then take do a left auto jog jog jog jog jog jog jog jog jog jog join. So whatever product categories are there, I'll get the subcategories associated with it and I'll create a new table which will have now I have the table which has the category and the subcategory and subcategory in one table itself. So I can rename it now to as category subcategory table. It's a it's a merge. Basically, it's a join between category and subcategory. And now I have a common Table, right? And I can
close and apply. So imagine I have created a new table which is imported created from one data set is which is Excel based and another data set which is text based. See this category subcategory table. So now I can use it the way I want in my visualization reports. So that's what the presentation says right that once you have uh the imported data you can shape you can combine you can adjust you can do whatever transformation you want To do and create your visualization. What is PowerBI? It is a business intelligence tool to visualize your
data and share insights across your organization. So when we talk about BI, it came into existence as a self-service BI tool and it does have different components which can be used. Now before we get into details of PowerBI, let's understand what are the different components or what are the different ways in which you can work on your PowerBI. Now one of the main challenges when it comes to organizations or users is that data is scattered in different places. It might be in different formats and anyone everyone would want to use that data to basically perform
some calculations create visualizations or dashboards which could be interactive and that's where they would want to bring all the data in one place might be transform it so that you can filter out and not load Huge amount of data in your system and you can work on selective data. So when it comes to PowerBI, it helps us in ETL, it helps us in data modeling, it helps in data storage and reporting. So what are the benefits of PowerBI? Here are some of the benefits. So extract intelligence rapidly and accurately. So that's basically transforming your enterprise
data into rich visuals and accurate reports for enhanced decision making. Now one thing we already know That when we talk about data data in raw format might have lot of hidden information. If we look at different data sets which I'll show you in the process, it might have lot of meaning but then the real meaning comes out of the data if we can create visualizations, if we can create relationships between different data sets and thus that can help us in enhanced decision making. Now PowerBI supports advanced data services. It integrates seamlessly with advanced cloud services
like Cortana to provide results for the verbal data queries as well. When you talk about seamlessly integrating with existing applications, that's one more benefit of PowerBI. So, it adopts analytics and reporting capabilities easily to embed interactive visuals quickly in your applications. You can build rich personalized dashboards. So it basically provides a Unified user experience with customized dashboard and reports that meet your exact needs. It also has a way where you can have secure way of publishing your reports. So you can set up automatic data refresh and rapidly publish reports allowing multiple users to avail the
latest information across your organization or across your working community. So PowerBI can connect to different sources. We'll see that in a while. So Basically you have an option which says get data and that basically opens up a window where you can find different type of data sources such as Excel, your CSV or text, JSON, PDF, getting data from databases or directly accessing data from databases. Now before we get further into understanding how PowerBI looks like, it would be good idea to share information and how you can set that up on your machine. So when it
comes to your PowerBI and let me open Up a notepad here. So for example, I bring up a notepad. Let's say when you talk about your PowerBI components. So you basically have PowerBI desktop and that's mainly your playground or that's mainly used for any kind of development activities. You have your PowerBI server or you can say service. Now, this one is where you would make reports online And share or make them available to different BUS. Now, that's one more component of PowerBI. And then you also have your PowerBI mobile which is mainly for viewing the
information or I would say viewing reports. So these are the three main components. We can also look at the licensing information of these. So these are the main. So PowerBI desktop is something which you can set up on your Laptop or on your machine. PowerBI server is where you can login with your user ID and password. and PowerBI mobile is mainly to view your reports. Now, how do you set this up before you can explore or start working on PowerBI? So, here is a link which you can basically use. So, if you look into this,
so this one basically says service self-service sign up for PowerBI. It says sign up of PowerBI service as an individual. Normally when you would want to use PowerBI you can use a website called HTTP and then you have basically app and let's say I think it's called app powerbi.com. Now this is the place where you can basically log in. Now if you see here I have created an account and if you look at my account it says auatl.onicrosoft.com. Now how do you get this kind of email? Because when you talk about powerbi it Will expect
you to have a official ID and it does not take ids which are from common domains such as Google or Yahoo and so on. So this particular link gives you an idea how you can do that. So basically you have what is PowerBI basic explanation on that. It says signing up for PowerBI service. So PowerBI desktop it's a totally free download and then you have mobile apps also a totally free download. And here it says that what kind of email addresses it supports. And If you look into this, you have to either sign up because
that does not accept your private email ids or you can go for this one which says enroll US government organization and this is where you can basically sign up for powerbi. So it basically says try free if you go to the website say powerbi.microsoft.com or you could go into this one which I was saying http slash App dot your powerbi.com and this is what you can use or as mentioned you can go to powerbi.microsoft.com for example if I open this in a different tab it takes me to powerbi microsoft I can say start free I
can click on this it says try free but then when it asks you to sign in this is where some of us face problem because it does not take your private email ID. Now, how do you tackle that? What you can do here is on this page which says learn about alternate ways to sign up. You can basically open up this link and in this link it says sign up for PowerBI with a new Microsoft 365 trial account and what you can do is you can basically click on this link which takes you to the
Office 365 and what you can do here is you can search for something which says say 365 E3 and here you have tried it for free. So in my case it is translating. Okay. So here you have an option which says try it for free. Click on this one and then basically go ahead with your sign up process. Now once you do that you can basically create an account or give a email ID. So it asks you to give an email ID to check if you already have an account and once you do that it
will guide you through the process where you can create an account like I have done. Now once you have done that so for Example we can go into my this page which I said http/app powerbi.com. Now once you have created an account you would be asked to login. Now I can click and login here and then basically given my password and once I do that it takes me to the powerbi server or service. Now on the top right it might say that go for a trial version. I have already selected that and this is a
prot trial which is giving me validity for 60 days. So this is the service which I can use. Now what it means is I can be using my PowerBI desktop which would be also installed. So once you log into this page, you can basically click on apps. You can basically search for something like PowerBI and that will show up an app and you can install and download on your machine. Now once that is there you can basically bring it up. So for example in my case I can just say PowerBI Desktop and that's the app
which I have installed on my machine and basically that comes up. So that's your PowerBI desktop which is coming up and it will still ask you to sign in so that you can share your information through PowerBI. So you see here on the top I'm already signed in and here it also shows you some tutorials and videos which basically helps you in getting to know something more or what's new. So you can always browse that. So you would have Your PowerBI desktop which would be set up. You would also have your PowerBI service which would
be running and then basically whatever you have developed on your PowerBI desktop you can share that through the PowerBI service. Now usually when you talk about licensing I can give you brief insights here. So you basically have your PowerBI service as I said licensing. So you have the pro version which is basically uh your $9.9 Per user per month and basically it has some kind of limitation. So it has say max 10 GBTE. You can work on u some features like incremental refresh is not allowed. You can always look onto the Microsoft website for more
details and in those kind of cases you usually go for the premium account if you are a extensive user and premium account basically is conditional based. So it depends on your requirements and then basically you pay for the service what You use. So that's mainly about your servicing. Now when you talk about your server and service as I said it is basically making your reports online and sharing and making them available to different BUS. So that is the highlight. So when you talk about sharing reports that's one of the things you have anomaly detection that's
also possible here you can talk about automation of reports you have security that is you can go for role Based or role level based kind of security implementation and all those are some of the features of your PowerBI server and service. So it is good to know and basically have your desktop and PowerBI service set up. Now once you have that then you basically have your PowerBI. Now when you talk about your PowerBI it basically helps you with various things. So this is how easily you can have it set up and then basically you can
explore this. So for Example as I was saying PowerBI can connect to different data sources. Now I do have an option here which says get data. I can click on this and that shows me all the different data sources. I can even click on more. If I am interested in looking what more PowerBI desktop tool helps me to do. So it shows me all the different ways in which you can get the data. You see here the server so the database services your folders your different formats. You can click on file Formats or databases. You
can look at power platform. So if basically you are connecting to a platform and getting some services, you can connect to the cloud that is Azure. You have online services and then you have other options. So these are all the ways in which you can get your data. When you talk about visualizations, you see a lot of visualization options here which can be used once your data is loaded. And I will explore and explain more about This. So you have something called as insert wherein you can go in for different visuals or different types. You
can also get into say transform the data. Now that basically is going to pop up and bring a power query editor. Now that's where a lot of your ETL works happen. So when you when you do a transform data it opens up power query editor which we can use to transform the data or change the data or modify the data as per our requirement before Loading all of it into our powerbi. So you also have option here which says modeling wherein we can create new tables or we can work on our data where we can
manage relationship. So as of now we don't have any data. So it does not show this one as activated but that can be activated. This is where you can view your reports. So this is in short exploring your PowerBI. We'll see what are the different options which we can use here. So it basically supports Different kind of data. So when we look at the visualization pane that basically allows us to create different kind of visualizations here and we will understand that. So you can basically visualize on your different data and create different kind of charts,
graphs, maps and basically derive insights from your data. Now when you talk about data models that's where you can basically establish relationship. So when you talk about data models it is basically used To connect multiple data sources to build a relationship. Now we might have different data sets or we might have data coming in from different tables where we may want to basically get insights or get data from multiple data sources for our purpose. Now in that case data models do help us. So for example if you have two tables let's look at the standard
tables. So you have products lookup table and you also have the sales Table. Now usually what you have in any kind of scenario is if we basically say you have your data tables. So that's basically where your data resides and then you have series of your lookup tables. So usually you might have say your data tables here and this is what I'm talking about which might have some data for example let's say sales is one of them you might have some other data table which might be for example let's say budget table or might be
something Else and these are your data tables. Now at the other end you might have your lookup tables. So basically you have various lookup tables and these lookup tables for example let's say this one is customer this one is territory let's say this is product and let's say this one is a calendar. So these are basically my lookup tables. So we can basically as I said your data might be coming in from different Sources. Let's say database or let's say some kind of files or let's say some kind of systems what you have. So your
data might be coming in from different places. Now you might want to transform the data. So this is where I could say there is your query editor which I was explaining. So you have your query editor which basically allows you to edit the data table or basically allows you to edit the data before it is loaded. Right? You can hide a column, You can add a new column, you can modify your column. So your query editors would be basically used to work on the data which goes into your data tables. Now you might have lot
of lookup tables as I said. So let's say these are my lookup tables and these are my data tables. So what we need is sometimes we need information based on our lookup tables and data tables. Now that's where data modeling comes into picture. So basically if I would want to extract Information from here. So I can notice that there is a product key here and there is a product key here. Now this is where we are already talking about say foreign keys or we are talking about your relationships right now when you talk about relational
databases you have something called as foreign key constraints which is in in PowerBI terms I would say it's not exactly a constraint but it is more of a filter propagation instead which is used to Basically connect your different data sources and you could have basically the cross filter directions you can go for single or one to one or one to many or many to many kind of relationships which basically allows you to work on the data. So we will learn more about data models when we are doing a quick demo there where we can talk
a little bit about normalization and denormalization the way the data exists right and then you basically would want to gather Insights from your data. So when you talk about your two data sources as I said so you have a products lookup table you have a sales table. Now if you would want to calculate the total order quantity of each product name which is we are talking about the order quantity as a information here and you have product name here. Now how do you get that information? What we see here is we would want something like
this or we would want more information. So how do We do that? So what we can do is the order quantity for each product is basically showing us the same value. Now this is because the product and sales tables are not connected and there is no relationship between them. Even if you would want to take two sources and just get information out of them, PowerBI would complain that there is no relationship established between them. So what we do is we create a data model. So what we do is we build a relationship Between both the
tables using a common key column which exists in both cases. As I said there is a product key here. There is a product key here. So that basically allows us to have a relationship between these two. Now that could be one to one. This could also be related to other tables. So it could be one to many. You could have many to one. So all those relationships are possible. So product key is basically used to create a relationship. You see the arrow Mark and then there is also this star which can basically mean one to
many. Now when the product key is used to join these two tables or form a relationship then we can look at the product names and the order quantities which basically gives me a total. Now that's the basic use of your data models. Now what about this DAX? So basically data analysis expressions. Now that's a a library of functions and operators that can be combined to build formulas and Expressions in PowerBI desktop. Usually when we work on our data sources when we would want to connect them, it automatically shows us these data analysis expressions. However, this
gives us extension. It basically gives us more power to work on our data. Now sometimes instead of working on DAX or DAX expressions, it would be good to go for better data modeling and have the relationships established better. And sometimes when the relationships are Established, you could use your tax which basically gives you more power on working on your data. So your values are calculated based on information from each row of a table. It appends values to each row in a table and stores them in the model. It increases the file size. So that's what
happens. Now you can right click on any column to add a new column. And for example in this one, it shows that there was a quantity type calculated column which was based on the Calculation. what we see here on the top in the in the expression bar which says what kind of calculation was performed and that basically gives us the value for quantity type and that is basically added here to our existing data. So that's the power of DAX and PowerBI. So you also have something called as uh measures. So DAX allows you to create
new calculated columns and measures. So basically here if you see we are working on AW sales and then we have selected Quantity sold and what we are looking in the report is we can basically right click on any table name to add a new measure here. So we have added what we call as quantity sold as the measure. So values are calculated based on information from any filters in the report. We will see this how this can be done and that basically here the measure does not increase the file size. It does not create new
data in the table themselves in comparison to what we were Seeing earlier that is your calculated columns. So these are your type of DAX functions. So DAX allows you to create new calculated columns and measures. So you have date and time which basically allows you to work on date and time data or fields. You have logical functions which allow us to create new filters or add more filters to our data. You have text functions which basically allows us to say transform the data into a lower case or an upper case or basically get The length
of a string or concatenate two fields or basically uh do a filtering based on some criterias or replacing some content. You also have statistical functions which can be used. You have information functions which can be used. So these are different types of DAX functions which we can use in PowerBI. So we will learn more on PowerBI through a quick demo where we can use some data sets and those data sets could be found on internet although I can also upload that on a GitHub link and you will have access to those data sets. So let's
learn about PowerBI through a quick demo by uploading some data sets and playing with those data sets. Let's look at a sample data set and let's upload it and as I explained while uploading let's also transform the data so that we can have selective fields or selected data loaded here instead of loading the complete data set. And then let's see how we can Visualize this or how we can use the information in this data set. Now what we can do here is we can click on get data and then we can choose one of the
data formats which we would be looking for. So for example I can go for Excel and basically click on this. Now here are some of my data sets. So let's look into the folder here and these data sets are also available on my GitHub link which I'll share with you later. So here we have something called a supertore. Let's click on this and here I already have a data set which is global supertore or I also have selective data. So let's select this one. Click on open. Now that's basically connecting to my data source and
that will show me what does that Excel sheet have. So it has different tabs which is orders, people and returns. So let's select orders. And that basically gives me a preview of the data which I have. And if you scroll all the way to right, it shows me city, State and then country. And if you see here we do see information of all the countries. Now this can be huge amount of data which may which we would want look into. But say for example my use case is that I'm interested in looking for the data
for United States and uh as a country and all its states. So we will do that when we do a transform. Now we can also select the returns tab and that shows me these three fields. However, the first row should have been the Heading of this uh particular data set and we will transform that. Now I can go ahead and click on load but that will load all the data. So instead of that let's go for transforming. So let's click on transform data. Now once you do that it brings you your toolkit. That's brings you
your power query editor which allows you to transform your data. So for example we have uh data from returns tab or returns data source as you see here. So we see column 1, column 2 and Column 3 and that also shows the type of the data here. It also gives me a quick small option here. Let's select this and then I can say use first row as header. Now there are various other options which you can do. You can add a custom column. You can add column with examples. You can keep the top rows. You
can remove the top rows. You can keep errors, keep duplicates. So there are different ways and you can also do a merge query or append query. So as of Now let's just say use first row as headers. And that basically shows that now my first row has become the header. You also see in the applied steps, it basically tells me if I have changed the type of the data, if I have made any other changes, those steps will get added here. So it basically shows me the name as it returns. It shows me applied steps
where I have changed the type and now I basically have this information. Now this is something where you can Change the type or you can basically set it to a particular format. However, we are not doing anything of that sort right now. So we can do that for any of these columns. So this looks good. When it comes to orders, let's click on this. And as I said, I would be interested in selecting for country as United States only. And let me just work on that data. However, we can work on all the data. So,
let me scroll all the way to right and here I have the country. Now, there Are these filters which we can use. So, basically I can click on this and that shows me all the countries are selected. Now, there is also something called as text filters which we will see how we can use to select particular data. I basically have other ways of filtering the data. So for example now I will just uncheck this select all and what I would be interested is in United States. So let's type it here that shows me has an
option here. Select this and then Basically say okay. So that should basically now filter out and it shows me the data is United States only and then you have different states and rest of the data remains. So if you look at the applied steps, it tells me that there are filtered rows. Now we have done the basic transformation for this data set on these two data sources that is orders and returns. So here you have an option which says close and apply. So close the query editor window and apply any Pending changes. You can click
on this and it says apply. So for example I can just say apply for now and that should basically apply the changes which I have performed using my query editor that is I have transformed the data so that I can have selective data uploaded in my PowerBI. Now it's doing that it shows me it is working on both of these data sources that is orders and returns. So that's done and now basically if there are any other pending Changes we can just do a close and apply. So that basically has closed and now you would
see the data appearing here. So I have uploaded the data as per my preferences. Now if you click on the data tab here. So it basically shows you your data fields. It might take some time to populate but if you see in the country field now if even if I click on the filter it just shows me United States. Now that's what we wanted. So we have already uploaded this data in Orders. You can always expand the option here which shows me all the fields which are there in this might be this is an aggregation
might be that's an ordered date. So this is again some kind of aggregation. So we can change the data types. So we are looking at all the fields in my orders table or basically coming from the orders data source. I also have returns which shows me three fields and that shows me the data which is returned order ID and region. So the Column names are applied correctly as we want and that basically looks fine. Now we can also look at your model. So when you click on model it shows me these two. However there is
no as of now relationship established between them. So it says under properties select one or more model objects to set their properties. So right now these are not related. There is no relationship established between them. So if I would want some data which Relates to orders and returns then that would fail because it would say there is no relationship. Now I can go to the first option which says report and we have not created any report here although we can create a simple report. We can look at the data. So we have our orders field.
Now we can basically pull out some information from here. We can choose what kind of report we may want to create. So for example let's go for uh the table option from visualizations. You have various options here which we can use. This is where you can do a formatting. This is where you can select the fields. This is where you can search and filter out the data. You can also add data fields here. So first let's click on table and that basically gives me a table. Now this table as of now does not have anything.
So you can use the filter and slices option here which will affect the visualization or basically what you can Do is now since we have orders here. So this is my orders and I would like to work on this. So let's say for example country is I can select country as a field and if you say it shows me country is all as of now and it says the value is United States. What I can also do is I'm interested in the states. So I have state now I can basically drag and drop it here
and then the state gets added here. So I can basically say select all and that should basically take care of My state field being added here. So state is all, country is all. Now we can basically look at something else. So maybe let's choose sales and I can just drag and drop sales here. So that basically says if you would want to have any kind of advanced filtering which says filter type so go for advanced filtering is less than or equal or you can also go for advanced filters. So that's fine as of now. So
we have added some fields here and that's basically My data here. So let's go for filters here which basically should select all my fields. Now if you see the visualization shows my country, sales and state which we had either by selecting the fields and dropping them here or you can in this section where it says fields. So you can for example let me show it again. So I can just delete this. I can click on the table option. I can just drag it here. I Can basically make it bigger. And I need to add data
to this one. So it says add data fields here. So now let's say country is what we are interested in. We are also interested in states. So let's drop that here. And let's say sales. So this is also what I'm interested. So I'm looking at one specific country. I'm looking at sales per state. And when we look at sales, it basically tells me that this is a summation. So you will basically get the Total sales which have happened. Now we are looking at this data here. We can always go to formatting. We can click on
grid. We can basically increase the font here. We can change the grid color. So for example, let's make it for example blue. So I can just select this and it should basically allow me to have the grid color as blue. Now I can go in for the grid thickness. I can go for row padding, outline color. So we can basically make it a little bit more readable. And then we can basically increase the font size here to look at the information. And you have other options here. So what would you want to do with column
headers? So I can basically have the font color. Background color is fine. Do you want to have an outline? Do you want to have a change in font? What is the text size? Maybe we can make the heading a little bigger. And then basically you Also have the field formatting. So all those could be done. You could go for background and all these things can be done in formatting. So now we already have our data here. And this basically looks good. And this is basically one of my visuals which I have here. And this has
given me some information for sales. And basically I can scroll this. I can also make it bigger. So I could select a particular field if I'm more interested in looking at the information for a Particular state. Now I can add more fields to this. So this is my one of the reports which I have created. Now what I can do with this report is I can basically have more data fields. I can add filters to this. I can basically look into all the data here by just clicking somewhere in the grid but somewhere outside. If
you select a particular row then that data shows up. Here you have an option of focus mode which you can go for. You can look at The other options which says export data. Now if this is the data which you are interested in, you can export it. You can always do show as a table. If you're interested in you can do a sort by country, sales or state. So for example, let's do a sorting by state and that basically gives me the data which has been sorted by state information. Now we could obviously have the
information here. So I can then change the order. So it shows me alphabetically This is the information which I have. So I've created a simple visualization using the data which I have and what I can do is I can click on save. So that basically asks me to save this as a PowerBI file which has an extension of PBIX and I can basically call it my report. So let's say first report and here I can say country or I can say statewise sales in USA. Let's say USA. And that basically is my first report. Now
once you have saved this report you can always look on your machine. For example, if I go in here and if I go into this folder might be I should look on desktop and this is where I should have saved it. So it shows me first report statewise that will open up in PowerBI and we have created a simple report which we have basically used by Taking our data. Now I can also do is I can publish this if I would want to share this information. So publish this report online in PowerBI service. You can
basically select your report what we have here and I clicked on publish. So it says what's the destination. So you can have different workspaces. I will choose the default that's my workspace. I can click on select. Now it says publishing first report statewise sales in USA to PowerBI. You can create a portrait view of your report and you can do all that stuff. So let it publish and then we can basically look into our PowerBI server that's our service where the information is already shared or published I would say which can then be shared with
different resources. So we can come here and basically I can look into the PowerBI and this is where I will be able to look into my workspaces and let's look in my workspace. So it says this is The place where I had initially downloaded PowerBI data set. It says your data set is ready. Let PowerBI help you explore your data. Right? So you can always do this. You can click on view data set which basically allows you to bring out your workspace and first report state wise sales. Now that's the report which we have published.
So let's first check in our desktop if that's done. So it says success open first report in PowerBI. Now I can click on This one straight away and that takes me to my service. Now once it takes me to the service, it shows me the report which we created which we published and it basically has the option where I can save it as a a different copy or give a different name. I can embed this in diff in a website or a portal. I can publish to web, embed this report for public access by anyone
on the internet. We can do that. We can export it to PowerPoint. So we Can do all these options. You also have an option of view where you can change the view. You can basically also edit report here. So you can do that if you are interested in something specific. You can do a sharing to teams. So if you have your teams or groups set up, you can share it with them. You have an option of common PES, you can basically view usage metrics report. Now that can be some sometimes helpful. You can basically go
ahead and go and subscribe A particular report. So if there are new changes made, you will be the one who will be informed. You can click on share. Now if I click on share here from my service it says only users with PowerBI pro will have access to this report. Recipients will have the same access as you unless role level security on the data set further restricts them. So I can grant access and this is where I will have to give the email ids of the people with a message that I would want To have
them look at this report. Right? You can also allow recipients to build new content using the underlying data sets and you can send an email notification to the users. As of now, I don't have any other groups. So, I'll not be sharing it. But I have created a simple report. Now, let's also look at edit report. Let's just to see what it helps us. And when you click on edit report, it basically brings up this one which says your file view. It gives you The filters. It basically allows you to add data fields to this.
So it is basically giving access to these data sets which were in my desktop. It is basically allowing you to give or create different visualizations. Now here we have the data which we are looking at. And if say for example somebody is interested in filtering the data so you could do that. So you could click on filter here and that basically applies this is the filter we have now Country is fine sales might be I can click on sales and I would say okay let's look at sales which is more than a particular amount so
we can say is greater than and might be I can give a number here so I can say 30,000 and basically I can say I apply filter. So right now I'm applying filter and I would look at the values which also shows me the total value is changed. So you have not only created a report you Have published it and now from the service you can edit it. So I'm looking at particular data here and then basically I can click on file and I can save it or I can say save a copy of the
report. And let's say I will call it the same name. So I'll say first report statewise sales and I will say modified. So let's do a save and the report has been saved. So now you're looking at the data here. So that is basically giving you information. So when I click on my workspace here, I can click on reports and that does show me my previous report. It shows me the modified report. It gives me an option of looking at the usage metrics report. So say for example you want to click on this one and
it will basically give you the usage metrics. So let's click on this one and that basically Shows me the report usage metrics which is generated. So views per day, unique viewers per day. You would want to look at the different platforms, who was using it, views by user, and this can be sometimes useful if we would want to look into this one. Now I can go back to my workspace. I can click on reports and that basically took me to usage metrics. I could be sharing it. I can analyze in Excel. I can look for
quick insights based on this data what we have. You can Basically look at the related information. You can also look at the settings of this one. And basically this is how you have your data report here. Now that also shows me the data sets option. So which basically gives me the data sets which can be used to create further reports. And we have our data here. So for example if I click on create report it basically gives me these data sets And we can continue working on this. So this is how I have a simple
report created without basically working on two different data sources but I have selected some data here and then I can basically add details to this. So for example, now if we look at orders and say for example this is the data I have and say you would want to add some fields. So let's go to returns and say for example I would be interested in looking at the the products. So might be What I should do is I should replace the report here instead of country. It would be interesting to look at the product which
we have or basically customer ID. So we can look at customer ID. We can look at the order ID which would be interesting to see if there is a particular order what was the sales which was generated and if there were any returns which were happening on that. So for example here when I have these let me cut out country as a field And I will basically take order ID and place it here. So now if you see my data has been easily modified. So I have my order ID, I have sales and I have
statewise information. So you have basically all the information but this is now order ID, state and so on. Now what if I would want to also see based on the order ID if I say I would want the returned field. So for example I would want to take this one and let's drag and drop it here. Now That says cannot display the visual. Now why is that? So if you click on see details that says cannot determine relationships between the fields. So it cannot display the data because PowerBI cannot determine the relationship between two or
more fields. And how do we fix that? So for example, if I click on fix this now it says there is a missing relationship between these fields. Use autodetect to search for relationships or create them manually. Now I can click on auto detect which will try to search for fields which exist in both the data sets or basically I can create relationships. So let's click on create relationships and that basically takes me to this page which says there are no relationships defined from table to table and so on. So I can click on new. Now
here it says select the tables and the columns that are related. Now I can say orders. Now those are my fields Where you have order ids and it automatically shows that returns also has an order ID field although all the values might not be same but this is how you can create a relationship and it says the cardality says many to one so you can have basically many to one relationship you are saying cross filter direction is single so make this relationship active and it has already helped us basically identifying the field. So I can
say okay. So it says now These two tables should be related or should be connected based on order ID. So here we have this and let's basically say close. Now once that is done if you see I have order ID I have returned I have sales column and I have state. And if you see in return I do have a value of yes which shows this particular order ID had generated some sales and it was for state Alabama and it was Returned and the value is yes. So if you scroll down you basically see all
the values. Now we can add different filters where we can say I would want only yes and no. Now this is again an interesting report. So let's go in also into the formatting and what we can do is we can look at say the grid option if we would want to basically say vertical grid and let's say on and it says vertical grid color. So Let's select this maybe I can try doing a black here. it puts it in a right nice table format and that basically looks good. So you have sales returned and so
on and this is basically the order which I'm seeing here. So for example, if I would want to change the order and if I'm saying okay I would like to look at sales and returned and so on. So we can be doing that. We can come here and say for example I have sales and returned. Let's try moving the sales column over Here might be state is uh an information which we would want in the beginning. So let me also move the states all the way here. So it gives me state, it gives me the
order ID, sales and if there was a return which was happening on that particular product. So easily I've modified my report. Now what I can do is I can just save it and I can do the same thing. So I can publish it. I can basically continue using it or I can work on a new report. So let's continue learning. And uh now here we will also see how you can load some data and perform some transformations and basically get multiple results or multiple tables or multiple data sets which can be then further used for
reporting. So PowerBI does give you a lot of options. Now here you have an option as we saw earlier that is get data or what you can do is you have an option where you can create new data also which says enter data. Now This is something which can be easily used if you have relatively less number of fields. So you can basically add more columns here and you can basically add values. So for example, if I would just call it something like um scientist ID okay and then I can say scientist name and then
basically I can say uh domain and then I can say for example let's say year of joining and and I can keep adding the number of columns Here I can Delete the columns right and I can give this and I can say country. So that's it. And now we have created these five countries uh sorry five columns which we have given some names and we can start entering some values. So I can basically say let this scientist ID be 22 34. I can give some name. So let's give Peter. let's say domain and I can
say biotechnology I can give year of joining 2011 and I can say Germany as the country and I Really don't want this particular column so I can go ahead and delete this now I can come here and then I can give something else so I can say this is scientist ID I can give John let's say this scientist is mainly working in physics and then I can say 2018 and let's say France and go back here and let's say 4567 and let's say Marie and let's say uh she Does her research in uh molecules and
let's call it 2001 and let's say Italy. Okay. And you can continue adding datas in this way. Now you can say if the data is already here. So I can say for example no I do not want this particular row. I do not want this particular row. So you can basically keep adding values here. Now you can say edit. So here we have to give some name. So let's say scientist. Okay. And then if you choose edit it basically brings up your PowerBI editor which allows you to work on these fields if you would want
to make some changes. Now we can make some changes here. We can see what are the number of rows. We can basically perform any kind of transactions here. So this one basically tells me what is the data type. So here we see scientist ID and this clearly tells me this is an integer. However, we will not want to do any kind of Computations here. So we can as well change this. So I can just do a right click and I can work on this particular column. What I can also do is I can just click
on this and this tells me that you would want to change it to date time or you want to change it to some number. So I can just say string because we are not going to perform any computation here. So on the ID column so let's say text and I'm changing it. So it says replace current add a new step. So selected Column has an existing type conversion. Would you like to replace the existing conversion or preserve the existing? So I will say replace current. And now the data type has been changed. It is of
a string. And if you see this step has got added here. So it says that the change type is the kind of transformation we did here. So that's fine. Now what we can also do is we have the scientist name but we don't like the column name here. So it would be good to change This. So there is something called as remove or remove columns and so on. So when you do a right click it gives you a lot of options in applying filters or doing some transformation. If I just click on this one. So
what I can do is I can look at the date time and let's go here. So we have an option which says do you want to change the type? Now we could have done that here or like I said you could choose the time and do it. You can just say transform and how do you Want to change it. So do you want to change it to uppercase? Well you can do that and changes all the values of this one. What I can do is I can again do a right click and I can choose
basically if you would want to clean up the data or if you would want to convert to lower case you want to capitalize each word. So let's choose that. And if you see here the steps are getting added. Now to undo any particular step if I just cancel This then I'm back to this. If I cancel this I'm back to my original form. So you can anytime undo your changes and you can basically work on this. So we are working on this particular column. Now there are various other options that you can look at. So
for example these are this is some of my data but it does not have much information. It would be good to load some bigger data set and then use these transformations or basically Working on changing the data types as I mentioned or if you would want to do a filtering and remove certain fields and only select particular fields if that's what you're interested doing a right click where I want to create a copy of this and then basically I can use that particular copy Now what I can also do is I can add column from
examples. I can duplicate column and then I can make some changes to that. So this is my duplicate column. And say for example you would want um this one to be changed. So we can say remove duplicates. We can basically if you're doing some kind of change you want to change the format here you can do that. you can use for other things like filling up and all that. Now I can just call it rename and let's say um alias scientist name right and I can continue adding columns or I can do some transformations and
once you have done with these transactions now this one if You see it shows as integer but is it an integer? No, this is an date format. So I can go for modeling and change the formats. What I can also do is I can select this and it says it's not a decimal. It's not a fixed decimal. It is date time. It is date. It is date time and time zone. Right? And you can select any one of these. So for example, let's make it date which makes it more meaningful. But then what happens is
when you do this, it is going for the Default dates or the older dates. So I don't like that. So what we will have to do is we will have to basically transform this. And here we have transform, you have change type. So you have date and time, you have date, you have time. So let's choose date and time. And if you see here, it gives me some default timing based on these values which we do not like. So again filter it out. But what we can do is we can just make sure that this
is changed Or you want to make it a string because we are not going to do any computation here. So I can keep it as date but then if I have more fields like month and days and so on then you can do that. So here it also has the option. So when you have selected this you have an option called transform and transpose uh sorry transform also has various options which allow you to work on these. Do you want to do some scientific calculations? Do you want to work on the date field? So As
of now it is just integer. So we can use one of these. We can do a group by. But obviously we don't have much data here. So as of now let us retain this. I can just change this to text and that's okay because we are going to look at this later. We can add more data and work on it. So as of now once we have done all these changes you can go back to say home and here you have close and apply and I can basically say close and apply. So the changes will
be applied And then my new table which we just created by entering some random data performing some basic transformations will be available. So if you look into this one so that's where my data is. It shows me the columns but there is no aggregated column as such here. You don't see any summation mark. You just see the field names or the column names. You see the values. Now obviously if you go into modeling there is no or there is no Existence of a different table which you can join these tables or you can perform some
transactions. Come back to the data set. So this looks good and we have it here. Now obviously we have not created any visualization based on this which we can and we can continue working on it. So over here this is my data set which is a small table where we have created some data and we can use it anytime. Now let's work on a bigger data set and see what kind of transformations we can do. We can then also see on modeling or basically using some smarter ways of working with the data. So what we
would want to do is we would want to load some data here. So let's go into sorry let's go into home let's go to get data let's use our old store data and I'm going to take the global supertore which is huge data set with all the countries and the products and the sales which have happened and we can take this data but before loading it as I Suggested earlier we should basically transform the data we should basically transform the data so that you don't end uploading everything and you don't work on all the data.
I mean unless you really want to. So here I will this Excel sheet which I'm talking about has two different tabs. We have used it before. So let's use orders. Let's use returns and that basically shows me the data what we have. So I can do a load but that's not what we would want to do. So you can do a load and you can get all the data but let's go to transform make some basic transformations before we load the data. So our powerbi editor allows us to work on this. Now here we have
it says this preview may be up to 9 days old. So I can do a refresh. I can select this and I say first thing is use first row as header because that's what I want. So it is basically setting the first row as header which looks good. And we have some order ids. We have the returned if the product was returned. So let's look at the filter here. So it just has yes values which we are looking at. So it says list may be incomplete. Let's say load more. And let's see what are the
filters here. So either there might be a product which is returned or it might be blank field. So that is chosen. So that's fine. We have order ID. We have region which is basically showing me different regions Here. Let's look at orders. And orders is again having your row ID, order ID, order date, ship date, ship mode. So you have quite large amount of data here for your different countries. Now in earlier example I chose United States and then I was only focusing on the states and city in United States. So we can do that.
Now I can basically work on refresh. So whatever preview was stored in the memory we are just refreshing it. Now here we have this row ID and if you see The row ID is an integer. Obviously we will not be performing any kind of computation here. So here you don't have any row ID but here we have row id. So let's make it uh from integer let's make it string and I'll say replace current. So that's that looks fine. It's a row ID which we will use to search but we are not going to perform
any computations unless you want to find on an average on row ID or anything else. Now you have order ids. So what we can do is we can Filter some values. We can rename the field as we desired. Right? So for example, let's go in here and what we can do is let's go into uh customer ID ship. Okay. And here you have say state, you have region. Okay. Now at any point of time if I would want to filter out the values the easier option is that you can select on this one and here
you can do some text filtering okay or you can basically select the values from here so for Example if I say let's get rid of select all what we will be interested in uh say United States and UK that's the data we want And then I can basically say okay. So it is basically going to filter out the data which is for United States and UK as of now. Now what we can also do is how about doing some more filtering before we basically work on this. So let's go in here and we are looking
at United States and UK related data and let's look at it is sometimes good that You can basically uh work on the data here. So for example I go into orders. Now what I would want to do is I would want to look at the fields. Okay. And we want to we have not yet loaded the data. So if you look in the background I just have my scientists because we have not applied these changes we have not loaded we are still in the transformation stage right now what we can do is let's go for
so we can do segment and all these kind of Fields can be used for grouping the data. So we will see that whenever you have a data set you would already know there are certain fields which have repeated values which can be used to group the data and we can do that we can change any kind of values which are not going to be computed on. So for example I have postal code I can use this but again we are not going to find a postal code which is greater than something right. So integer is
not the valid type. Let's change it to text. Okay. And um if for example I would want to look at this. So I have done some changes here. Okay. And this shows me null. So for example, let me just revert this back and let's make it a whole number. Okay. So there are certain columns or rows which do not have some values and we can basically get rid of those values. So as of now we can do a filtering here. So let's keep the postal Code as integer or let's change it to text. Do a
replace content. And now what we will do is we'll scroll all the way right. might be I'm interested in technology category. So that's what I'm interested in. So what I can do is here I can do some filtering. So I can basically choose technology. Now the easier way would be since these are categories and there are only three categories, we really don't want to go and apply text filters here. But if you had something like an office supply and um office inventory then you could have done some text filter. So let's not do some text
filtering here. What we can do is I would be interested in technology. So that's the field I'm interested in. So now you are only having data which is related to technology. Okay. And you have some product names. So this is where we want to do some kind of filtering. So we can rename the Field. We can select some field. So let's for example let's go for uh any of the field which I think might have more entries here. So for example let's go for Canon wireless or Canon image. Right? So let's go for product names
and I will say let's go here. Now I could have done a transformation but that's not what we want. We want to do some filtering. So let's go to text Filter. And here I can say begins with. I can say ends with. I can say contains. So let's go for contains. And it says enter a value here. So you would want to keep the rows where the product name contains something and it gives you some suggestion right so where you are seeing in some values here so for example if I would have selected this then
it applies the complete thing but that's not what I want so I will get rid of all this and I Will say it contains canon now I can go for advanced filtering also okay wherein you can select advanc adanced and then you can give different columns and what do they contain what kind of values you're looking for so you can do that but we will not go for advanced in one step let's go for basic and let's say okay and now I should get all the products which are canon and you have different products so
this is one kind of filtering I've done it tells me what Is the category of this it is machines it is copy years. It is obviously belonging to the technology category. We are looking at uh the market which is for this particular data. When you look at the country, we are still focusing on United States and United Kingdom that kind of data right. So we have filtered the data. We have done some uh selection based on the data here. And what I can do is I can then basically rename a particular field. So I can
do that. I Can say I'm interested in um sales which is the data here. We can basically look into the quantity. So sales is something which we are interested in. But maybe we are interested in sales which are more than a particular value. I'm not interested in lower amount of sales. I would want to look into United States and UK data but I'm interested in sales or I'm looking at if the discount percentage was something or if the profit was more right. So we can apply Different kind of values here but what we can do
is with these changes because you don't want to transform and make changes all here. You want to make the changes once the data set is uploaded. So we have selected some data. We have create done some transformations. We can basically break a particular column into multiple columns if that's what we want. If we see that we will do an aggregation based on year or we will do a aggregation based on the country or year And order ID. Right? So we can break this data into multiple columns. So we can do that. But for now let's
do a close and apply and let's apply this. So that's going to apply all these changes. It's going to load my data. But remember now we are having selective or selected data which gets loaded. So that should get be available here. So it takes time sometimes. So you have to wait and then you can go ahead and check here. So for example now I'm looking in tables. Let's Basically minimize this. Let's go for orders and this is the data I have which obviously row id if you see so you have all the row ids and
again again you can do filtering here but this is where you have already loaded the data the data is available and then you can start working on the columns here. So we have this if you closely see we see this summation mark and this basically means that these are my aggregated columns or these values are measures which can be used For calculations. So we can see that we can create our own aggregations. So we can do that. We can rename the field as we have seen. We can filter out the data. So we have all
the fields showing up from orders. And let's also look at the returns which basically has the columns the order ID and your region. So it basically shows the region here where was the product returned from and we have this information. So what we are Doing is we when we were doing a text filter in the previous example remember it was case sensitive. Okay. And you have to take care of when you're doing a text filter, you have to give a field which exactly matches as it exists in the content. Now, okay, this is the data
we have and let's look at the order column. Right? Now, what we can do is we can do some quick transformations here and we can basically look for more data here. So, let's say we have uh some Filtering to be done. Now I can do a filtering based on my uh product. So we had all these products but now let's do a filtering on product and then products you have copiers you have machines. So you have mainly two categories right and when you look at machines it basically talks about your PC uh something. So let's
look for machines. And here we have basically the copier fields which are more. So I can Basically go for filtering here and that's the data we have. So we don't want to really unselect any of these here. But what I'm doing is I'm saying text filter and let's go into this one now and let's say contains. Okay. So I'm saying contains and then I can basically say uh let's say copier now that's what I'm interested in and uh show rows where product name contains copier. Okay. Now you can give a and Condition here. So if
you would want a specific copier if you are interested in okay let's also say uh contains and let's go for laser also laser and let's say okay. So you see the data gets filtered out here and uh we have the sales which we are seeing here. So let's go to sales. And we will be interested in anything above 500. So let's go to sales. Let's go for number filters. Let's say greater than. And I will say for example 500. Oh sorry 500. Okay. And uh let's say okay. So that basically filters out the data. And
then I am also interested in quantity where I would want the quantity to be more than one or more than two. So for example, so let's go in here and you can basically choose what is the filter you want. So you can apply any number of filters. Now you have the filtering here. You can always click on The filter and if you want you can just do a clear filter and the filtering will be gone. So you have all the data right and this is the particular data we have and here I have say for
example product name. Now I can keep the filter because I'm interested only in these values or I can filter out. So this tells me that when we did a and it is basically going for laser and copier right so let's say for example clear all Filters the thing is gone what we can do is let's go here go for text filters say contains and here I will say it can be a copier okay or so last time we did a and I'll do a contains laser So let's choose this. That gives me more entries. So
either the product name has copier or it has laser. It has the quantity. It has the product name and we have all this detail here. Now what we can also do is we can do some transformation on the product ID. So for Example, you have product ID or order ID. So order ID shows up as uh looks like the country name uh the year and then the order ID. So we can basically split it up. So I can select this and here I have option of let's go to home. So when you have you have
selected this column so you have an option of transform data here. So use the power query editor to connect, prepare and transform the data. So if you really want to transform the data Here, if I basically select this, if I just do a right click here. Now here it just tells me do you want a new column? Do you want to create a copy of this column? Okay, you want to create copy table. So this is your transformed data what you have. And for example, let's create a copy table. and let's come in here. So
you should be able to see your copy table now. So let's go in and select this one. And what you need To do here is creating a copy of this or copy table would not be the right option. What we can do is we can work on transforming this rather than doing it from here. So you have this option of adding a new column, going for a new measure, renaming it. Right? So that's okay. But what we can do is select this particular column. Let's do a home. And first thing is let's go to transform.
So I want to transform the data. Now let's select this one. And it brings up your Power editor again. Here we were interested in orders. So we are looking at our data here. Now if you see my countries United States and UK. So you can confirm that. If you look at category it is technology product name has copier and laser as we selected. So those things are retained. So you have not lost any changes as of now. So this order id column what we have now as I said you can be doing a filtering okay
you can select this Particular column and then you have other things which can be used to transform here like you want to change the data type you want to use first row as header you want to replace some values okay you want to run some merge queries you want to do some analytics so all these options are here now what we can do is while this column is selected I can do I right click. Now there is an option called transform which is basically going to help me in changing The data here. Okay. Now I
can basically duplicate the column. So I really don't want to work on this column itself. But it would be good to have a duplicate column on which we can work on. So I can do a split column here if I would want to. But let's create a duplicate column. So let's say duplicate column. Now that gives me a duplicate column. So we can rename it later. So now I will not work on my original column but I'll work on a duplicate column. And what I'm Going to do is I'm going to basically transform or split
this. So again do a right click. Now you have a split by. So we can say split by by delimiter or by number or by characters or by position. So by lower case and uppercase. So you can do all of this. So let's go for by delimiter. Now if you see it basically identifies the delimiter which is hyphen or dash. Now you can go for split at leftmost identifier right sorry leftmost del Limiter rightmost each occurrence of delimiter and that's what we want to do. You can look into advanced options where it says split into
columns. So do you want to split that into columns? Do you want to split that into rows because that's more or less like doing a group by and you can say number of columns to split into. So we have here 1 2 3 4 values. So that looks good to me. And uh let's do say for example if I choose three. So I can choose three and then split using special characters. So you could do that. So let's for example let's say okay and let's look at the data how it looks like we can anytime
delete the data we can keep it the way we want right so what we did was we created a copy of the column then we did a split and what we have seen is we just have three columns now if you see the fourth bit is gone fourth bit Doesn't show up right because I just did a as the resulting column. So I have the order ID. Okay. And we can check if there is already an order ID column. So we have order ID but that has the complete order ID year and the relatively product
ID. So you can see the customer ID. You can look at this one. So it basically has your order ID. And then let's look at the fields here. So we have the product ID which says TE C M A. I'm looking at the first one 3700 and that has in no relation to the order ID. Right? So we can make sure that there is nothing which is conflicting with our entries. Now once that is done so we have order ID which can be used to categorize the data. You have order ID which is basically the
year. Okay. And you have order ID which is basically having some more value. Now I can keep this data as I want. So I can basically Click on this. I can go for renaming and I can say let's say let's call it order id and let's say ids. So let us in case there is a particular column and you would want to look at. So just give the name correctly. Now order ids is fine. So here we will also rename this one. So let's call it uh order year and that's going to be order year.
So we can again change this to string. Okay. And here you have the order ID. So let's rename this one. And I have my let's call order number right. So we have we are seeing all the steps which we have added here. We just split the data based on the delimiter and we have now three new columns which have got added to our existing data which was already filtered and we have done some splitting up of data by Creating a duplicate and then renaming it. Right? So if you go and look at your transformations. Now
if I would have done a split here straight away then my original column would be gone. But probably we want the original column because sometimes you may want to search order ID which I consolidated information. Sometimes you may want to segregate it based on year. Right now we have the year field or order date field. Here you have the ship Date. Right? But then maybe you want to just aggregate based on year. You don't want to really spend time in aggregating or extracting the month and day and so on. So my these three columns can
be useful. Now what I can also do is I can merge the columns if I want. So we have split the columns but what I can do is I can say select select select. So using your control and now do a right click. So you should have an option called merge columns right now this is Something which is uh we would want to merge. So let's say merge columns and let's say do you want to keep a separator? So yes, I want to keep a separator but might be this time I will give my separator
is a uh colon and then what is the merge column name you want to create. So let's call it something like um order. Okay. Uh year and then let's call it Numbum. Right. So sometimes renaming the fields to a name which makes more sense or based on your naming convention is good. So let's do a merge. And now what I have done is I have done merging of those columns. So I split the data. I merge the columns. And now if you see my the columns which I had created those columns are gone because you
did a merge. You did a merge. And now you have the fields which are either uh earlier you had Something which is separated by a hyphen and here you have something which is separated by colon. Right now anytime if you want you can unmerge this by removing this step. Right. And you can get rid of this merge columns. So if for example I would do that. So I have my data back right. I have my data back. So what we could have done is we can select this. Okay. And then what we can do is
like what we did earlier. So you can Basically go for removing the columns. Okay. You can do a merge column. Okay. You can select one by one and create duplicate of those and then you can merge them. Right? So all the possible options are there. So you can the best option would be to create a duplicate of these columns and then basically merge them as per your convenience. So might be you can say order ids and order number is the pairing what you want year is something which you don't want right Because we already have
the date field. So I can basically say remove and this one is gone. Now I will select this and this and let's go for merge columns and I want to give a separator which is might be without the ear and you have space or you can go for custom like earlier we had give a symbol and then what do you want to call it? So let's say order specs. Okay. Right. So this makes more meanings because we already have the date field. So why do I want the year into my order ID? So I can
always be doing a segregation now based on order specs. Right now this is some simple transformations. What we are doing here? we are seeing the data which we have. Okay. Now what I can do is I can basically first apply these changes so that all my changes what I have done Are applied. Right now once these changes are done we can basically go ahead and save this file. So I can just say save. I can go for save as. I have different other options. So if you would want to perform keep performing your transformation then
you can just do this you can add a column you can view the data. So for now our transformations are good enough and what we can do is we can basically go back to home. We can do a close and Apply and we will be back to our data set which has been modified. So we can see if the data what we have has been transformed. So we have order specs here. That's good. We did not do any filtering or uh we we have the filter left here which basically tells me that there are these
different fields. We have not removed them. But what we are doing is we are just applying a filter to choose copers and laser printers. So that's what we Have here and this is good enough. Now what I can do is once this is done I can basically save it. So I would want to call it some kind of report if you would want to create. Okay. So let's call it as uh uh let's say second report and here I will say country and technology specific info. Let's save it. And now basically I have saved my
data. So what you can do is you can go for creating other report or basically having this information published if that's what you want to do if you want to go for visualization because right now what we are seeing is we have lot of data here. We have lot of data here. It shows me there are these uh tables or data sets which we have worked on that shows me here in the models but there is no Relationship with them. If you go into visualization then you don't have any option or you have not created
any visualizations based on this data. But if you go here now based on the data what we transformed if you see we have order specs right now that's what we chose we basically have other fields so you have ship date you have aggregated columns which can be used for visualizing and we can work on this so I can come back here And what I would be interested in is this data set is fine but I want to do some grouping I want to basically have some selective data in this and for that what I can
do is uh let's go for ship mode. Now this is something what we have. So we are in this data field. Now we have this transform. So let's go back to transform again and let's choose our orders. So that's the data we have. And now if you see here this is You know huge amount of data. What we want is we want to group them based on the shipping mode. So here you have a option called group by. So I can select this. I can go in here. I can basically work on okay get rid
of duplicate values but that's not what we want to do. You want to do a group by. So as I said you can do a group by from here or you can choose from the transform option above and you can do a group by. So let's do a group by. Now how do you want to group The data? So I'm saying I want to group the data here based on shipping mode or which is the other column you want to use to do a group by. So let's go for shipping mode. and what is the
new column name, right? So, we want to basically find out that you want to go for ship mode, but that's not enough. I mean, I can do a ship mode and I can do a grouping by, but you want to just count the rows. No, that's not what we want to do. So, let's go to advanced. So, ship mode is fine. So, that's your grouping, right? But then what we also want to do is we want to do a grouping based on say sales. So here for example let's go for sales. Okay. And what should
we call this? So might be we can say uh shipment wise sales whatever you would want to call. So you can basically get the operation. Do you want to really count the rows? No, we want to basically do a Summing or we want to might be find out an average price, right? So, let's do a sum. Okay. And here I would want to do a summing based on say for example sales. So, this is what I'm going to use for getting a count of the sales. Now grouping by might be we will change this instead
of uh instead of sales we are using a ship mode what we can also do is let's go for a segment And that would be valid grouping. So it will take a combination of ship mode segmenting group the data based on that and then get me the sales which is which is basically let's call it shipment wise sales. So it gets a sum. So let's do a okay and that's my more relevant data which I'm looking at. So I'm looking at the ship mode. I'm looking at what is the segment and then I'm looking at
what is the total sales there. I'm looking at Again the ship mode standard and home office. So always remember when you are choosing multiple fields or multiple columns for grouping by you are basically having a combination of two fields. So that has to be unique and your grouping is done based on that. So now you're looking at the sales wise and this is something as an important information what we have. So we have done some transformation and what you can do is You can use other ways like you can use pivot to get individual values
from it. You can run on merge queries which is basically running some merge queries and merge the query with another query in this workbook if you have. So you can go for this. Now I can basically go for apply and close. So just to add to the group by step what we did was if you see here I have removed the group by filter which we just did couple of minutes back and what I have done is instead of Transforming your complete data set you can basically create a copy of it. So for example I
can just do a copy and then I can come here and do a paste and I have done that and I'm calling it orders summarized but this is my complete data. Now what we will do here is we will basically go ahead and do a grouping by again like what we did earlier based on your shipment based on your segment and based on the sales. So that's what we Are looking for. So let's go for the ship mode and we go for group by and here you would want to go for advanced to ship mode
and this one I will go for segment. So that's fine. Now we want the new column name. So let's say shipment wise sales. So I want to do a summing not the counting of rows and I want to do a Summation based on sales like what we did earlier and then you say okay and that basically gives you the data here. Now what we have is we have the resultant data based on the data which is coming in from here. We did the same thing just 1 minute back but we worked on the original data
set. So what I did was I created a copy and now I'm working on this one. So I'll say close and apply. And now we are back to our desktop. So It is applying these changes where we have done some transformations, we have done some grouping by and what we can do is once we have the data here we can anytime look at our data sets or tables. So this is my original one. If you see now I have order summarized. I can just pull out this information. I basically have my returns which we have
not really touched and we have the scientists. So we have all the four data sets here. Now What we can also do is let's look into order summarized and we just have this data here. So this is fine and you can continue working on this. You can basically merge columns from two different data sets and then you can get the merged column and you can rename it. So you can obviously do that. You can basically do a uncheck whenever you're working on these data sets. So what you can do is if you would want to
work on Transforming for example let's go back here and say I want to do some transformation on it. So how did we do it? We just did a transform and you can go back to transform. So you have this data here and the data what you have you might be interested in transforming this into something else and then you don't want to maybe load this data. So you have this option where you are selecting orders and then you have something called as enable load Which can be unchecked. So when you do a uncheck what will
happen is whatever changes you perform only those changes related data set will be loaded and they will still be available but this will not affect your it will not affect your original one. So for example let's say copy and let's go in here. I will do a paste. Let's say orders. Oh I did a paste. I need to rename this. So let's do a renaming. Let's say orders. And here I will say summarized. And I will say us. So I'm renaming it. Now let that get loaded. We can perform some transformations on it. So I
have here where's my country? So let's look for country. Yeah. And let's look for country. So here I will go for only United States. Okay. That's what I'm interested in. And then I can choose well I am interested in just central US. So I can basically go for just the central US and I can get Rid of all of these. So now I should have only central US data and this is fine and this is the data might be we are focusing on right now for doing some visualization might be looking at sales might be
looking at the product names. So you have the product name. Now remember you don't see any filters here right because the filters are coming from the resultant set and your transformation. So if you have any filters you would be seeing in The top row. Now this is the data we have. Let's um let's not restrict it to region. Region would then reduce my data but that's good enough for us. I can just say no I'm clearing off this filter. I'm still looking at United States but I want to look at all the regions. Yeah, whatever
we would be interested in category and that's anyways chosen as technology which we had chosen when we were loading the data. We have copiers and machines right And we can basically keep this. Now this is fine. I can apply the changes. I can apply the changes based on this one. And what I can do is I can just say for now apply. So that's going to apply all the changes which you have done in orders or summary or the new one. Right? And what I can do is now I can choose order and I can
say don't enable this in load. So it says disabling load will remove the table from the report and any visuals that use Its columns will be broken. We are not creating any visuals as of now. The table will be removed. So that's fine. And now I will say close and apply. So what happens is you are loading the data based on the changes. Okay. Now you have your order summarized. You have returns. You have scientist. But you basically do not have orders anymore. So that was not loaded. So I only have this one. I only
have this one. And You have returns, you have scientist, you don't have the orders column. Right? Now that particular data set was not loaded at all because we did not choose that to be loaded. Right? Now while I'm in this orders which is let's see here and actually you can drag in. Yeah. So you have order summary and this has basically kind of data which I'm looking for. So I really don't need the orders table. So you can do it in steps and you can aggregate this. You Can have aggregated data. You can have all
the data which is filtered, transformed, grouped by and then basically just load it and the original data set which you used that's no more being loaded here. Now at any point of time if I really want I can go back to transform data and remember it is still here. It is still here. It's not gone. Right? So you can basically select this and you can say enable load and you will have the data Back which you can continue working on. Right? So these are some quick transformations which really help us in working on the data.
Now obviously if you have data you want to perform some left joints right joints you have inner joints outer joints so you can always do that you can take two different data sets might be I'm interested in taking the orders summarized which talks about standard class consumer shipment wise sales and here if I look at order Summary I have other details but the thing is we need to make sure that these have the values. These have the values. Say for example, the segment column here and the segment column here can really be used to join
these two tables. So if I create a relationship or if I create a join, I can basically merge the data. So this is how you work on data. We will also see some more examples on might be modeling the data using some expressions to work on the data. So we already Looked at creating a report, selecting particular fields and then publishing the report onto your PowerBI service. Now this is the report which we had created which says state, order ID, sales and then I also added this returned field and that was basically by creating a
relationship between order and returns which we can also have a look in model. So this is the relationship which is created. If you just place your cursor here, it tells me That we have a relationship between order ID of returns and order ID of orders. And that basically allows me to join the data bring it in my one report. Now this is basically your data sources you can look at and if you click on your visualization so that shows you your report. Now what we can also do is we can make it interesting. Now we
would not want to scroll through the fields to see wherever or what was the order or what was the order ID which was Returned. Now I can do a sorting. I can filter out. What I can also do is I can use this option which shows slicer here and that basically allows me to work with this data. So we have this report here and basically as I said you can click in here it shows the data. Now what I can do is I would want to filter out information or slice the information using your returned
either being yes or having a field which has no value. How do we do that? So basically I can drag And drop the returned here as a field which comes from returns. Now that basically shows me only the value as yes but I do know that there is there are some fields which are blank. Now how do I add filter to this? So I can click on this one and then I can click on slicer. Now once that does so it basically pulls out all the different values. So you have either yes or you have
the blank field for returned. So what we can do is we can Select yes. You will see only the orders and their ids and sales where the products were returned. So that gives me 108,118 and I can select blank. So that will basically get me all the orders or products which were not returned. Now this is a simple way wherein I can add a filter to or a slicer to my report to basically give viewers a choice of uh selecting different fields and you can add any number of slicers. You can Basically say I would
want particular kind of information. So for example, if I go into orders and we know we have the state. Now I do have the state information here and basically I can if I would be interested I could filter this out in my report itself. I can sort it. I can look it in a different order. What I can also do is how about bringing in state here and basically dragging it here. So that gives me the state option. It is giving me a visual which is Basically giving me a geographic location of all these points.
So yes, that can be good. What I can also do is I can keep this which basically shows me the state map might be it can be useful. You can zoom in, you can zoom out, you can look at specific information here. You can drag and drop here. So that's fine. What I can also do is I can again take the state and bring it here and that basically can be instead of my map I can go for slicer. So that gives Me all the values here and which basically allows users to choose the fields
or the states which you would be interested in looking at. So for example, if somebody is interested in looking at the data for Georgia, just select this one and you see the map automatically shows you where in the map that's the place and it shows me all the sales for Georgia state. Now I can also basically select yes and that shows me which were the orders which were Basically returned. So that gives me a quick overview of statewise what is the geographical location if the orders were returned or maybe I can just click on blank
and it shows me the non- returnturned orders. I can again go here and uncheck the georgie option and that shows me all the states. Now once you have done this this looks like a comprehensive report which can be useful for the viewers for your management team and so on. So we can just do a save and That basically is saving my report. So I have this report. Now what if I publish this report? So I can just do a publish and when I publish it says okay workspace. So let's say select and then it says
replacing this data set may impact two reports. You already have a data set named by this one. View the impact. I would say replace or I will say view impact. So that basically takes me to the PowerBI service because sometimes we may have some reports which We have already uploaded and updating an existing report might basically affect my existing report. So it basically shows me the impact analysis. It shows me one workspace. There are two reports. They are they have not been added to dashboard but these are the ones which will get modified. So let's
for example as of now go ahead here and let's look into my PowerBI. So go back to your desktop and I'll say no I don't want to replace. So click on cancel. So I do not Want to publish it. What might be what I can do is I can try saving it as a different report. So let's say save as. And now I will basically say additional filters. Okay, let's save it. So now you see the name on the top changes to additional filters. And now it's saved to publish this. And now I can go
ahead and publish it. Select your workspace. And basically it says this is the report Being published with our additional filters with a map which gives a geographical uh area showing us the information. And then basically what I can do is once it is done I can look into my PowerBI service like we did earlier. You can do a filtering. We can basically query this data. We can share it with other users who might be interested in looking at this particular data. Now this says it is done. So it says get quick insights And might be
it's a good an option to look into what kind of insights it's it gives you. So click on that. See the beauty of PowerBI where it tries to search for any kind of insights which it can gather from the kind of report which you have built. Now once the insights are ready it will let you know what we can also do is we can come in here click on view data set. So you see now it shows your additional filters and here you have option where it should be Showing your report. So let's say view
and view is fine. So and if it doesn't show up sometimes it might taking taking time for refreshing. So you can always go here and then you can click on your report and that should get populated. So this is the report. These are the filters which I have. Gives me an option of choosing all the states and basically allows me to edit the report and look at all the information here. Meanwhile, we can see here it is still trying to Gather some insights from the data. And now you see there are some insights here. So
it says sales which is coming up and a subset of your data was analyzed and the following insights were found. So you're looking at sales by ship mode. So it says standard class, second class, first class and same day. So there were different shipping modes in our data and that's what it shows me the sales which one had the majority. It shows me the profit. So which city or State had more profit. So New York City has noticeable more profits here average by shipping cost by subcategories. So there are these different subcategories which we can
look at such as copiers and machines have noticeably more shipping cost profit by product name and you can basically look at the row ID and quantity. So this is where you're looking at a regression analysis. You're looking at row ID and quantity. So there is a correlation between row ID and Quantity. So these are two different variables or fields which are related. You're looking row ID by category row ID. So it says California has noticeably more row IDs. Sales your profit count of region and count of returns. So there is a correlation again between two
different variables. Average of shipping cost. Now we would have taken lot of time in building all these visualizations but PowerBI has already helped you in Gathering all these insights and then you can basically select which one of these is what you are interested in. You can focus and look for more information based on all the fields it has given you good amount of insights which will help anyone who is looking at this particular report. So that's your quick insights here and we have this information. Now once you have this information, this is basically where you
have your focus mode. So it says subset of your data was Analyzed, following insights were found. You can basically say download and uh here you have other options which allow you to work with your PowerBI. So let's look at this one. So this is where we have our report and we can continue exploring it more. One more interesting feature which PowerBI has other than having your insights ready to use which is basically in your workspace and you can basically use these insights. What we are looking At various options here and basically you have this option
where it says spin the visual. If you are interested in a particular insight you can always pin it which you can always go back and look into. What you can also do is you can also click on edit report here. Now that's a report which has already been published to your PowerBI service not yet shared but that can be shared or that can be subscribed. Now once you click on edit report it has option of Reading view mobile layout. You have basically an option which says basic options for navigating through the data set. You can
go for how visuals on the canvas interact with each other. You have all these options and one of the good options is ask a question. So you can always click on ask a question and that basically b says some suggestions. Now you can open this and it says okay ask a question about your data. Try one of these to get Started. What is the average sale? Sort orders by order date. Sort orders by product ID. How many ship modes are there? compare quantity and discount. So do not worry that your report has only four fields.
So it has state, order ID, sales and returned with some filters. But what about you looking at how many ship modes are there? So it is already looking at your data. So if you click on this one here and you can see what are the different fields what we have and You have ship date and ship mode. So this is one of the fields which it is showing you to ask a question. Now what you can do is you can say how many ship modes are there. Let's click on that. Let's ask this question. It
says four ship modes. And do you want to add this to report? Yes, you can. You can let it be as it is. So you can basically keep this question here. And here you have an option. The visual is showing number of ship modes. when you place your cursor Here if you see here turn this Q&A result into a standard visual and let's do that and basically it is saying number of ship modes right so now that's the power of your uh BI powerbi which has basically allowed you to ask a question and quickly add
a visual to your report now what we can do is we can basically save this Or you can say save as and give any different name. So maybe I'll just say save because I would want to have this information and the report Is saved. So in this way you can basically not only create smart visuals, you can not only relate different data sources using data models or relationships. You can add maps, you can add filters, you can add quick questions. You can basically just go here and say for example you would want to ask a
different question. Now we see the fields here. How about looking at the shipping cost? Right? So here I can say um What is the uh highest shipping cost? And that shows me some suggestions. Let's select that. And that shows me the value which is great. And I can keep it as it is. I can basically convert this to a visual. I may want to keep the question because might be this question was asked and uh you would want to see the result here. might be you can move this somewhere here and that gives me some
kind of question Which was asked and we can do a save and that's my report which has been saved in my BI service the data set is still there you can basically go ahead and share this how to connect different data sources with powerbi so in this session today we will see how can you connect different type of data types data sets like Excel, PDF, CSV, etc. to import in PowerBI for your data visualization needs. So what's in it for us today? We will be learning how to Connect to data, different data types, data files
like Excel, PDF. Then what are the different data importing modes and then I will also show you practically different sets how to import them in PowerBI and use it for your visualization purpose. Now what are the steps to connect to data? So now we will go directly into PowerBI and try to import one by one few most commonly and popularly used data sets which are most commonly used uh in a day-to-day Activity. Rest of course there are PowerBI supports any number of data sources uh but we will do something practical on the most popular ones.
So let's let's open our PowerBI. Now this is my PowerBI and first I want to show you that how can I import data directly from a web page and import the data. Now it is asking for a URL in order to import data. So what I have done is I have created a Google Excel sheet with simple data with rows and Columns and what I have done is I have shared this uh sheet as publish to web. Okay. So you just need to say publish to the web the link as web page and say done.
It's it's automatically published and say link. So copy the link which you have published on the web. Copy this link and then go back to your Tableau. Paste it link over here and click okay. Now PowerBI will try to establish a connection with this Google doc sheet because it's published on the web. You need to wait for a while while it is reading. Okay, now it has read one of the HTML tables. So I'll select this one. Now you can see it has it is showing me a preview of the table which is there on
my Google sheet, right? It has 11 rows. So it has all showed all the 11 rows. So now I can go and transform this data because I can see my headers are there starting from the second row. So there's an opportunity for me to transform the Data. So I'll go and transform it so that it looks clean. Okay. So first is I need to remove the first row which is the null row. Remove the top rows. Okay. And then I need to use the first row now as a header. So you just click this option
use first row as headers. That's it. So now if you see my row ID, order ID, order date, ship date, all my data is now ready. So I can say close and apply. Click apply changes. Now this is an example of web data import. You can go and preview your data right now. Uh the biggest advantage of this data connection is that it's a live data. So for example, I insert another row. Let me change the order ID. Some some I've changed some basic stuff and I it's autos saved. Control S. Now I'll go to
my tableau and I'll refresh. Now you can see as I refreshed my power query editor, I clicked refresh all and I got my new row which is there in the live data. I got that fetched from my Okay, I got that row the row row ID number 12. So I I have to say close and apply. Now you can see the new row, the row number 12 is now available in my new data set in the data set because it's a live connection. It's a live connection with the webbased Google sheet. Okay. So This is
one important way in which you can import data. Now let's try to import data from a text file. Now I have already prepared a text file called subcategories.txt. Now let me just open it in a notepad. Now it's a very plain simple file tab separated file in which you have product subcategory ID, subcategory name and product category key. So basically to which product category this particular subproduct belongs to. Right? So what I'm going to do is I'm going to go back to my get data option and I'm going to select text/ csv option and I'm
going to select option mod product subcategories.txt. Okay. So now PowerBI has identified that it's a tab delimited file. It has recognized the headers etc. Right? And I can now directly load this file. Okay. So now once the data is imported in PowerBI, it is like irrelevant to me. It's a composite data import I'm doing, Right? So in my presentation when I'm talking about importing data, there are different importing modes, right? Import data import can happen through different ways. Okay, one is direct query mode in which I create a live uh connection to the database which
I'll also show you uh using MySQL and MS SQL server and also you can do a composite mode in which you can have data imported from Excel plus you can have direct query modes so you Can have multiple uh modes to connect and create a composite data model and that's what we are doing right now in a practical. So what we are doing over here is one we have imported data from the web. Second we have imported data from a text table. Now after doing text now our next task is to import from CSV. Let's
try another one. So now I have imported product subcategory. Now I'll import a CSV file. So again I'll choose the Option text/ CSV. And now in this CSV file, let me open this CSV file and show you what in is it. So this is a list of all my products, product key, product subcategory key, product uh stocking unit, etc. A simple CSV file and I'm going to import that. Okay. So now it has identified the delimiter is comma rather than a tab and it has already recognized the headers correctly. So I'll load it. Okay. So
now my products are there. Product subcategories are there for product categories. Now what I have done is I have created a Excel mode now. So now Excel I'm using to import my product category. So now I have to click on the option of import data from Excel and I'll say product categories. Select the sheet. load and now so my products product categories product subcategories do with different uh uh data storage types but Still now the data is imported into PowerBI it is a composite data model now another very important data type which you can import
is the PDF also right so what I have done is I have created a PDF called customers my customer's data is lying in a PDF Okay. So what I've done is I've created a PDF which has data for some columns are there like you know customer key, prefix, first name, last name, birth date, marital status, gender, email Address, annual income, total children etc etc. So this is the data set which I have created in PDF. So what I'm going to do is I'm going to select PDF now and import customers PDF. And see it has
recognized my table on page one which I'm going to load. Okay. You can rename this as PDF table. Okay. So this basically these are the different type of Data types we have imported PDF, Excel, text, CSV and web page. Now let's take a look at another interesting data set which we want to import is the MySQL server data set. So what I have done is I've already installed MySQL server on my local instance and there's already a schema of SQL live tutorial over there and I have certain tables already prepared over there like department Employee
etc. So my goal is now to import this data or create a live connection with this data set. Now in order to import my SQL database connection in PowerBI you need to first download a connector MySQL PowerBI connector. So you need to go to this link and then click on download and install the MySQL connector based on the operating system you have. You click on Download and install it. After you have done this, go back to PowerBI and then give the IP address of the database. In my case, it's there in this local machine and
the schema which I want to import is SQL live tutorial. So, I'll give the name. Click connect. Okay, now it's connected. So now it is asking me which particular tables you want to create a connection with. I'm choosing department and employee and I'm Just loading them. Okay. So now this is the exact data which is there in the employee and department in MySQL. Okay. So this is one example of how to create connectivity between PowerBI and MySQL. Now I want to do the same thing using SQL Server, Microsoft SQL Server. So I have also installed
Microsoft SQL Server on my machine and I have used the SQL Express. So this is the name of my server. So which I'll copy the server Name and go to get data. Select SQL server and for now database is optional. I can say direct query. Click okay. Okay. Now it is showing me what all tables I can import. So in my SQL server tutorial in my SQL server I have I have these three tables customers employee attrition Olympic events. So I can use probably the customers one which is Now you can see this is the
data the Customer's data which is lying in my SQL server. Okay. So I can preview it and load it. So now you can you can preview the data in uh PowerBI that this this is the data. So I can rename is customers from MSSQL and this is from my SQL and okay. So now this is not the only uh data Sets you can import. Now if you take a look at the options which PowerBI gave of what different type and variations of data it can it has compatibility to import from. Okay. So we can just
take a look at the categorization on the left hand side first. There are file based like excel, text, XML, JSON is also possible. You can evenly directly import entire folder and within the folder whatever uh data types of files are there it'll detect It. PDF, park key or even shareepoint folder which is itself a Microsoft uh technology. Then different kind of databases SQL server and MySQL we just saw but it's not only limited to this. You can connect to Microsoft Access, SSAS, Oracle database, IBM DB2, Postgress, uh, Caiase, Terodata and then SAP uh, uh, databases,
Amazon, Red Shift, Impala, Vertica, Snowflake and any number of databases which are there in the market today uh, Amazon etc. Then it also allows you to connect with its own power platforms. PowerBI platforms, data mods, PowerBI data flows, data vers etc. Azure, there are different kind of storage uh mechanisms in Azure and Azure itself is a Microsoft technology. So it has a compatibility with lot of Azure uh based data stoages like Azure SQL database, blob storage, uh Azure data bricks, right? Azour HD inside Spark. So if you have those kind of services running on your
Azour cloud Services, you can even import them over here. Now online services like you know you have ERPs running uh or some data which is shared on the internet if you want to import it uh that is also possible through certain products uh Dynamics 365 Microsoft Exchange online Salesforce Google Analytics Adobe Analytics GitHub uh LinkedIn sales if you want to do some analysis of some social networking uh you know feeds s that also you can Import. Then other miscellaneous are also there. Web based hive, R script, Python script if there's something to import, get data
from uh Google sheets like we saw one example in our video right now. So there are multiple options available. Now once you have imported the data which is relevant to you u in our subsequent sessions we will see how to create relationships but just giving you a glimpse that whatever data you are Importing powerbi autodetect certain relationships and it'll create for you but then you can go and manually also change. So this is the composite data model which is getting created in the back end while you are importing the data. You can easily go and
manage these relationships either keep them as is, you can delete and create new ones manually. So there is no limitation in that. So this is what we have witnessed. We Have imported data from different files types, data types and then you know we have tried to once it is imported into uh PowerBI then there is no limitation of how you use it. You can create visualizations across different data sets and then create your standard reports. So this is the example of importing data from web importing data from a database from a PDF and then once
you have data you can shape and combine data you can Basically do what whatever transformation you want to do you want to uh make joins merge the data. So for example, if we go back to our PowerBI and if I go back to my transform data section. Now as I have now different data sets available with me, I have I can do any kind of u you know operation transformation on the data, right? Uh so like I showed you I uh upgraded the header row because one of the imported data was not showing the header
Correctly. uh or this columns like this exact one column is extra. I can remove the column, right? All those transformations, whatever I do in the back end gets captured in the applied steps section, right? This is the customer data. You can create uh you can merge it, you can append it uh you know with other data set, right? Let's for example, I want to create a merge data set of my categories And subcategories. So I can say mer select these two data sets and say merge queries as new and I can select product categories and
product subcategories. Select product category key on both the sides and then take do a left auto jog jog jog jog jog jog jog jog jog jog join. So whatever product categories are there, I'll get the subcategories associated with it and I'll create a new table Which will have now I have the table which has the category and the subcategory and subcategory in one table itself. So I can rename it now to as category subcategory table. It's a it's a merge basically it's a join between category and subcategory and now I have a common table right
and I can close and apply. So imagine I have created a new table Which is imported created from one data set is which is excel based and another data set which is text based. See this category subcategory table. So now I can use it the way I want in my visualization reports. So that's what the presentation says, right? That once you have uh the imported data, you can shape, you can combine, you can adjust, you can do whatever transformation you want to do and create your visualization. Data modeling in PowerBI. Now today we will discuss
how to create relationships and different kind of data models within PowerBI based on the structure of data you're importing. Okay. So what topics we are going to cover today? We are going to talk about different types of data modeling and the most important part and aspect of data modeling is the cardality. the cardality which you basically decide after reviewing the nature of data and after you've imported it what kind of Cardality you have to basically highlight right and there are different type of cardinalities which you might have heard earlier also if you are from PL/SQL
background like one is to one one is to many etc we will we will talk about that now what are the different types of data modeling Now, dimensional data modeling is one of the most popular and most uh you know widely used uh modeling. In dimensional Data modeling, you have master data uh like for example customer data, date, store data, product data. So these are like you know uh less frequently changing data sets. So there is an organization right and you have set of customers their email id phone numbers etc that will change less frequently
as compared to the sales transactions because transactions are happening every day every minute. So sales is a more fast changing data set in dimensional Modeling which is in the terminology of data uh is also called as a fact and customer store product which are like more of static data and not less changeable data is sometimes called a dimension. So this is a typical dimensional data model which is typically used uh sometimes right and then there is another model which is relational model. This is a typical model which we have been using in database design like
you know primary Key foreign key relationships. So for example you have a customer who has purchased a product. So probably he might have the customer might have the details of the product which he has purchased and you will make a join between customer and product table and even uh you can make a join between product or product type or customer or product type. Customer table will also have a key to the product type. So this is less uh conducive for reporting but It is more of a transactional uh relational model but of course this is
also feasible. But from the PowerBI perspective when we talk about reporting and visualization this is the most extensively used dimensional data model and this is what we going to see in our example now. So what I'm going to do is I'm going to show you certain data sets. First we will prepare and create certain of our uh data sets and then we will import in our sample PowerBI file and Then slowly slowly we will create the relationships. Now one important thing which you need to understand that in PowerBI if you go to PowerBI there is
an option that that PowerBI auto detect new relationships after data is loaded and import relations from the data source on first load. So for example if you are importing the data from a database where you have already defined the primary keys and the foreign key relationships. So uh that is the first option which PowerBI will auto detect. And secondly, if suppose you are importing two different kind of data sets, one is Excel, one is CSV. And if PowerBI detects a common column, key columns, it will auto detect a relationship which you can go and later
change, modify, manage in your relationship uh menu, manage relationship menu in PowerBI, which I'm going to show you. Okay. So if I open up PowerBI and this Is where the option lies. Go to file go to options and setting options data load and these are the two options which are by default check. You can uncheck it and auto and manually prepare relationships. There's no limitation to that. But if you keep it checked then PowerBI will do its job to detect the relationships. Okay. Now coming to the next important factor cardality. Now before I start playing
Around with my data and start showing you certain relationships. It's very important to understand these four types of cardalities. One is many to one. Right? So basically many to one means that many orders contain data of one customer. So per order one customer is there. So from customer to order or product or delivery address it's a one to many relationship. And from the other side from order to customer perspective it's a many to one Relationship. Okay. Second other uh cardality is one is to one. One is to1 relationship is only applicable when you are saying
is it's an extension of the current table. So for example in one table you have employee details and you are extending the details of the employee in another table like employee address, employee ID. So that is like one is to one. There's no multiple records of a single employee. In the address table only one Employee ID exists. Right? Now one is to many as I said is the reverse side of many is to one. It's in customer table only one customer record exists per customer and one customer can place many orders for multiple products and
can also have multiple delivery addresses. So that way this is a typical one is to many relationship. We will be seeing this example also in our sample data set. And last is the many to many Relationship. Now many to many is a very typical example. So which I'm going to show you pract practically and in our case we will see that like for example you have placed an order for a particular product uh you know but there are multiple fulfillments which has happened. So suppose you made order for 10 products but at the back end
when the company is fulfilling it is first fulfilling the first two products then the rest three. So basically you the Fulfillment is happening in batches. So one order ID might have a multiple fulfillments for the same order ID. So there will be a multi many to many relationship which I'll show you practically. So now with this background let's start importing our data. Now the first important thing which we need to import is the master data. So first I'll import all my master dimensions which are which I'm going to you know use in my example. So
first is the customer's Table, customers data. So this is the customer details like customer key, prefix, first name, last name, birth date, marital status and gender. Some redundant columns are also present but we'll remove it. So my customer data is loaded. Now today's session is all about this section of modeling. So we will keep our focus over here. Okay. Now some columns probably some blank columns are there. I can select Them and say delete from model. Yes. Okay. So now this is my customer's data with the relevant columns and the key per customer key. Now
there's no relationship in this model right now, right? Because only single table is there and the associate data is only imported. Now let me also import my another important master table is the products. Select the product data, product key, product subcategory, product SKU, Product name, model name, product description, color, size. So just see all the relevant information only specific to the product is available. So I'll import it. Okay. Now see there's no relationship between product and customers directly because until unless a customer makes an order places an order for a particular product there is no
join right so now between these two tables the most important now another table which will Now make sense is the sales order table sales Now I I'm assuming that PowerBI have auto detected the relationship. Now you can see that because I've already uh ticked that check box. Now let's see what PowerBI what relations PowerBI has autodetected. Let's first sec check the relation between customer and sales. I'll double click this uh join. Now what it has done is it has created a join of many to one between Sales and customer. So what does that mean is
that one customer has can place many orders right and that is that it has detected by the quality of the data and the data sampling which PowerBI has done. You can also reverse this relationship. Here I can select customers and I can select sales. Now it has become one to many. So that you can also do manually. So that is what I said. Whatever PowerBI is detected it is up to the discretion of PowerBI internal Configuration and algorithm but you can go and change it. So this is now you can make this is by default
active. So we want to keep it active. one customer many sales orders cross filter direction means that only from customers to sales is the filter applicable not reverse I'll come to this with my another example but first let's change the relationship so one is to many means from one customer and many sales orders similarly let's see what has happened at The product side of the relationship similarly powerbi many sales orders for one product. You can for simplicity sake you can say products sales product key is the join. Now just focus one more thing please uh
also see the the column on which the join is is the grade column grade out column. Product key is also here and product key is also there and it is what we wanted. So one is to many relationship from product to sales table and active. Now looks fine. This is Something which is looking logical and probably now we can proceed further to create a report. Now let me explain the cross filtering with an example. Now for example I want to check in a report that what is the count of products which uh which a particular
customer has ordered. Okay. So what I'll do is I'll select the product count of product name. Now if you see The for each customer in front of each customer name the count is coming as 293 293 it is getting repetitive because because there is a one-way filter direction filter between customers and sales and sales and products right so this join is singlesided it means that from customer to product you can't find a relation relationship because it's a single side cross filter right what does this if I change it to both it means That it is
equal to a join between product and sales and every product detail now is appended to the sales table so if I want to make you visualize this you need to go here I'll first open my sales table we also open it here. Let's make clicky is okay. Now if I click okay, you can see the single arrow is changed to double arrow. It means it's a it's a both side filter. So when you say a both side filter, it means that implicitly Within PowerBI, you can imagine that all the product columns now will get appended
because of both ways filter you have applied. And if you go to your report now see the change of the numbers now 40 20. So the total count of products across all my customers come out to be 293. Now the report looks uh correct. If I change the relationship from back to single between product and sales then you can't make a join between customers and product. Basically you Can't derive the product count from the product table. See this if you have to live with it then you would have to go to the sales table get
the product key and get the value of count of product key but that is not correct. Okay. So if you want a report in which you want the count of product name and even if you want a count of distinct product name. So this will not come correctly. You would have to go and change the Direction of the filter which is from single to both. So this is a typical example there where you want to use a two directional filter. Now let's proceed further and import other data set in order to give show you another
example. Now I want to show you an example of 1 is to 1. So I have another table which is called customer details. So the key in this table is again customer key but only email address, annual income, total Children, education level etc. Other details of the customer is there. So I'm loading the customer details. Now you see it has auto detected a 1 is to1 relationship. But what is the meaning of 1 is to1 means one customer key only has one entry in customer details. There is no multiple entry. So if you click this
button it's a one is to one and the cross filter can be both or single doesn't Matter because one customer will have only one value. You can make this as active. Okay. And if you go to the customer report table, you can now easily associate a email address with the first name. You will get 1 is to1 record. So now you can see that with one is to one relationship with the first name I have associated The email id and for each email id there is a associated first name with that. So this is an
example of one is to one relationship. So in this example what we have explained is that for each customer there is an associated customer detail right uh so you have the first name email address education level homeowner occupation and total children count so in this report what we have done is uh if you click over here so the first name and the email address okay so there's a One is to1 relationship and 10 and if you drag the customer key uh report takes time to render and even if you can so this is the reporting
output you have the customer key first name associated email address and the count of product names uh which the customer has ordered. order. Now this is an example of one is to one. Now I want to show you an Example of many to many. Now for that I'll import my fulfillment data set. Okay. Now in my fulfillment data set there is a column for order number. So basically what I'll do is I'll drag order number from here to here. Okay. So now what has um uh PowerBI detected? I'll do one thing. I'll select sales over
here, fulfillment over here and Order number to order number. Okay. So, it's a many to many relationship. So, it means that per order I have created multiple batches to fulfill that particular order. Now, many to many relationship is a definitely a candidate for both ways cross filter detection uh direction. But you can you can check that. But definitely uh PowerBI shows a warning that this relationship has cardinality to many to many and this should only be Used if it is expected that neither column contains unique values. Okay. So we know that fact that's why we
are accepting this relationship as many to many because we know there are multiple order numbers over here in the sales table which are mapped to the multiple order numbers in the fulfillment table. We'll click okay. Now you want to keep uh the uh direction as both ways or one direction that is up to you. the way you want to uh map the Report. So I can double click over here and you can even click. So now you can select from which way single filter you want from fulfillment to sales or sales to fulfillment. I'll prefer
sales to fulfillment and click okay. Okay. Now we have our all our different kind of relationships over here which we have tried to shortlist one to many, many to one, one to one which is uh this example and many to many. Now if I show you further relationships which you can Keep on adding like for example I have uh uh the example of territories. Now in which particular territory the sales was done. So I can map it over here. Okay. So now it's a typical one is to many relationship because territory is my master table
uh where I have a static list of continent, country, region and it is mapped to the uh territories which are for in which my orders have been placed. So it's a typical one is too Many. So that way you know you can keep on adding data. Then you have uh details of returns. Now this is another transactional table which is about the orders which have been returned rather than being you know returned by the customers. So you have a product key and so automatically PowerBI has detected a relationship between the product key and the product
uh table. Right? And even if you can join the territory key in which territory the Return has happened. Right? So mostly the most common relationship which you will observe is the one is to many because as I told earlier the most common relational model is the dimensional model. Uh the static data the slow changing dimensions. The sedds are the master tables and the most frequent changing are the fact tables. So if I talk about a typical dimensional model, the fulfillment table, sales table and the territories table sorry uh The fulfillment table, sales table and my
returns table are the fact tables of my data model. with today's topic which is about how to create PowerBI dashboard in a few minutes. Yes, you heard me right. You can create a complete PowerBI dashboard or also known as a report in just a few clicks and a few minutes. Now in this particular video we are minimizing the time in creating dashboard because sometimes data cleaning process you already know if you Are an expert or if you are a beginner in data analytics you already know there are steps in data report or a dashboard right
first you load the data from a source it can be cloud PDF excel workbook CSV or anything and after that you do some data cleaning right you split the columns or you eliminate some blank rows blank columns and some irrelevant data which does not fit or some um for more reference on data cleaning you can also refer to the video Which is linked in the description box below and so right that's the process so basically you import the data from the source clean it transform it and then load it and then create a dashboard right
so in this particular video we have the completely cleaned data set which is really available for creating report which will be also linked in the description box below if you want to follow along. So um with that uh let's get started. Right. So that's how it Goes. Now let's have a look at our data. So our data has um some row ID, order ID, order date, ship date. So basically you can totally understand this particular data set is about sales report, right? Let's say I'm working with XY Z company which is based on data analytics
and I get an email from my client that he or she wants to know how exactly the sales are performing in what exact states or countries and uh what exact regions and what category of Products or subcategory of products and what's the sales quantity order profit right so let's say you have a one uh category or subcategory or a product which is giving you high number of sales and profit right you want to improve more you can increase the price or let's say you have some product which is not performing so well right so you
can uh offer some u discounts let's say at 50% off so that you can move out the product from your store right so some uh Basically if you analyze the data and you come up to a decision where which product is performing in which region or state or country and which product is performing in not performing in which region, state or country, right? You can come up with the strategy for sales and improve your revenue. So that's the entire process, right? So with that said, so this is the data we have and let's quickly jump
onto PowerBI and get going with creating a report or Dashboard within a few minutes. Right? So here we are on the PowerBI desktop. So here absolutely you can see you can import data from Excel, you can import data from SQL server, you can import data from a blank table or use some sample data. Apart from that you can also get data from a different sources as well. Right? See uh we will be trying to do some automation right. So regarding the automation, we can also uh go through another video from simply Learn which will help
you understand how to automate the process of data transformation, cleaning and uploading. So uh you can just give the file location here the folder. So I'm going going with the folder here. So it will give me a new window. Uh you can go to browse and I think I have my folder in downloads and uh Excel data sets. uh Excel. I think I'll be having that named as sales report. Yes, just click okay. And You have the sales report here. You can have a quick preview of the data. So here you have the options combine,
load, transform data, right? You can just directly load because I've said you that this particular data set is completely clean and is ready for reports. Right? So you can just select the data you want and just load right I'm doing it this way because let's say uh for let's not not let's say uh we Have uh the data here in a form in in such a form that the data is from last 3 to four years right so let me select it this way so in this case as well it will take the excel file
and uh even if you add some more data to it it will uh apply the changes to it. Right? So let's say uh we have the data for the last four years 2020 209 2021 2 and 3 and since 2024 is not completed yet we are not taking that Data. Let's say we enter into 2025. Then the new data for 2024 gets updated and loaded onto the same data table. And you don't have to uh you know process the data once again, clean it, transform it, load it. No, that's not going to happen. You can
just load the data. You can just drop the data on the Excel spreadsheet and your dashboard or report will reflect the new changes automatically just by refreshing the dashboard. Right? That's the automation you can do. So What exactly automation is the process where human effort is not involved and the computer or your any uh tool does the job for you is called as automation which reduces a lot of time of yours and you can use that valuable time to plan on the strategies to improve your revenue. Right? That's the overall moto of data analytics. Now
that's the uh preview of the data. I'm just going to directly load it. I'm not going to transform it because uh it's completely Clean and transformed data and ready for reports. So here you go and uh you can close if there are minute errors you can just close it and uh if you want to check your data you can go through your data here and you can have the sample data right here right if you check the order date not ship date sorry uh ship date can enter into uh 2023 as well right so here
you have 2020 and uh the other days right so you will have okay let's go to the data filter so we'll Basically have four years not 2024 right so uh that's the way if you just add the 2024 data it will automatically reflect in the visualization now uh all that we need is create a dashboard or report which will help us analyze some data right so uh let's get started just double click on the report section and here you'll have icon or vizard which will ask you what exactly you're looking for preparing and Q&A. So,
I want region wise sales and I want it in a bar Graph. There you go. You have the region Y sales in bar graph. Press on uh okay and you have it. And you can place it somewhere in the corner. Then if you want another one, double click. And you want category wise category sales. And let's take that in pie chart. Category sales pie. And here you have it. Press on agree and accept. And you have it over here. And yearonar sales in A line graph. Sales by date or sales by sales by date. Line
graph. Here you have it accept that so that you can identify which is the peak uh performance year or pre-erformance date for us which gave us the highest number of sales in a certain region. Right? So here we have it. You can place it can customize it. Right? And now let's go for subcategory. Double click and uh Subcategory. I think we have a hyphen a space there. Say sales. Uh the type of chart you want. You can go with the donut one. Select that. And one. So you have a subcategory wise sale. And now let's
go with uh region wise. I mean the country wise, right? So let's go to the data again here. So here we have segment city, state, country. Okay, let's go with country now, right? Country, Country sales map chart. So whenever you have a region or state or a country involved, go with map charts. it's the best way to represent right so we have considered European nations over here so for that reason we have the European states here and uh now we can go with let's go with the data once again and have a look what else
we can do and shipment mode right so you have the shipment mode as well so uh you can identify what type of Shipment mode customers are majorly preferring shipment mode bar graph Actually, so basically I think we have uh three types of shipment mode. Let's check the column name of shipment mode properly. Ship mode. It's not shipment mode, it's ship mode, right? So let's type again ship mode bar graph. Okay. the shipment mode sales Bar ref there you go so agree accept it so you have uh standard shipment is the most preferred one now we
have this so let's add some KPIs here so uh KPI of sales you will adjust that agree there you go and let's add a KPI I for profit and you have it. Agree. KPI for revenue. Double click. Let's check if we have a column called Revenue, quantity, discount, profit. Right? So, let's go with quantity and discount. So revenue is not added. So it's okay. So you can identify which uh uh product or subcategory is highly selling in in number of quantities. So you have you'll have uh the sum of profit, sum of sales, sum of
quantity and what else we left the last one discount and rate sales discount profit. So let's identify if we have offered any. Okay. So let's add a KPI or rate as well. If we are completely focusing on a single product, a single category, we must be having uh a KPI for rate as well. Double click somewhere. KPI on rate. There you go. Accept it. And you have the completely formatted dashboard or sales report ready for a meeting. Let's close all these so that we have a better visualization and you Can just go to the uh
slides share mode or fit to the page mode where you can have it. That's good. So uh this is how you create a completely functional fully functional sales report or sales dashboard just within a few clicks and just within a few minutes. Let's say uh I want the sales report only for central region. Click that and it's completely interactive. And let's say we don't want that. Click Outside and you will have everything back to the normal. Right? And let's say I wanted to focus on a particular category technology category alone. You have these sales happening
and the subcategory of sales and what are the products over here right you'll have that and which is the country uh happening to ship the most of them and what's the shipment more preferred by them and highest performing date or year. So here you can see 2022 June was The highest performance for sales and uh the lowest one is somewhere here in March 2020 or something right so that's how you can create a fully interactive dashboard or report based on sales data in a few minutes and in a few clicks and that brings us to
the end of this tutorial on the topic how to create a completely functional sales report or sales dashboard in a few clicks and in a few minutes. Today we will learn how to use PowerBI with AI. For that, let's Consider a small problem statement or in other words, a simple use case. So here it goes. A business chains data analysts use PowerBI to generate sales performance dashboards across multiple stores. Due to manual data cleaning, report structuring and repetitive DAX calculations, the process takes several days delaying the important decision making. Analysts struggle with errors and inconsistencies
due to high data complexity. By integrating PowerBI's AI Features like smart narrative and AI insights, they automate trend identification and anomaly detection. Now analysts quickly generate visualizations by simply typing questions reducing manual effort. AI assisted data modeling ensures cleaner data sets and accurate relationships enhancing efficiency. As a result, report generation time drops by 50%. Dashboards are more insightful as stakeholders receive actionable insights Faster. That's it. If these are the type of videos you'd like to watch, then hit that like and subscribe buttons and the bell icon to get notified. Now as discussed in the use
case, let's imagine a similar performance analysis dashboard. Now what exactly is AI in PowerBI? So AI and business intelligence are two technological spheres that when combined offer a powerful tool set of transforming raw data into actionable insights and making data accessible to Everyone. Similarly, AI features in PowerBI can analyze user behavior to customize reports and dashboards to meet individual data needs using the raw data. Now, let's imagine this. Suppose there is a significant growth in business and you wanted to identify the reason behind it and replicate the same idea across all categories or maybe reward
the team which is responsible for that. Or let's say there is a significant drop in business and you Wanted to understand the reasons behind it and pinpoint on the one which is responsible so that you can resolve and improve the overall business growth and pull out some revenue out of it. Right now, if you are doing it manually using Excel data sets or even if you're doing it with PowerBI data sets, you might end up creating a lot of Power Query STAX expressions and also if you're working on Excel, then you end up creating a
lot of pivot tables and it it might be a Manual task and will consume a lot of time, right? Or in case if you're in help with AI then the matter of days could turn into the matter of minutes and you will have the exact data or information you're looking at and you can resolve it in a matter of minutes or hours maybe after a business decision has been made. Now let's go through a practical demonstration on PowerBI for this. So before that let's have a quick look on the data set that we will be
Using today. So this particular one which is the auto sales data for 2024 is the one we will be using today. So we have all the columns order ID, audit date and uh delivery date, state, dealership, pin code, region and uh the names of the products and fuel type, leads, revenue, profit and cost company everything right. So this is the cleaned data that we have today for our data analysis. Now we will be identifying the key areas that led to the Quarteronquarter growth. Or if you drill down a little bit more then we can also
identify the month-on-month growth or in case if there is a drop in month-on-month or drop in quarteron quarter. We can also identify what is the reason behind it. Where is the loss coming from? Which car product or which dealership or what might be the reason is it about the season or the customer or the dealership. we can drill down a lot of information from it. Right? So This is the overall idea of how to utilize AI in PowerBI. Now let's quickly switch to PowerBI. Now we are on Microsoft PowerBI and we have already loaded the
data which is here. This is the preview of the data that we are going to work on. Now let's go back to the report view and pull out a visualization. Now I'll be trying to proceed with the bar graph. Now let's pull some data. So, I'll go with the order date and drag and drop it to the Xaxis. Now, let's go with the orders and that will be in our y-axis. Here you go. Now, if you drill down a little bit, this year will be turned into quarters of the year. Now, if you kind of
drill down again, now you have month on month. Now, let's try to do some modifications to this data. Set all the data visualization and add some improvements. Go to the view tab here. Go to the customize current theme and go to text. And now here you can increase the font Size maybe 18 to have a better visual. And after that if you go to the format icon here you can also add a data labels if you want. What is the total number of sales that we made? Now we have a perfectly functional chart here. Let's
eliminate this. Now let's begin with the AI for identifying the drop trends or the growth trends. Now here you can see u the month of August in 20124 has gave us the highest sales which is 2,618 Orders and you can see there is a quite a good uh growth here. Now if you wanted to find out what is the reason behind this growth and then just click that bar and right click or double click and go to the option called analyze. So this is similar to the Excel's whatif analysis but with a little change. Now
here you can see that um PowerBI will automatically give you a lot of charts and visualizations like this one here which is a water model and this will Indicate you what was the reason of the growth and here you can see uh we have received 11 orders from Andhra Pradesh. This is a statewise analysis and also you have decrease. So there is a decrease in miseram and a few more regions like karnataka and everything. So this is uh something behind the reason for growth or drop and here you can see the product wise uh analysis
where we have some growth from cars like 8 series wellfire and others after that You have some dealership data as well. So there is a good orders that is received by prime auto, elite auto and a few others. And there's a drop in Asian dealerships etc. And here you have uh the status on delivery. So uh this is also good. And now let's say now you wanted to identify what's the reason for drop. Now what you can do is you can just click on the uh month which showed you the maximum loss or the least
orders received and you can go to that. So the Least orders is practically from March and here you can see there is a sudden drop from August. Let's say you wanted to compare with these two months and then what you can do is you can just same procedure you can just analyze and now explain the decrease. Now you have the reasons behind the decrease here. So we have a couple of cars which are not receiving good amount of sales and after that you can also see statewise drop in orders and after that you can also
see Uh the type of car which has stopped receiving orders. You can see sedan class has drastically stopped receiving orders. That's the reason behind it and after that uh most of the orders are in transit. So you can filter out u the intransit ones because this will be delivered to the customer. So the overall um you know sales will be uh or the overall orders number will be increased once this transit becomes to deliver right and here you have u Delivered as well right so it might be a drop in delivery numbers as well and
uh compared to the previous month the the number of orders delivered will be low or if you had to talk about intransit I think this is about the intransit one as well so previous month had a lot of cars in transit and this month compared to that number it is a bit low. So this that's that can be the reason. And after you drill down a little bit more you have region wise analysis. Now this is Also another way to find out the drop analysis. And let's say you wanted to compare two different months of
a quarter. Right? So here you have quarter 1. The highest performing month of quarter 1 happens to be the February one. No the January one. and you wanted to compare that with the highest performing month of quarter 3 which is August. In that case, you can just hold control and select the first month from first quarter which is the highest Performing one and you can compare month going to the analyze button explain the reason between the increase here. Now you have the entire report. Now let's say you have a brilliant looking chart over here and
you wanted to present that to your stakeholders. Now all you can do is you can just select this particular chart and select the option which says plus which will automatically get added to your dashboard which is somewhere here and you can set it in the way you Want. Now you almost have a semifunctional or mini dashboard for you which explains you the decrease or the gain in orders and also the month-on-month performance. So you have a fully functional uh report in front of you which is explaining the comparison between the highest two performing ones. So
this is how you can make use of AI in PowerBI for data analytics. Today we will learn how to use Power Automate in Windows. Power Automate will help you to Automate the mundane tasks like rolling out reports, emails, presentations and many more. For that, let's consider a small problem statement or in other words, a simple use case. So here it goes. Let's assume that you are a business unit head working with the top brass of your organization. Your routine might include a lot of task like setting up roosters, scheduling scrum meetings, writing down emails, common
data entries, and a lot more. These might not Sound much of a task but definitely consume sizable amount of your working hours. The time that you prefer to be more inclined to utilize for resource planning and setting up growth strategies for business development. If only there was a way to automate all these processes without having to hire another resource. Yes, that can be done with help of one of the most recent Microsoft Office tools called the Power Automate. With Power Automate, the time For all these tasks will be dropped by 50% and you have a
lot of time for productive utilities to build better business strategies. That's it. If these are the type of videos you'd like to watch, then hit that like and subscribe buttons and the bell icon to get notified. Now, the first question, what exactly is Power Automate? So, Power Automate is a cloud-based service that automates repetitive tasks across apps and services. is designated to help Users optimize business processes and focus on more strategic tasks. Now it is not just confined to Microsoft products. It can also be get attached to Google or any other applications as well. If
you want to roll out some emails via Gmail or if you download some files and store it onto Google Drive, Power Automate will always support it. Now let's proceed to take an overview of how to use this Microsoft tool. So some of the mundane tasks like sending out an email, Scheduling some roosters or meetings or some points of the meeting, it'll always be a lengthy task that consumes a lot of your time. Now for a sample task like sending out daily emails, we will try to automate this task. Now we have logged into the Microsoft
365. So here you can have some apps over here and you can check out the app for power automate. Now we can just go to all apps and here you can just scroll down and here you can see the option that says Power automate. There are other ways as well. You can just you know search for the power automate app and you can find it out in apps here or now you can just find it here. You can just log into the power automate app. So, Power Automate already has some templates for it, right? So,
you can just navigate to templates and there are many uses of uh automate. So if you want to do with some email attachments or if you want to trigger a PowerBI datadriven alert or if You want to you know set up something in your Microsoft office or you want to put a notification or if you want to download all the attachments from your email directly to your Google drive and connect your you know X or Twitter you can do that. So there are multiple people who have already used Microsoft Power Automate and they've already delivered
some templates for the ready for use purposes. And if you want to go with some topics, you can also go with This. Or if you're working with remote work kind of style and you wanted some applications or some automation things you are looking for, you can also look out the templates which are remote work related. You can just go through all these templates and you know go ahead with that. Apart from that, if you're looking for some data collection type of automation, you can also do that. And if you're looking for an email automation, you
can also do that. And if you already Have created some flows, you can just go through this and find the flows that you have worked on. So, I've got one flow already ready here. I've set up an email uh you know that you can be sending out to your uh reporties or you can send out this email to your fellow developers or fellow uh colleagues and if you have some approvals you can also do that. So there are a lot of things. So as we discussed before we will go with the automation of sending out
daily Reminders. So I'll just type down get daily reminders and I get this type of automation here where you'll just send out an email reminder to the outlook.com email which will be which can be about an email to check the numbers check the reports and u hand it over to your fellow team members right so uh or it it may be about a scrum meeting or roster meeting or roster setup so I'll go with this one and here you have the recurrence and office 365 outlook so uh This is my email and I'll just continue.
And here is the flow. So here you can just click and you can give out an interval. Maybe if you want to give it every day or if you want to give it a weekly or bi-weekly, you can set it up. So I'm going to go with uh every day. And here you can set up any u prescribed uh or if you have any u type of time zone that you want to select, you can go ahead with that. So I'll go ahead with anything here. I'll go with Alaska Maybe. And you can also provide
some uh time and it's not a mandatory one. And apart from that, here you have some hours you want to select. Maybe I'll go with 10 a.m. in the morning. And also I want another setup at 6 p.m. which might be the logout time. And uh yeah, this is the one. And apart from that, just go back to the flow once again. And here you have to set up few parameters. So let me give out an email here as well. the recipient email. You can add Multiple but I'll go with one. So I'll add one
email recipient address here. That's the one. And apart from that uh subject could be about uh scrum call with report updates or with numbers with number updates can give anything according to the business needs and you have a reminder and uh I think I'll add the same subject here. There you go. And you can also provide the importance. So I'll go with high importance one so that uh the Recipient will understand. So far you have set up the flow. I think it's fine. So you have no more notifications here. This says the flow is completely
fine. Now you want my to save this flow. And once the flow is saved, you can proceed and test it. Just click on this. It's save. And you can you if you want to try and test it, you can also do it. Just test it. And uh it can happen in both the ways automatically or manually. I'll now go with the manual one so that I can Just crossverify if this is happening or not at the current time. Right? We set a timing 10:00 a.m. in the morning and 6:00 p.m. in the evening. Right? So,
it only happens twice. But now, if you want to run it at this particular instance, you can also do that by just testing it. Right? I've tested it and uh I have sent out the uh yeah, it finds good. And now if I select run flow, then it will run the flow. Now, it has already done. No s it's successful. No uh problems here and Done. And you can now just uh you know kind of log into your out mail outlook email and then check the email. Right. Just a second while I log into my
inbox. There you go. Now we have this particular email that we send. Right now you have a reminder scrum call with numbers update and you can just reply or forward it. So this is how you can just automate the emails with your Power Automate and you can just send out email reminders to your fellow developers or Teammates on a regular routine. And that's how you automate a lot of simple things as your daily tasks. And I've already created two here. And if you go to templates, you have a lot of other templates here. Most top
pics or if you're looking for a dedicated one like data collection, email, calendar, mobile, etc. you've also found them there. And if you're still in confusion, maybe you can just type down and find the templates which are in match with Your requirements. Hey everyone, welcome to today's video on PowerBI DAX. So here's a quick question for you. Have you ever felt like PowerBI is like a cool looking dashboard, but you just wish you could do more with your data? You know, add those custom calculations or get insights that are a little bit outside the box.
Well, that's exactly where DAX comes in. Let's think of PowerBI as your data playground. It helps you visualize your data with Charts, graph, and tables. But what if you want to take it up a notch? That's where DAX, which is data analysis expression steps in. It's like the magic behind the scenes that makes PowerBI not just a visual tool, but a powerful data crunching machine. So, what PowerBI DAX is used for? Well, it is used to create custom calculations. If you need to calculate total sales, average profit, or even something more complex, DAX is your
go-to tool. You can also build Dynamic reports. If you want your reports to change based on user input or the time period, DAX will make it happen. You can also dive deeper with your data. With calculated columns and measures, you can explore data in ways that are more meaningful at your specific needs. So in this video, I'll walk you through how DAX works and show you how to create those custom formulas that can truly unlock the power of your PowerBI reports. So let's get started. Hello and welcome to Simply. In today's session, we are exploring
one of the most powerful features in PowerBI, DAX or data analysis expressions. Whether you're a beginner or looking to deepen your PowerBI expertise, this video will help you master DAX and unlock advanced analytics for your reports. DAX stands for data analysis expressions. It's a form of language used in PowerBI, Excel, and other Microsoft tools. DAX lets you create powerful calculations, data Models, enabling you to extract deeper insights from your data. In PowerBI, DAX is essential for building calculated columns, measures, and calculated tables, which are the backbone of custom analytics and reporting. The purpose of DAX
in PowerBI is to analyze data and create custom calculations for better insights and reporting. With DAX, you can go beyond simple aggregations and build metrics that answer complex business questions Such as year-to- date sales, growth rates, or profit margins. However, you must be thinking if you're following up with the previous video where we have learned about interactions and different type of charts, we haven't used any DAX there. However, we still had meaningful insights. Why is learning DAX is so important? that is is essential for advanced data calculations and custom metrics in PowerBI. It also enables
time based Calculations such as yeartoear, quarter overquarter analysis and helps to create custom measures like profit margin or growth rate. Ultimately, DEX empowers you to transform raw data into meaningful business insights that drive business decisions. Let's look at the types of DEX expressions you will encounter. So within PowerBI, first we have predefined functions like aggregate functions, logical Functions, date functions, information functions, user functions. Then we have DAX statements. These control the logical flow of calculations such as if and switch statements and more variable and written very commonly used statements. Then we have data retrieval functions
which we call as queries. These will help you to pull data from your model. It is often used in PowerBI's query editor like evaluate or define. First we will explore Predefined functions as most of them are similar to Excel. Dex functions come in several categories. First we have aggregate functions like you can see sum and average, minimum, maximum, count, standard deviation, variance and more. Other are time functions. You will find them very similar to Excel like year, month, end of month and do week, weekday and more. Another are logical functions like if, and or switch.
We'll see multiple conditional logic. Logical Functions here. Other than these three types of functions, we have information functions, reference functions, parent and child functions that are very specific to DAX. Most utilized tax functions are common date functions like year, month, quarter or looking at difference between two dates. Other are time series functions where we are trying to calculate year on year as variance and written using the year functions, date functions within the Statements. Figuring out year till date, month, quarter till date which is calculating the any numerical like sales profit till year to date, month
to date and quarter to date. These are the time series functions. Other most commonly used aggregate functions. Even when we are not explicitly defining a function implicit calculation as soon as we drag any numerical variable into the canvas immediately it converts into sum of that particular amount. So if it is sales Amount then it will be sum of sales amount automatically that is implicit. Yes, we can go ahead and create the explicit sum of sales, average sales, aggregate functions within PowerBI. Other most utilized functions are user functions. User functions like calculate are where DEX gets
really powerful. Calculate allows you to change the context of your calculation. For example, summing the sales only for a specific region. This gives us Flexibility to answer very specific business questions. And logical functions like if and switch let you build conditional logic into your calculations. If takes a condition and return different results based on whether it's true or false. Switch is like a more advanced if letting you handle multiple conditions easily. Let's create a calculated column together. We label each sale as high or low based on the amount. In previous examples, we Have been using
one or two predefined functions nested within one another or simply one function or a statement to calculate one single column or an aggregate value. That really shines when you combine functions. For example, to calculate profit margin, we have to go ahead and utilize sum of sales minus sum of cost that we divide it by amount. Again, this uses both sum and simple arithmetic function to give you a critical business metric. For time based Analysis, you might use dates YTD to calculate year-to- date sales or compare this year's sales to last year's. We can use same
period last year. We can also use previous year, previous quarter, or previous month. These time intelligence functions are essential for any business that wants to track performance over time. Let's go ahead and see some time series based calculations with DAX in PowerBI. As we have already seen few examples of PowerBI DAX. Before we move on, here are some best practices for writing DAX. First, keep your formulas simple for better performance. Use measures for calculations reused across the reports. Instead of adding a calculated column, it is always better to add a measure. Measures are easily revisible
across multiple tables within the data model itself. Plus, measures are not the part of a data Model. Measures are created when they are utilized inside a report. So, measures are always recalculated. So these are always updated if your data is updated. Third, always check how filters and relationship affect your calculations. If you're applying a relationship, it means within a data model. If you're applying filter or the slicer, it is going to affect the DEX calculation results into the report or the visual we Are creating. In the next section, we'll explore one of the most important
concepts in Dex, that is context. Understanding row context and filter context is key to mastering DAX and building dynamic interactive reports. Let's dive in. Let's explore some most used DAX functions in PowerBI. This here is PowerBI desktop application. In this application here, let's import our data from Excel. We can import it right from here or we can click on Excel workbook Right here. We getting a auto sales data set. Let's click on open. Here we'll have our navigator window. We can highlight sheet one. We can look at the data set. Right now let's check on
that. We have order ID, order date, delivery date, how many days we took to deliver, which state did we delivered it to, dealership name, regional details. Then we have customer details, product details. Then we have your pack details that involves what was the traffic, Quantity sold out, leads converted, orders placed, and then the revenue, profit, and cost to company. Let's transform data here. It will take us to power query editor. Then power query editor here. Let's check out this is our order ID. It is at data type 1 2 3. We have order date with
the date number. Let's check the data types for state. Everything is in text properly. Now if we look at the numbers, Numbers are in the proper date format too. However, if you want, we can change revenue, profit and cost to company into data type pitch decimal number. We'll add a new string. Let's rename this to this is our auto sales data. Currently we are ready. We'll close and apply. Our objective here to explore DAX measures. Here in the auto sales data here We have dimensions and measures. Dimensions and measures. Measures are numerical values. Here we
can see wherever the summation sign is there, these are numerical values. For example, first we have here is cost to company. Cost to company. The summarization here we can see is sum. Similar customer ID says PowerBI understands customer ID is an identification of a customer. It is a unique ID provided to the customer. The summarization here will automatically be count. Similar pattern we can find in other columns too. Let's go ahead and create calculated measures simple functions post. For example, we'll create a simple table post. In this table here we would like to explore that
how much quantity we have sold out in each category. If we check on category here and quantity we see by default sum of quantity here. This sum of quantity is implicit calculation. Implicit calculation here means this calculation is decided by tax. Now when we go ahead and Define the calculation by using modeling that will be called as explicit calculation where we are going to use tax functions and statements. Let's see difference between both. to add the measure here. We can add the measure from new measure. Here we can also go ahead and right click On
table and click on new measure. We can also click on model link and add a new measure from here. So let's go to the table. Right click on a table and let's add a new measure from here. When we click on new measure, observe here a formula bar is opened up where it is directly writing measure equals to as a name. In the data pane on the right and bottom corner, we have a measure Here. This measure is getting saved in data pane on right hand corner here we can see the item is a calculator
right here. So let's rename this here as total quantity. Make sure as a standard practice we do not leave any spaces. Later on if we are trying to reuse this aggregate calculation and if there is a space in the name we will not be able to reuse it in any other calculation. Now We want to sum you'll use the predefined function aggregate function sum a simple sum function here. If you want to read more about it you can expand it from the arrow. It will give you an simple explanation. This is true for any of
these functions. Here we select the sum from the drop down here. As usual, we can see the parenthesis will open up. It will give us the syntax right here. Here the syntax and the arguments right here. And we have the options to choose From below. Now we want to go ahead and calculate quantity. So we will simply go ahead and type Q in the dropown. We have quantity with the help tab in our keyboard. We can simply use quantity right there. And since we are in PowerBI, remember to close the balances. To commit or save
this particular calculation or the measure, we will go ahead and press enter. Once you press enter on the right hand corner, you'll find Total quantity right here. order quantity sold right here. As you scroll down, you can see the measure is created here. Let's check and utilize this measure in the same table. We can close this from here. Select the table. Let's add the total quantity sold. As we can see there is absolutely no difference between explicit and the implicit Calculation here. However, now if we want to move forward and add the average here, we
can have the similar average calculated there. But when we have sum of quantity, the implicit measure, if we are collaborating with someone, they will have the freedom to change the aggregate calculation right here. However, when you have explicit calculation, the calculation is fixed. The collaborator can simply calculate The percentages out of that. Nothing more than that. So, aggregation cannot be changed. So if you want average quantity to be sold out to individual customer, we can go ahead and and create the explicit calculation for that too. As you can see what we have here is cost
to company and the quantity plus the profit we have made out of that and the revenue. So let's calculate let's create a simple table where we Want to analyze how many average days we take to deliver to certain state. So here we have list of a state and now we want to calculate average days we take to deliver. First let's look at the implicit calculation which is by default sum. So we'll go ahead and create a new calculation. Here we are at the home tab. In the home tab on the right hand side we have
a new measure. So let's click on new measure. We're going to call this average days to deliver to avoid the spaces. You can also add underscore sign in average days to deliver. We start with average function. Now we have days to deliver and we close the parenthesis. Once you press enter, we can find average days to deliver is here. Let's check here in this report here. Next, we would like to calculate that if we are taking More than 5 days, meaning we are taking longer duration to deliver. So that is not a expected KPI. The
average days to deliver if that is equals to five or less than five that will be considered a good performance. For that we add a new measure again. So currently as you can see we are in the measure tools. In measure tools also we have new measure here. Measure tools is opened up. We because We have we are currently selecting a measure. Let's click on new measure and apply a logical condition. The logical condition is going to tell us about delivery performance. We'll simply go ahead and type I in the drop-down. We have if in
the if here see the syntax it is pretty similar to Excel. Logical test is Here. In the logical test we are saying that read average days to deliver the measure that we have here. If it is greater than or equals to 5.5 if condition matches then the performance is needs to improve. If result is false it means it is just opposite meaning we are doing good. So the performance is good. So we have one condition and two output. Now we can see our measure is saved here. Let's check on the measure. Now we can see
whether each is state what is the average days and whether we it is good or it needs improvement. Let's keep a slicer here for region. This region size slicer will give us more insights here. So within the central region we need improvement in east here we are doing good in few states we need improvement And we need improvement in most of the cases here let's look at few more common functions like date functions currently if you want to go ahead and create a table we want to see year- wise sales we have done we have
delivery date and order date. Let's use the order date here. Now moment we check on the order date, the entire hierarchy will be added here. We can simply check and check and decide to Keep whatever we need right there. However, every time extracting and checking and checking from here is not always desirable. If we want to go ahead and utilize the year and month within another calculation. So maybe we want to simply go ahead and look for the quarter 1 sales. In those cases, we will not be able to extract quarter one right from here.
So let's go ahead and look into most used date functions. Here we add a new Measure again. and this new measure here. Let's get the year extracted. We can simply use a year function. And the year function here we have to write a date. So date we it will find here in order date. As you can see right now when we are using ear function we do not have the option to select the measures here. The reason is there are few functions that Are that can be used within calculating measures. However, few functions like year,
month, quarter and more these can be used only when we are adding a calculated column. So here you will find some limitations related to context. To understand more functions, we have to look into calculated columns and calculated tables. So first we are going to add a calculate table for calendar. and then we will move forward and add calculated columns In that. We'll also understand why calendar is important to be added and how we can use multiple date functions and logical functions to create a proper calendar table. By learning that we will cover the concept of
row context and filter context too. Now we are going to explore one of the most fundamental concepts in DAX that is context. Understanding context is absolutely essential for writing effective DAX Formulas. There are two main type of context. row context and filter context. Let's break these down. We'll see it with an example. For the example, first we'll add a calculated column. To add a calculated table, we will go to the table view. And here let's add a calendar table. And within The table you'll understand about row context and filter context. To add a new calculated
table currently we are at table view. In table view we'll have table tools open here. In the table tools we can see the types of calculations we can add. Currently we're going to work with new table here. Once we click on new table, observe here on the right hand side we Have a calculated table icon and here also we have a table. What we are looking for here is to create a calendar. A calendar in PowerBI is a column which contains continuous date since the start to the end. This calendar table is helpful and required
if you want to do any kind of time series calculations. The easy way to create the calendar table is use calendar auto. It's an automatic calendar. It will Automatically figures out from the model wherever is the starting and the end date is. Currently we have one excel sheet. So we're good with it. We can simply open and close. We don't require to add anything inside the syntax argument. As for career math, we can see as optional because it is in the square brackets. It can automatically pick up from the data model or imported data that
we have here. As you can see, we have this Calendar table here. The column is added that is date column. This date column, let's select the date column here. Column tools will open up a new tab. This is not date and time. We are only looking for date here. There is no time component with limit and the format has a small date format and we are good with that. Now let's talk about the context. Row context. Row context is essentially the DEX's awareness of the current row when Evaluating a formula. It's automatically created when we
work with calculated columns. For example, currently we have date. We want to go ahead and extract year, month, quarter out of this date column. So we will go ahead and add a new column here. And in this new column, see a new column is added. So we want to go ahead and extract year. To extract the year, the function is year. Enter Where our date is in the calendar table date. So we can find the calendar table and date with PowerBI we have to give the entire address entire location of where the column is. So
we are telling table name and then the column name. Here this formula is evaluated for each row individually. So if we are asking it to extract month, it will go ahead and extract month from individual column individual row here. So we can add one More column here and we can ask to get the month here. So the month can get table here we'll have the month number. Now if you need quarter you can add a new column again and get the quarter. We get the date again from the date extract the quarter. In both the
cases it is going to be numbers. As you can see it is January Quarter is one. So this year is always seen as calendar quarter. If you want to see the month name, we'll again write one more calculated column again in the row context because it will evaluate for individual row where we want to go ahead and look for monthly for the month name. Here we will go ahead and use the custom formatting that is the format option. It is par to text function in Excel. In format function we will give the value and format
it to Extract the month name here we will use the function format. Format function helps us to custom the format of any given numerical value and this value converts any value to a specified number format. So for example we are getting the calendar date here. The format we are looking for is month. So we can ask it to extract month. We will write M four times there and the month will be extracted. Next is local name. So we are not uh looking for that Right now and we close it back. This is not as per
local or time zone. We simply want the month out here. So we don't need it right now. And this is format. This is monthly. Every column that we have added it is based on row context. It is calculated for individual row. Notice how it calculates for individual transaction. This is row context in action. Next is filter context. To understand the filter context here, let's go back to the report view. In the report view, let's calculate total profit. So the filter context on the other hand is about which subset of data is being considered for a
calculation. It's created by filters applied to visuals, slicers applied on your report, relationship between the tables, filter arguments, index functions. Filter context is crucial for measures which don't have an inherent row context like calculated columns do. Let's see filter context in action with a measure. So let's add a measure here. Measures work on filter context. Let's calculate total profit. To calculate the total profit here we use sum and we ask it to get the profit from the data. When you place this measure in a visual For example showing profits by region. When we place this
measure in a visual showing profits by region, profits by region, the filter context for each region determines which rows of the proper columns are summed. If we click on west region, if we click on west region in the slicer, the filter context changes and our measure recalculates to show only average days to deliver in west. Sum of quantity or total quantity only in the region west. Similarly here also we can see it will show only region west. This is the default filter context. This filter context can be modified within the DAX measures by using calculate
functions. The calculate function is one of the most powerful functions in DAX because it allows you to modify the filter context within the formula. This is how the calculate function syntax looks like. As we can see here, calculate function evaluates an expression in a context modified by filters. If you look at the syntax here, it says that first you give the expression and then the filters are applied. So for example, we want to calculate the profit only for certain Category of product. So let's see here certain category of product we have hack, MPV, sedan, SUV.
So let's calculate the profit only for SUV. To do that, we'll add a quick measure here. I'm naming this as SUV profit. And here we use calculate. And calculate we are simply asking them to calculate the total profit. So here I'm using total profit. You can also go ahead and write sum of profit. In both The cases it will go back and reexecute that particular tax. Now here we will go ahead and give a filter. A simple filter will be that go to auto sales category which should be equals to S U V. This is
the SE profit here. See the difference if we add the cards here. This is our total profit from all the regions and categories. This here is SUV profit only profit that we have gained from SUV. This overrides any existing filter context within the specified filter. So if I go ahead and select central for example here you can see SUV profit gets impacted by the outside filter right here. Context transition is what happens when row context gets transformed into filter context. This occurs when you use an aggregation function inside a calculated Column or when you use
a calculate formula here. For example, if you want to calculate what percentage of SUV profit contributes to the total profit in this case, context transition will happen. If you click on new measure, we click on new measure and we'll see it here. So, we want to find profit contribution. We want to find profit contribution percentage of SUV. So here We'll get our SUV profit that we need to divide it by. So we can use divide function here. SUV profit is numerator and denominator here. Now we want total profit. So now we will write calculate here.
Reason we don't want this denominator to change or get filtered out because of slicers or simple selection of central east and not here Because this total profit changes when we select anything from out here. We want it fixed. So SU profit is going to be fixed. It is from SUV. In the calculate here, we want to fix the total profit value. So, we type calculate and we need total profit here. And here we use all function. This all function returns all the rows in a table or all the values in a column ignoring any outside
or even inside filters. Inside all function we'll ask it to keep the entire auto sales data intact in order not to get this particular total profit impacted by any outside or inside filter. Here the all function removes all the filters from the sales table giving us total profit across all the product. Let's see. Let's implement this to see which product contribute most to our overall Profit. I'm going to add a card here. Multiro card and let's see the profit percentage here. Let's convert it to percentage too. So it is in general format right now. Now
it's percentage. See, so the STV contribution here is 25%. If I convert this into a simple table right now, maybe you want to see in front of category, you'll find that it is only giving us the SUV percentage. It Doesn't understand this at all. Meaning this is a fixed percentage answer that we get. However, if we go ahead and add fuel type and we don't see the category here, now we'll have a correct percentage right there. So while calculating this here it is able to hold on to the total profit calculation right there. So this
is your profit contribution as part and see here this is not 100% this is 24% that is only for the industry that Is only for the SUV. The same can be applied to calculate profit contribution for each product. I'll copy this here and let's create a new measure. Now we want profit contribution for all product here or let's say each product here. So now here we do not have SUV profit here. Instead of that we'll ask it to get the profit. And now here we are asking it to fix the profit right there. This total
profit uh always gets filtered out. However, the second total profit which is used within the calculate function and with the all function is just going to be fixed in the output and we calculate it like this. And now if we go ahead and create another table visualization where we want to see each category contribution and profit. Let's convert this into Percentage. You'll find the percentages right here which amounts to 100%. If you go ahead and simply put total profit here and calculate the percentage from here by utilizing this we still get the same answer. However,
when the outside filter is applied see the difference. If I'm filtering right now with the central which is the interaction right Now if we are selecting central here now we know that in the central the all over contribution of these is 7.32% within central out of the entire profit. However, here we see 16% for HPA and likewise reason this particular profit contribution is coming all the ways from the fixed total profit of 601.78 entire total profit. So these look same but this profit Contribution cannot be impacted from the outside or inside filters. Now that we
understand the context, let's explore some advanced DEX functions that leverage these concepts. In this session, we will analyze auto sales data set by using PowerBI desktop. Continuing from the previous topic, we have an understanding of context in PowerBI DAX. Moving forward from here, we will guide you through every step from connecting to the data, exploring The data model, defining key metrics, using advanced index formulas like time intelligence, forecasting, dynamic titles, etc. to building rich reports and dashboards. By the end, you'll have a solid hands-on understanding of how to turn raw auto sales data into actionable
insight with professional visualizations. Throughout the tutorial, feel free to pause and try each step yourself. We will also provide explanations for the main concepts and Formulas. Let's get us started. Open your PowerB. We can go to get data and click on Excel workbook here or here. Browse and find auto sales data. Click open. In the navigator preview, select the table, the sheet one and click on load. PowerBI will import the data and displayed in fields pane on the right. This creates a new table in your data model. At this point, check that order date and
delivery date columns are recognized as date or time types. Under the column tools, this is date and numeric columns like cost to company, profit, quantity, revenue, and numeric. Let's go to power query to adjust the types. To go to power query, go to home tab and click on transform data. Here this redirect us to power query editor. This data set now serves as our fact table of auto sales record. We can check the data types right here from decimal number fixed decimal whole numbers to date time and the binary too. These numerical value can be
converted into fixed decimal number that is currency. We always add a new step. From here we will close we will go ahead and first rename from sheet one. So let's rename this as sales. Let's close and apply. I will close the power query and load the data into PowerBI. The data set now serve as our fact table of auto sales records. In a more complex model, we might split this into fact and dimension tables. For example, separate tables for date, product, and dealers And separate table for numerical variables like order date, orders, profit, quantity, revenue,
traffic. But for now, we have one table containing orders, customer information, sales figures, and so on. After loading, notice that our data model currently has a single table. In a robust BI solution, we often create separate dimension tables. For example, we could extract a unique date date table from order date column or separate Tables for region, dealership, product, and customer. Creating a proper date dimension and marking it as a date table is important for time intelligence calculations. In fact, PowerBI and the DAX engine requires you to mark one table as a data table to get
correct results for time intelligence functions. For example, you could duplicate the order date field into a new table of union dates. Then under table tools menu, use mark as date Table. We'll assume a proper date table is in the place. This date table we are referring to is the calendar that we have created in the last few steps. So let's go ahead and see another way to create a calendar. So we'll go to the table view. In table view we click on the new table. This is our date table or we can also call this
as calendar table. And we ask for automatic calendar. Again We will select this column and convert this into data. Now this is a separate table all together. We'll go back to data model and we make sure that this data table is connected to order date in sales data. So we are making sure that this table table is connected to order date here with the help of common column as a relationship is created. Observe that the cardality is one to many meaning date here and date table are Unique. However order date here is repeated since multiple
customers can place multiple orders on the same date. However, this creates a unique list without unique list of continuous states. Now, let's go ahead and mark this as a date table. We'll go ahead and we mark this as a date table. We click on the three dots or epsilon right here. And from here itself, we can mark as date table. We switch this on and choose the column date is validated here. Let's read everyone the built-in date table that are already associated with this table that is the order date. It will be removed. Meaning now
we cannot use order date within visualization. We have to use this date table only since it is going to be continuous date. Look at the icon will change like an ID rather Than having a calendar like this. Meaning now this is a continuous state as before. We can go ahead and keep on adding year, month, quarter, year. That is going to be our row and text calculation right there. Let's see a new way to create a calendar table. As usual, we'll go ahead and click on new table. This time we going to name it dates.
Remember that PowerBI DAX is a functional language. It has its own way To write. We can move to the next line here with shift and enter. Now when we say it's a functional language meaning we have to give indents, we have to open parenthesis, close parenthesis, add its places. It has a structure and format to type although it is used as a guideline and since we are beginners we might not have a full knowledge of how to write it. In that case there are multiple websites that can help us to reformat the power. For now
what we are attempting to do is we are trying to add a new table. In that new table we want to first need a one first column where we have calendar. Now in that we have added the entire calendar date meaning a continuous date without any gaps. Then we want to add year column, month column and likewise. To do that we will start with add columns. Now add columns here returns an entire table with multiple columns at once. So we don't have to add individual New column every time we want to add something new. So
now we'll go ahead and type the entire code to get the full calendar table. This is add columns. Add columns is asking here what will be your table. The table here will be our calendar auto that is automatic calendar here. I will take this to the next line too. Indent is automatically there. Now within the syntax it is asking us uh the name there. If you read the syntax it is Asking name of the column there. So we will say the column name is year. Now the expression meaning the formula should be year and it
should go ahead and get the date. Now how do we define the date there? So it is going to be dates. Here is the date column right there. close parenthesis common. We need month. So we're getting month number the function is month here. Uh here we Have date. Likewise with shift and enter we will move towards the next line and uh we can keep working with new columns right here. Now we don't have to add columns every time and write them separately. It saves us so much time. Editing is easier. You don't have to work
with individual columns anymore. Adding, deleting, and managing your columns becomes easier. These are the exact same functions we have already applied before. Now as it will require we can go ahead and add more columns in here. Currently I'm adding some basic columns here. Let's see an example. Then we'll keep on adding the columns and see here we are in a similar manner we can also go ahead and add the quarter name for year. So here let's see we have 2024 quarter 3. So we utilizing our format function again we are extracting year then we are
concatenating it with quarter here. So likewise go uh we can move forward and add more columns in here. Now once we have our date that is our calendar table and the sales data here. First uh we will make sure that our date column here is marked as date table. So it is recognized as a date table here. It'll also make sure that in the data Model here in the data model here this date is connected with order date to determine the relationship properly. And now order date will not be used for visualization purposes. Now we
will use the date always from the dates table. Now let's move forward and understand DAX. Now we create key performance indicators as measures. You will go to report view. You will go to modeling tab and click on new measure. In this new measure here, let's define few KPIs here. First we have revenue. So the first measure here will be total sales. We'll sum the revenue. This sums the revenue field across all records. Let's add one new measure. Unit sold. This sums up total number of cars sold. Make sure your home table is sales data. Meaning
the formulas are getting saved within the sales data only. Our data has a profit column. So right click can enter a new measure. To illustrate we can write gross profit here. Let's calculate gross profit. We do have cost of company. So let's figure out the sum Of profit deducted by sum of cost to company. This follows the example of a profit measure as an aggregate difference. We'll add a new measure in sales data. Since we have a difference gross profit, let's look at find out profit margin percentage. Here we define the profit margin percentage by
dividing the entire profit by revenue. This will compute the profit as a Percentage of revenue avoiding divide by zero errors with the help of divide function. In divide function numerator is sum of revenue. Denominator will be revenue. The numerator is going to be the profit. Denominator is going to be revenue. Sum of revenue. Alternate result even if we leave it alone it will be Zero. Divide function helps us to avoid divide by zero errors. Now we can move forward and create visuals here. We can go ahead and figure out let's see total sales by individual
region. We can choose to create simple bar graphs too. So first let's figure out a simple table for region wise total sales. You can convert this into a simple column chart. You can keep this column. This is the region by total sales. You can go to the format. We can switch on the data labels right here. Currency data labels are in billions. We can choose them to keep it at the outside end. In the values, let's fix the units at medium. We'll select the theme later. For now, We'll keep our chart right there. Next, let's
see product by sales. We'll add the table again. So, this is product and total sales. Now, we have got multiple product here. The best here is the budget. Let's keep it right there. Again, we can switch on the data labels. Right here, we will fix the data label values at millions. We can make it bold to make the color Appear here. Let's decide the decimal values to be zero. Let's give a background color right now so that it is the charts are easily visible. Now let's see dealer wise. This is our dealership name and total
sales. Here we can trade and budget again. That's total sales by dealership here. As usual, we'll go ahead and switch on The labels and fix our labels that display on experience. Now let's move on and see some time series calculations. To use the time intelligence functions, we have to make sure that our date is marked as table. Let's let's see few examples. We'll make sure we give you a new measure again. A new measure here. First let's figure out what is the sales YTD that is year to year year to date. For year to date,
monthto- date, quarter to date sales use DAX time intelligence functions like YTD, MTD and WDD. Time intelligence in PowerBI uses special DAX functions to compare and accumulate over dates. These let us easily compute year to date, monthto date, quarter to date, year overyear changes. For example, YTD here total YTD here begins on first day of the year ends with the last date column. After applying specified filters can sum the revenue from the start of the year to the current context. The dates we are looking for is from the dates table right here. Before these will
work correctly, ensure your model has a designated date table as we have created just now. Next, for month to date and quarter to date, we will follow the similar time intelligence functions like these. So we're creating a new measure here. Let's see the sales month till date. We have total MTD And sum the revenue same will be followed with sales due today. Let's also compare last year sales versus current year sales. For previous period comparisons, we can use functions like same period last year, date app functions, parallel period or previous year, current year or previous
quarter, previous month, year. So let's use the uh very famous function Same period last year. This is last year versus current year sales. We use the calculate function. In the calculate function, we are going to ask that go ahead and give us total sales. Calculate total sales based on following filter. The filter we will give with same period last year. It is going to go ahead to the sales data. It connects with the calendar table and it will return the Set of dates in the current selection from the previous year. So the current year is
2025. It will go ahead and select the 2024. If the current year is 24, it will go back and filter out your table by 23. If you're in current quarter, let's say you're currently at quarter 1, then quarter 1 of 2025, then if you go back and select the quarter 4 of 2024, it is not specified with whether it is an year, month or quarter. As we have Seen so far, same period last year. It will simply go ahead and select the entire rows based on that particular period. It can be year, quarter, month or
weeks. Here we will simply ask it to filter down the dates column right there. This is last year versus six. So let's see in the next page here. I'm adding a multi row card for now. And this card here, let's look at let's add a table here. In this table Here, first let's go ahead and get our year and month name here. So, we have two here and month here. Observe that month is not in the sequence as we would think. month is in the alphabetical sequence and that's not what we expect, right? So, we
go back to table view and we'll select month name. Highlight month name here and ask you to sort by month number. When you go back, you will see now it is properly designated. The sorting is fine. Now let's add the calculations we have done so far. This is sales year till date. This is quarter till date. This is month till date. As you can see this is January, February, December. So the uh numbers are you will find are absolutely different. This is yearly, quarterly and monthly. Now let's compare last year sales with current year sales.
For that we add one more table. Here we are going to add year first and let's add last year versus current year. What we see here is just 2025 sales. Let's add month name. Let's add quarter for now. Now let's say this is last year sales right here. Whatever sales we are looking at is from the last year. I would like you to compare this year. This is 2025 quarter 1. And look here. This is 2024 Quarter 1. What we are actually looking at right now is 24 number right here. What we here have is
last year sales. Let's bring this year sales here and immediately you'll see the difference. This is this year sales and this is the last year sales. Now we can see the comparison right here. To compare last year with the current Year sales, we need to create uh more index measures. This is where the KPI measures will come in force. So let's compare profit for individual year. As of right now we have last year sales with us and this is current year sales. If you want to see how far we have uh went from year on
year or period by period how far we have come around here in that case we have to go ahead And see the comparison whether last year is greater than current year or not. First we'll create a one uh we will create new measures here. Now we click on the quick measure a pane will open up. Here we have certain inbuilt calculations. Here we can aggregate as per category. We do have filtered calculation and here we have time intelligence function. Right there we can talk about yearto date, Quarter to date, month to date. As we have
written here, we also have an option for year over year change, quarter over quarter change and month over month change here. Since we have seen the last year sales, let's see how do we track quarter over quarter change in the base value here. We are looking for profit currently. So let's bring profit here. In date we can click on add data. This is the date right here. Number of period Is one. We are fine. And we click on add. Wait for a few seconds here. And the function is written for us. So currently we PowerBI
has used a statement. First is v variable that is the name of variable is previous quarter and calculate function with date add function is giving us the total profit for the last quarter that is minus one that is last quarter and now it's dividing it by the total profit here. Let's go ahead and bring this percentage profit. It is formatted in percentage automatically. Let's bring this percentage profit right here in the table. And here we are. Now we can see quarter on quarter growth right here. These are few examples of time series analysis. Now let's
go ahead and create some visuals. We apply the functions that we Calculated the measures into some visualizations. Then we learn about analytics and PowerBI. Let's outline the report pages we will build. First we are going to create a dashboard overview. Here we can use card visuals or KPI visuals to highlight highle numbers. For example, you can create a card here for total Sales. Another card here for total units. So this is the card visual here for total sales. The following are the same here. Living a gross property. You can add it for total quantity that
is unit sold. You can also use PowerBS KPI visual to show actual versus target. Here is our work API visual. This visual here. So let's look at total sales here as per month as compared to last year sales. Once it is filtered down to last year or month, you will be able to see the excess. We are fixing the display on its smaller values. We can use format painter with colors. Here we can see whether we are above the go target or not. The Target currently that we have here is our last year sales. Next
from this page here we have our total sales by region, total sales by dealership, total sales by product. Here you can keep all of that at dashboard overview. The theme can be set and designed later. Now within here we will see more here. category with sales. We'll have one more chart here for region for state and sales and sales can be easily changed into a map. You'll keep a small map here. In the bubble map here we have uh the bubble sizes with total sales here. Let's go to the format your visual in the bubble
size here. Currently it is automatic. You can ask it to use the data range right there. And we can now see it changing. You can Also utilize the legend here or the color here as per the region here. That's great. Now we cannot have uh now we don't need the title at all. So we can go to general here. Switch off the title. We have got the legend right here. We are fine with the legend right now. We can keep it at the center. This is category by total sales. We can convert this into a
donut chart. Any donut chart here we will switch off the Legends right now. And we go to detail labels. In detail labels, we can ask you to only keep the category and percent of total. Right here it is not properly visible here. We can go ahead and change the font style and point size to make it more visible. To quickly design it, you can go to view here and apply a certain thing for a quick design. From here, you're free to choose any design you need. This is all about your theme. Most of the time
this theme is automat is provided by your organization. Currently I was sticking with a very simple theme right there. This is our dashboard overview. Next let's create a sales trend trend page here. So phone it and see the sales trend page here in this sales trend page. Let's color the background. First we'll create a line chart because We're looking for a trend here. From here we'll add the year and month right here and year and month and total sales. Now we go to the analytics pane here in the visualizations pane. Here is the line set.
You can switch on the markers and the visuals. Since we in the line chart, we will not be switching on the data labels. Here we are more interested In looking at the patterns and the seasonal changes. Here this is the overall total sales. Within here we can also go ahead and add gross. We can go ahead and add here the legend. In the legend here, if we add category into the legend, we will be able to see the trend over all the three categories. Right here, when we go here into the lines, We select individual
hatchback. For example, if you want to go ahead and give different colors to it just for the recognition, we can go ahead and actually do that from here. You can see the line right there for HP. Currently, it is near to blank. Likewise, we can go ahead and uh check for individual ceilings. And if you're looking forward, you can go ahead and change the color and line here. Color of the line here. This is for individual category right here. So let's take the trend for all the three categories here. We'll add a slicer for category.
Let's convert the slicer here with vertical list and single select. We going to keep it on top of the line chart. When we do this, when you keep it on top Of the line chart, we'll make sure we go to format and ask it to bring to front always. So when you click here, it will still stick here. When you click, you can see individual lines right there. After this trend here, let's look for the trend of year and quarter with gross profit. For now, let's create a stag chart. You can see over the quarter
the sales has been going down. Here Let's add units sold into the mix. Now we can see unit sold has been almost similar. variable difference here in the small multiples here if we add year we can see we do not have data for all the four years we have data only for 204 so it doesn't work this is the quarterly trend of profit and profit margin we can see it is going Below over the time even though total sales is currently going up. So currently we have total sales unit sold and the profit margin. This
is quarter on quarter here and remember that this is only for the hash pack for MPV it is going to be different. sedan it is different SUV is different. If you want select all option here you have to switch on the select all here. However when you see single select All option will not be available. If you want all option then we have to make sure that this is just checklist not an individual selection. any quarterly one here. You can go ahead and switch on the data labels to see here data labels doesn't make much
sense. It looks crowded. So if anyone wants to look at the data set, they can simply hover the cursor over there and they can easily see all the three Numbers right here in the tool. Let's add a separate chart. for the comparison of profit margin over the fuel type. Let's bring quarter here into the left hand. Now we can see in every quarter how within petrol it was lowest in the quarter one it went up this is diesel and here Let's switch the quarter and the type easy to know right now this is quarter 1
quarter 2 quarter 3 and quarter four we can see here right now this is CG he's a hybrid and petrol Now this quarter is aggregate quarter for individual year. We do have only one year. So let me show you by adding the slicer here. If I had a slicer here for year let me convert this into a vertical list. You see you have only two years but 2025 we have absolutely no data. For 2024 here you have the quarter. So we are good with 204 individual quarter right here the numbers doesn't change. Remember that. Go
to format and make sure you bring it to the front. So this here is our profit margin for petrol, diesel, hybrid and CNG auto vehicles. Right here You can easily see right now if you look at CNG here. All right. So CG has uh been for quarter 1 and quarter two it has been the lowest in third quarter it was the giving the highest profit margin and fourth quarter it goes below. Similarly, we can track these hybrid and petrol. Petrol definitely shows us the increase in profit margin. Next here we'll add with performance versus targets.
And performance versus targeted. It'll go ahead and create few more DEX measures. These next measures are going to help us to determine how much traffic we had, how much customer traffic we had out of this, how many leads we are able to extract and then we are going to compare this with the extra uh with the achieved revenue, sales and profit. That is going to be performance versus targets. We are diving into exciting World of advanced interactive reports in PowerBI. If you have ever wondered how to transform your static reports into dynamic engaging reports, this
video is for you. We will build a report focusing on advanced features like filters, drills, bookmark, dynamic tool tips and KPIs. By the end, you'll have a dashboard that not only looks amazing but also tells a compelling story. Let's get started. When we talk about features like Bookmarks and drill through, this is a performance review report for the retail business unit that we have created in the previous video. First, let's add advanced slicers to filter data dynamically. As we have understood the difference between slicers and filters. First let's explore slicers in the visualization pens here.
Here we have a slicer. First we can click on the slicer here. Let's add region to the slicer field. Let's resize it to fit neatly on the left hand side. Currently the slicer here has vertical list with the check boxes here. We can format it by using the format painter. Now observe here when we click on east entire dashboard filter out only to one selected region. We can also select multiple regions with control and click. Clear the filter. We can clear the selections from here. Let's add another slicer With date. Let's drag and drop date
to the slicer region. Here was a categorical value. Date here is a date type. As you can see clearly when our data is date inside the slicer we are getting a slider from the start date till the end date here. Let's explore the types of slicer here. To see the types of slicers we click on format your visual And we go to slicer setting. In slicer setting the style of the filter slicer settings the style of the slicer is between the two date then before the date after the date. We can also find relative date.
Native datas. If you want to go ahead and look for last, last, next or this, it can be last month, last week, last year or this month, next month, year. This is Dynamic or relative filter based on your system date currently. Then we have related time. If you have date and time both, you can also use relative time filter which is quite handy to do hourly analysis. Currently we are going to keep at between. This is the date slicer. Let's add one more slicer. This time we will see new slicer with the lightning bolt. And
we click on new slicer lightning Bolt. Let's add customer type. Customer type. We can see there are two cards inside one single slicer. To understand this, let's go to format your visual. You open the slicer settings. Here in new slicer, slicer settings only give us the option for selection. What kind of selection we are looking for? In the layout, we have the option to select the arrangement. Currently, as you can see, we have two cards only, Two types of customers. Instead of keeping a grid, we can keep a single row. The single row we have
two type, we can keep two buttons only. The new slicer will always have the cards inside it. it will not give us the option like vertical list between here. So this is the customer type. These are the two cards inside the slicer. I was setting the layout here. Let's use the format painter. After format painter here it is quite visible. Buttons are not getting formatted. To format the buttons here, these are the buttons. We have more options for formatting. The new slicer gives us option to make slicers interactive like we are using an online application
and to make it interactive with the end user. For example, when we hover the cursor over the corporate button, it darkens with the color. When we click Here, it gets a shade darker. And when we select it, it becomes black in color. Shows the interaction. It also make it look like we are actually pressing a button. So interaction is enhanced. To define these type of interactions to customize these interactions, we have multiple settings inside buttons. What are stages? First one is default. What is the default without selection format setting? When we hover, how it Should
behave when someone hovers the cursor over the buttons. When we press the cursor, how the change should look like and when any button is selected, how it should look like. There we are going to start with default. Default the background is white. Border is fine. In the fill here, let's follow the color here. You can increase the transparency too. If you want, you can also add an image. Currently, we're Filling the color. We do have the option for shadow and glow. Now, when we hover over it, when we hover over it, see here, it is
now changing the color. We are fine with that. It is changing the color on hover ring. This is what we were looking for. Now on press here on press here it is giving us the third color which is not good. So we will go ahead and choose the Color from similar family and it will get a little bit darker. Here I will reduce the transparency too. Now hovering is lightening up the color. pressing is making it a shade darker than before. On selection, it is turning black here. Now we select here the state selected and
instead of black here we'll simply give the same family color here. Now let's see here hover press leave Hover press leave. It looks like we are the end user is interacting with the buttons. We do have the we do have the glow option. So when someone hovers over it, it glows like this. This is a new slicer with more enhanced interactive features. Let's add one more slicer. This time we will be using the simple slicer. And let's add discount in the slicer. Since discount is a number, we will see a slider. Again, this slider is
giving Us the option from 0.05 to 0.025 here as in percentage. So let's check on discount here. Currently the discount is a decimal number. I think. So let's convert this into percentage. We can see now it is 5% 25% here. Let's format painter. Here we are. This is the second slider that we have discount everyone. It is a series of numbers from 5% to 25%. We can see in these slicer settings the options that we have available are between less than Equals to greater than equals to with drop-down individual numbers here and the tiles option
similar to new slicer and a vertical list too. Since it's a number between slider looks much better. It gives us option. It gives us option to filter the entire page currently. For example, we want to see the performance report the sales and profit. If we are Giving the discount between five and 10%. So if we give the discount between 5 and 10%. This is the report. Now if you give the discount on the higher part let's say 20 25%. This is the report. Define the 20% here. Type 0.2 here and press enter. These are slicers
for date categorical variable and the new slicer too. When we have date and numbers it gives us slider by Default. When it's a categorical variable, it gives us the vertical list. And the new slicer will always give you the tags. Once we have added the slicer, we do have other reports here. Report two and report three. If you want to go ahead and reuse these slicers in the next reports, we can easily copy and paste. We're going to select the slicers here. Going to select the slicers here. Copy And paste them in the next report.
We can certainly do so. When you will copy and paste with simple Ctrl C and Ctrl + V, a dialog box appears. It says that would you like to sync the visuals? It says that one or more copied visuals can stay in sync with the visual it was copied from. PowerBI simply won't say that if you will change anything in report one by using these slicers. For example, if you select south in report one automatically, Report two will also convert chart and filters every chart and card with that particular slicer. It will be syncing the
slicers on both the reports. For this demonstration, we are going to sync these slicers. We won't see any difference right away. To understand the difference here, let's go ahead and create a table. I'm creating currently a simple table with category Category and region. Let's say total profit and sales and the discount here. The discount here. Let's convert this to average. It's a small report. Let's also add the quarter to track the changes. Now let's apply the slicers. We are not going to apply the slicers here. We will go to the report one and apply the
slicers here. Let's select the corporate type customer In north region and let's say we are looking for the last quarter. So we have made the changes on the slicers. We have made the changes on the slicers in report one. You click on report two. You'll find automatically it is selecting north. We are towards the last two quarters here. Quarter three and quarter four with only north region. And if I report one and two, all these slicers are synced. If you don't want to Sync the slicer, we can still copy and paste. However, then both these
slicers will be independently filtering the connected pages and graphs. That was types of slicers. Let's move forward with other component of the dashboard that is drill through and drill down. First we'll go to drill through. Drill through here enables users to navigate from a summary report to a detailed view of a specific data point. So let's set up a drill through from report one to Report two. To add del through we need one bird eye summary report and another detailed report. In this performance review report we can see u analysis by region category and customer
type year and quarter and the state. To deep dive in the report too currently we have added a matrix with the matrix. Here we can add more detailed cards. For example, let's go through the cards here. These cards here are Here to show the KPI metrics. Currently, we in the KPI metrics, we have sales. In one card, we can add only one metric. So, here this is the sum of sales as we can see. Let's format it here. However, the title font is too big. We can go ahead and change that here. This looks better.
We can resize and place it accordingly. So, this is our sum of six. In a similar manner, if you want to show Other matrix, we have to add same card and we can add only one number, one matrix inside one card. If you don't want to do that, we have too many metrics to show. We do have an option of multirow cards. Multiro cards here can show multiple matrix within one single card. However, they will not be presentable as a normal card. Normal cards, we have the ability to change the Title. Here we cannot change
the title. We change the sum of discount into average. This is multiro cards. Then we also have a new card here. Let's explore the new card. In this new card here, let's bring sales profit unit sold. As you can see right now, if we are adding numbers, we are we can also go ahead and add It gives us simple tiles exactly similar To the slicer we have seen before. Let's format. In formatting first we have layout. In the layout currently it is a single row. Let's convert this into a grid. In the grid here we
get to choose how many rows and columns do we need. Currently it is showing just one column. Five columns and three rows. Now here I'm going to change the number of columns to two and maximum rows to three. After layout here we have option Of callout values where we can decide the font. Here I'm keeping the font as 24 invisible. Let's decide this. Now we can format paint it. We can decide it to keep it at center. Decrease the font a little bit more. After the value settings, we have the layout for vertical alignment. We
have card settings. Here these are the cards. Here we can choose the shape of the cards. You decide how Far they are from the border, the alignment of the cards and the background color of the cards too. Here we have the card settings. For this also we can use the format painter. Here we have got cards too. In general settings under the effects we'll have the same background and border options. Here remember that these are for the back card visual not for these buttons here. We can select the background color. Increase the transparency. Looks much
better. Now we can also give a visual border with the increased rounded corners. Currently this here this is our new card. In this new card currently we have added only numbers. Let's add a dimension or a categorical value like customer. When we add a categorical value like customer let's say currently customer type selected one is retail. But if I and if I click on corporate, it will remain corporate. However, if we clear out the slicer here, it will still get a stuck at the first customer corporate which is quite misleading. Usually adding dimensions inside the
cards is avoided unless you have a very good option to make it work. So inside this card, let's say the fourth metric that is left that is our discount. Uh now we are going to have four cards Here inside a grid sum of discount doesn't make sense. It should be average. We can go to the drop down and convert this to average of discount. So now we have KPI matrix available with us. We have got KPI matrix everyone with the KPI matrix here. Now we have a detailed report. Now let's set up our drill through.
I'm to going to click on report one at the back And let's observe in the report one here below the visualizations we have drill through cross report available here. When you hover over the cursor over the cross report here, it gives you an information that it allow other reports to use this page as drill to destination meaning we need to decide which will be our micro report and which is going to be our macro detail report. Destination is the micro report. Currently report one is from where we want to start which should take us to
the report two. So this detailed report will be should be switched on for drill through here. To set up the drill through there are few prerequirements. The pre- requirements are the report one is currently using category customer type region as a categorical variable with The sum and profit. In the page one we also have a state and year and quarter used these measures and dimensions should be used in report also. If we go now to activate the drill through we simply switch on the cross report and report two. Switching it on we need to add
the drill through fields here which bit chart will have the ability to give us the drill through option and to Filter out the report too. On the basis of that to add this here let's go ahead and add region sales profit and category. We are dragging and dropping. We have added category profit region and sales. As we added all the four here, you'll find that these looks like filter cards. It also give us the message that it is going to allow the drill through when it will be used as a category sales and property numbers.
So when they will be summarized in a report, they will use this report too as the destination report. After adding this here observe everyone on the left and upper corner of your screen a button will be added. This button will help us to navigate from one report to next. Let's see a review. First let's bring the button where we can see it here. Let's click on report one. In report one there seems to be no change. Let's find out by example going to top five estate by sales. Sales is getting summarized here. So if we
go to the chart here and we right click uh we select For example Scotland and we right click here. It allows us the drill through to report two. Meaning we will be looking at the entire report only for Scotland. Let's click on report two and drill through and see. Now we are looking at the filtered down report only for Scotland. What are the information available to us right now? In Scotland, we have done our sales starting from 25th January and the Last order was at 2nd February. We have given the discount from 6% to 25%.
Immediate insights everyone. Plus, we have given total sales of nearly 50K. This is the average discount and the total units sold. We can also see the KPI cuts in the new card matrix too. And what we were able to sell out, we were able to sell clothing, electronics, furniture and groceries. There we have the detailed report within deep dive for one single state. To go Back here, since we are in developing mode, we will be control and clicking here to go back. We'll go back to a normal report. Let's try more deep dive. Let's click
on retail here. Now this report and the second report since the S uh slicers are synced both are showing us retail customers in the retail customer type the highest sales we have seen in the electronics here. So let's right click on electronics and drill through everyone To report to. So here we can see customer type is automatically retail. The first order was near 11th January and the last order was in October. Here similarly we have given the discount in a range of 8 and 25%. On an average it is 16.5%. Here we have the category
only electronics. This drill through will help us in deep dive and navigation from the macro Report to the detailed in-depth analysis. us to navigate from a macro summary report to a detailed in-depth view for a specific data point and KPIs. Talking about navigation here, mark are a fantastic way to create dynamic navigation. With the bookmarks, we can set up a before and after view. We can set up a navigation with the help of buttons or with the help of bookmark navigation buttons too. So let's explore how Bookmarks and navigation work hand in hand. Let's take
an example for bookmark. To understand the bookmark better, let's go to the view tab. In the view tab, we have filters pane here. Then bookmark and selection. To understand the bookmark better, let's click on bookmark pane. And bookmark pane has to be opened up with the selection pane. Why? Selection pane will tell us the list of the objects that are present in This particular page. And what is a bookmark? A bookmark here will help us to create multiple views within one single page. In real time, bookmarks are used to set to used in a book
where we go ahead and we place the bookmark on a certain page number. So that when we come back, it we can go ahead and simply go to that bookmark and restart exactly from that page. In a similar manner in each page, we can set up multiple bookmarks and each bookmark Will give us a separate view of one simple report. For example, let's create a simple bookmark here. So, currently we have these objects and the slicers here. Let's say we want to see the sales report first and then we want to see the profit report.
So, let's create some space here. A profit report everyone. Now this particular chart should appear later. We want all these three charts first and later we want the sum of Profit by region to appear. So we want two views. One view is entirely for the sales and the second view where we have the detail for profit is also added here. Let's set up a bookmark. We set up the bookmark here. Our objective is absolutely clear. In one view, we want to see all the three charts, but not the second one, not the profit one. In
another view, we would like to see all the charts together. We'll use the Selection pane here to select and show, hide the object. As we can see in this selection pen, we have the show and hide option here. For example, we want to go ahead and the profit chart. So, I'm selecting the profit chart here. The moment we select the profit chart here, observe here, sum of sales is automatically highlighted in the selection pane. Now, we can hide it here. Okay. Click on hide this visual. No Accident. And don't worry, it is just in this
view here. Once you have written this here, we will go ahead and click on add. As soon as you click on add, this bookmark one do not have profit visual only sales here. So we can go to the three dots here or epsilon and we click on rename. I'm going to name this as sales report. So we know only sales visuals are there. Next, we need all the visuals at one Place. So let's unhide this visual here and add here. We go to the three dot. We rename this to sales and profit. We have these
two bookmarks. Let's click on sales report. It is gone. Sales and profit. It's here. Now these bookmarks can be used to make multiple views out of one single report. The similar manner we can use these bookmarks to switch between different type of charts within one single page Too. Go to new page for demonstration of bookmarks. First let's create two visualizations. First visual we are creating a simple stack bar chart for category and sales. A simple bar chart everyone for category and six. I'm keeping it right here. Next here let's create A line chart. between profit
and our date hierarchy hierarchy. We don't want to see day wise here and year and quarter together. We are good with this. This entire profit trend is for all the regions. We will scroll down under the visualization and we drag and drop region to small multiples. Now we can see the trend for individual region. Here you do have the option to add legend. Here the legend we can add customer type. Now we can see trend of profit for each customer type. Let's format them. First formatting, we change the color of the line here. Series, we
select corporate right now. And I'm going to change the color for corporate. Then we select retail series. I'm going to change the color of retail To the red family for now. Changing the colors. We have the option to switch on the markers. So here we have a very clear view of all the trends for the profit. Give a quick format for the background too. So here we have our two charts ready. Both the chart this here is for sales and this here is for profit. Now we want to switch between sales view and the profit
view. So either we want to see the sales analysis or we want to See the profit analysis. So let's place these charts here first and let's enhance the bar chart here. In the bar chart here too we will scroll down in the small multiples we add region. In legend here we add customer type. the same color. We will go to the bars. In the bars, we select corporate. Corporate is blue in color. Then we select retail which we have kept Red family color. Be consistent in the colors everyone. And now here we have sum of
sales by category and customer type. And here we have profit by month and year. Now we want to switch from sales report to profit report here. To do that we are going to utilize bookmark. Expand bookmarks and selection pane. Observe that we are in the report tree. However we can clearly see previous bookmarks too. To complete the report, I'm going To copy the slicer region into the report three. When I paste the region here, it will ask us, would you like to sync the visuals? This time we will not sync the visuals. Plus we will
change our report slicer from vertical list to a type. Once we have the slicer now let's create a bookmark. Create a bookmark here. First is the sales bookmark. Another will be the profit bookmark. So first we are going to hide sum of profit that is the line chart. We add the bookmark. We rename it and we'll say this is the sales. Next we view the sale here and hide and we click on add. And then we rename again to profit. Now we can easily switch from sales to Profit here. Yes. However, we don't want to
see it like this. The end user should have the immediate option to switch between these within their canvas. If you're looking for that, we have two options. One is to add the buttons. Another is to add the bookmark navigation. So, let's see the newly added bookmark navigation button. In the home tab we have view tab. To add the bookmark navigation we go to insert. On the right hand side we have got Buttons and here we have navigator. In the navigator one navigator here is bookmark. When you click on bookmark navigator on the left and upper
corner we can see the list of all the bookmarks at one place. However we don't need all the bookmarks here. We need only sales and profit. If you go to the format navigator, format navigator. Here we have the option for bookmarks. By default, it is going to select all the bookmarks. Here we go to the Drop-down. It doesn't give the option to select the which bookmark we need. So let's see how we can make sure that we can only see sales and profit. Go back to the bookmarks pane. We will control and select sales and
profit. Control and select sales and profit. Say both are selected. Now go to profit and right click. When you right click, you'll have the option of group. Let's group them together. It says group one. Let's rename the group one to report three. So you will remember that this is the report three group. After rename we go back to format navigator and to view all the bookmarks to view the selection between the bookmark and the group we have to add or refresh the report again. So I'm going to delete this here. Let's remove it and then
go to buttons again navigator and click on Bookmark navigator. Still all the four are here. Now we go to format navigator and bookmarks. When we go to the drop down here we have the option right now to select. Go ahead and select report three group and see now we have sales and profit. Now with control click on sales edit is sales. We click on profit. It is profit. To make it make the buttons look better. Now we have options for formatting The style of the buttons. These four styles will remain with default word presence selected
similar to slicer. However, we will have more options here. Here we can also change the font styles too. I will give you a quick style setup to make it look like the buttons are interactive. First, the default style is the white filled text. Let's change the text to a slider bold font. After the text here, we'll go to the fill color, which is going to match one shade lighter than the background. The border here will look same. Right now we are not giving any shadow or a glow. This is the default view. We can see
that in the profit. Now when we hover over it, it turns white. We go to the hover here and we'll ask it to make it one shade darker. And in the text we'll ask him to increase the font By one. Now see it looks like we are hovering over it here. A small change after hover. After hover we have press. Currently when we press it looks like we are pressing it here. And the texture it will remain same but it fill here it gives one darker shade. So when we are when it is default we
hover and we press. Now the selected view is black. We'll change the selected view right now Back to the darkish area. Plus we increase the font size with the bold font. See the entire process. This is hover, press and select. Hover, press and select. So now our bookmark navigator is working. However, our report looks like we are jumping from one page to other. Now we don't want end user to know that. So we are going to make sure that both of them Are at the center. Both of them are at the center here. When I
go to profit, go ahead and set the profit here too. Now let's check this sales. This is profit. Looks like we are changing the entire dashboard with the help of few buttons. You can change the entire dashboard from sales to profit. You don't need to create two separate pages for that. Bookmark can also help you to switch Between just one single chart within the entire report. This is how we can use a bookmark for navigation. Bookmark has major uses. We will explore the bookmark in a entirely separate video dedicated only for that. Now let's go
to the next option to make dynamic report that is dynamic tool tip. Dynamic tool tips let users see additional insights when they hover over visuals. So what is a tool tip everyone? When we go to a certain report and we Hover over any chart whe it's a bar chart or a tree map a white colored dialog box opens up. This is called as tool tip. So currently the tool tip is same estate and the sum of sales. We go here to the tree map. It is giving us more options here. So let's go ahead and
see how we can go ahead and give a dynamic tool tip. To do that first let's add a new page. In this new page, first we will set this page as a tool Tip. Whatever we create on this page, it can be used as a tool tip. We'll go to format your report page. And here we have Canva settings. In Canva settings, the default is 16 by9 slide. We will go to the drop-down and here we have the option of tool tip. A height and width is here. Now here we have a tool tip. We
can see that the canvas is quite small. We can click on plus to zoom in. Now here we can design a tool tip. When we say design a tool Tip here. So for example, if I go to this report here and hover over, we should be looking at a deep dive analysis within the Scotland. So right now we can see top five estates by sales. If I hover over the Scotland, I want to go ahead and see more details about it. So for example in the page one here I will open up the data pane
right now and let's say we want to see that how much profit we have gained In that country plus we'll also add category for the details now it has automatically chosen a type of a chart let's go ahead and convert this into I stacked bar chart easy to read the names here. We can go to here and switch on the data labels for tool tip. The name is quite huge. So I'm going to go to general right now and switch off the title all together. We can see category and country and sum of profit. We
don't need the now here Let's format. We don't need to see country. We know these are the country names but we do need the name of sum of profit but we don't need the numbers here and the numbers are indicating what kind of category it is. So we do not need this title here. So let's format in the formatting here the first form in the first formatting we go to y-axis in the y-axis here we need the values but We don't need the title in the x-axis here we need the title but not the values
then we have legend the legend however we don't need the title we can see all the four are here Plus, since it's a tool tip, we'll decrease the font size here. So, this is a a small bar chart. Everyone, first let's explore edit interactions with filter and highlight. Once we have A dashboard like this, we would like one chart act as a filter or highlight or simply do not interact with it. As you can see on the right hand of the corner right here, there is an edit interaction button. You can set the interaction for
individual chart that how it interact with other charts. Here also observe on top of each chart. There are two types of interactions either filter, highlight or do not interact. We can find the setting Interaction feature within PowerBI to gain more detailed insight within the dashboard. Filter simply filters the bar charts or the line chart or the map chart here or a simple table. Highlight simply highlight that particular bar or part of a bar or part of a line where these edit interactions are usually set by default. However, we might need to change it as per
our requirement. So let's see how to do that. Now let's look at the steps to edit the interaction. First we have to select the chart which we intend to use as filter or highlight. Then we click on format tab. In the format tab here on the left and upper corner you will find edit interaction under the visual tools. Once the interaction mode is on we have to first select the chart and then we can select whether we want other chart to get filtered by the selection or to highlight or we do not want it to
interact. This interaction mode will be Also available even for the cards that we add. We can have this interaction for the slicers too. Not only charts also the slicers after edit interactions which is a basic interaction available in PowerBI reports. Next is bookmarks in PowerBI. Bookmarks are a very useful feature within PowerBI. They help us to save multiple views of one single report. How do we use the bookmarks here? Bookmarks allow us to create multiple views of a report page like Including filters, slicers, multiple visuals, hide and unhide. So one single page inside the PowerBI
can be converted into multiple views and those multiple views either can be navigated like a slide or a picture to represent different perspectives or states of the report. Now how do we apply bookmarks? First we need to go ahead and switch on the bookmark and the selection pane. So we need to go to build up and we have to click on the bookmarks pane and the Selection pane to activate. Once we are there and on the right hand side we can see the bookmarks and the selection pane. We can go ahead create our selections and
add to create a bookmark. So for example if we have a slider for example if we have a hierarchy to choose we'll choose a hierarchy. When the hierarchy is freezed on our screen, we can go ahead on the right hand side and simply click on add a bookmark and name the bookmark. In a similar manner, if we Have an entire dashboard, you want to spotlight one of the charts there. Go ahead, select the spotlight and freeze that screen with the bookmark like this. You can also apply for example a country filter. So let's say you
apply the filter for India and the entire data currently the entire dashboard screen or your report screen is showing all the data that is related to India. Now you can save or add that as a bookmark. Now we can switch between two views. First Is the global sales and then sales within India. So we don't have to create multiple page reports here by simply adding the bookmarks. We can go ahead and create multiple views and navigate through the bookmarks too. Let's go to the PowerBI and see some examples. So since we have created multiple bookmarks,
now we would like to navigate those views. This is very easy. There is two types of navigations. Page navigation and bookmark navigations in The new updates last year. Now we don't need multiple shapes to be added. Set the bookmarks or we don't need buttons there. However, we can utilize that here too. to activate the navigation or use the direct navigation available here. We have to go to buttons dropdown. So let's see these steps here. If you want to add if once we have created the views, if you want to add buttons, we have to first
click on a chart. Once you click on a chart format and data drill option Opens up. Go to the format tab. In the format tab, you we are going to find buttons. Once you find the buttons there in the drop-down, go to the last section and you are going to find navigation. In the navigation, go ahead and select bookmark navigation. Once you select the bookmark navigation, you will see the list of all the bookmarks and the views you have created on multiple pages. Now you can go ahead and navigate. Let's see the example and power.
Currently, we Have seen we can directly activate the bookmark. There is an entire strip of the buttons. We can click and navigate to whichever view we want. However, we can also choose to switch between only two or three bookmarks, not all the bookmarks there. For that, we can use button and images too. Meaning, we'll go ahead, we get a button or a shape and we assign a bookmark to that. For example, here once we open the bookmarks and the selection pane here, we can link the Bookmarks to certain chart or to a certain image or
a shape. It creates an intuitive and a very easy visual user interface. These steps will involve we insert a button or image from the insert tab. We select the button and we go to visualizations pane. In the visualization pins under the action, we choose the bookmark and the action type. Action type will be the page of the bookmark. We can use this to switch between two charts within the entire Dashboard. We can also choose this simply switch between two entirely different views of a simple page report. Once we go ahead and select the bookmark we
want to associate with that button or image, we can we have to do that for multiple charts together if we want to switch between the two views. For each view, we apply two different images. Once the images are done, we customize the button and image style for better user experience. And to set the theme of The page and if you want to go ahead and hide hide and unhide them accordingly, we will place them maybe overlap with each other or maybe at two different places as the requirement. And once the bookmarks are analyzed, we can
simply click on that image control and click and we can switch. The developer of the designing mode once it reaches to the user, the end user will have to simply click and it will switch the charts. Let's see an example in PowerBI. Next, We'll see working with parameters. Now, parameters are far more different than the interactions and the bookmarks we have seen so far. Parameters are simply one step ahead of filters. Let's understand what parameters are. Parameters are found in every BI tool. Parameters in every BI tool allows us to dynamically change the charts and
the visual based on the user input. Usually parameters are just the range of values passed from the outside by the end user. For example, we want to uh we have a chart where we have sales versus countries. Now we can give an option to the end user where they can simply change the x-axis entirely. They can change the uh country to category from segment from a states city. They will keep on changing the dimensions and the chart will automatically update as per the dimension. You can also give them access to change the buyaxis. Maybe they
want to see country by sales, profit, Total quantity, total number of orders and likewise. So if you want to go ahead and change the dimensions, maybe you want to go ahead and change the filter. For example, you want to see top uh we usually go ahead and tell them these are the top five customers or vendors we are working with. We can also go ahead and give them the access that okay you go ahead and see what we whatever top end numbers you want to see. Meaning they will have the option to change the Filter
value to 3 5 10 20 whatever they want to see. and that's parameters that will change the data displayed based on any categories or numerical range they are selecting. There are multiple use cases body parameters. There are two types of parameters here. First is numerical parameter and that is speed parameter. As you can see in the example here for the numerical parameter we have given a discount slot and a slider. So we can simply click on new parameter. There we can select an uh what if parameter or a numerical parameter. We can give a simple
number here. Once we create a a slicer link to the parameter and it is always created, there is a simple checkbox there. Now further we can use text measures to incorporate into the measures into the filters as we apply and these measures give the end user the flexibility to input the values within the calculation or within the filter or within the x or yaxis to Change and view the reports in the field parameter. We can go ahead and change the x and yaxis. Field parameters are very easy. We will simply go ahead add the parameters
there and uh as we discussed we can simply change product category, region, country or segment. We can also go ahead and change sales and profit. These text parameters are written automatically there. When you are creating the parameter at the back end, it is simply writing a text and we Can utilize that particular parameter and multiple calculations too. Now let's go ahead PowerBI and see how parameters work. This page we are going to use as tool tip. I'm going to name this as tool tip one and we will use utilize this particular page as a tool
tip for other charts and the reports. To create this as a tool tip, first thing that we do is we go here to the right hand side format your Report page. We go to the canvas settings here. In the canvas settings, we'll go to the drop-down and select the tool tip. Now as you can see here the dotted lines here are showing us the true size of the tool. Vertical alignment will remain at top. You can also keep it at the middle. I prefer the I prefer it here. Inside this we can add cards visualization
or simple tables. Use it as a simple canvas. However the size will be different. Now inside this Here we go back to build visual and let's add the new cards here. In the new cards let's add our sales. We have our profit. discount and the unit sold. These are the four cards here. As you can see, all the four cards are simply added one beside the other in a vertical manner. We can go to the formatting here. We do have the option of layout. We can ask it to arrange in a grid. How many rows
we are looking for? So, we can increase the rows by two. How many Columns we are looking for? So, we can reduce that to two. Now, we have a grid. After creating the grid here, we don't need to see the overflow. We have just four cards here. We open the call out values and the call out values as you can see font size are too big. We'll stick to our font here. We'll reduce the font to 12. As you can see this is the values font. So the value is smaller. Here we Do have the
option we can align it at the center too. Once the values are done, we are at the label everyone. In the label here, we can decide what kind of labels we are looking for. We can also reduce the font size here and make sure we are selecting the font size here. Font time font size. When the size is selected, we will keep the layout at the middle. Then let's go to cards. In the cards here, we have multiple shapes. We Will uh let's go ahead and change the shapes. Here we have uh the snipped shaped.
Here we can change the shape. We can change the shape too by simply uh deciding here where we the padding simply means the difference the space between four cards. Here we can keep it narrow. Make sure the alignment remains at center. Within the cards here we can decide the background color. Please utilize your organization colors for that too. You can also add the images at The back. So this is here we have multiple borders. You can decide for the border colors here. So there is multiple options here for formatting. First the cards are selected and
created. Now we can utilize this as a tool tip on other charts. So let's see how do we add this as a tool tip. If I go to report one here in this report one this is a simple column chart here. So let's look into the column chart. The Tool tip that we have currently is simply saying New is best sum of sales is that and we can easily see that within the chart too. So let's add a better tool tip. To add a better tool tip here, first select the chart here. We will go
to the format your visual under the visual settings here and we have general settings. In general settings go to tool tips. In tool tips we have the option that what you are looking for. Are you Looking for a report page or the default one? Here you can see we are looking for a report page. And once you will go to the drop down here you will automatically find tool tip one cards in the dropdown. Once you select this here tool tip one cards. When we hover the cursor over here you can see the card appearing
right there and the number will keep on changing as per the selection. When we see it like that the size is too big as per as we See in the column here. We can go ahead and customize it too. As you can see here we have multiple options for the tool tip. If we have something typed there we can go to the tool tip one card here. It looks smaller here. However, we click outside. We go to the format page and canvas settings here. In canvas settings here within this tool tip you we can customize
it too. So when you go to tool tip you can see height is 240 and width Is 320 right and we want it to make it a smaller. So we can go to custom here. First thing that we do here is we make it a smaller like this. Right? So for example if we're keeping it smaller within this custom tool tip let's see I'm going to keep it here a smaller. Does that make it a smaller in the report? Let's see. It doesn't. It still leaves the white space. So, we go ahead to custom page
navigation. And the canvas Settings, we will go to custom. In custom here, let's reduce this size height 200 and width here to 250. And you can see now this looks much better. Now we can see the report one. You will see the size will be immediately copied. Now likewise you can decide on the size of the tool tip marks here. You can have another type of tool tip cards Where we have multiple visuals added it here. These visuals should be added again within the canvas on the tool tab. Now when we go to build visuals
here. So uh for example waterfall chart. So let's add a waterfall chart here. We'll add this waterfall chart within the quarter and the profit. You can easily see it here. This is s of profit by quarter. We can go to the settings here first. Switch on the labels. Now on the y-axis here, let's switch off the values. We don't need the values here. Once we have this here, we need to make the title. Let's clean it. Now we can go to general. We have the title here. Let's reduce the title by nine. Font size will
be nine. Let's select our point style two and this is simply profit by the bottom. Now Above this here we can also add a donut chart. Don't worry about the alignment and the size here. First we going to add this here. There we go. Now let's create a donut chart between customer type and the sales. Since it's a donut chart, it we will simply present only the percentages and nothing else. So we go to the settings again. First is we have to keep the legend. We have to tell them what type of customers are or
we can simply add it into the details table. Here in the detail label we can say that simply go ahead and tell us category and percentage we'll go to the slices and let's change the color here that's retail and corporate same here we have to go to general and we can edit the title right This is sales by customer type. It's a small tool tip. If you want, you can color the background too or you can make the background purely transparent. So, it can simply uh refer to the back end. uh these kind of reports
which is uh detailed there right these kind of tool tip can be applied to several other reports so for example this is our country the sales and property here the bookmark that we have added right so for this here let's add a bookmark for this let's add the tool tip to add The tool tip here we go to general again we go to tool tips and now in the report page just observe you currently tool tip has multiple text here that is the automatic tool tip. Now we have to tip the card for visuals. Once
we activate that automatically you can see the entire chart right now is getting filtered out with the help of the dimensions and the category and region. Similarly if I click on profit here we have another chart right. So we select On on this chart tool and we select our tool tip visual and this is a line chart for individual quadrant and the ear and you will see changing here too. It's a quite a fantastic visual here. You can always go ahead and color the background. This is all about the interactions here. Let's have a quick
recap. We started with a simple report. We have edited the interactions here. We have also added the interaction with The slicer and multiple sliders. We have explored in the new lightning bolt slicers and new slicers and the old slicers here with the help of edit interaction too. Then we moved forward and we have understood about date slicers, cards and multi- row cards too. In the third report here now we have jump uh we have seen bookmarks. In the bookmarks here we have understood that we can navigate Through in the bookmarks with sales and profit. Likewise
then we have seen how to add the tot cards here. So right now this is the report that we have created before and we have explored multiple different types of visualizations with the interactions tool. Thank you. I will see you again with the DAX and the parameters. Thank you. Hey everyone, welcome to Simply Learns YouTube channel. Today we will learn about data transformation in PowerBI. That said, if these are the type of videos you'd like to watch, then hit that like and subscribe buttons and the bell icon to get notified. So what exactly you mean
by data transformation in PowerBI? So data transformation can be a little similar to data cleaning. So before any kind of data analytics, you might be receiving raw data from a website source, a SQL database or an Excel file or multiple sources right once after you Receive the data whether it is batch mode data or streaming data you are supposed to clean it right you might have to clean up the discrepancies like the blank rows or the blank cells or any irregularities in data such as wrong data type right such discrepancies from the data should be
eliminated in the first stage of data analytics. So that's exactly where data cleaning and the data transformation comes into the picture. So you have various tools for data Cleaning and data transformation like Excel SQL. But if you are a PowerBI user then good news for you Power Query and PowerBI can assist you in terms of data transformations. So in this tutorial we will be discussing the fundamental the most important day-to-day data transformations which a data analyst takes care of in the process of data analysis is what we going to discuss today. So let's quickly switch
to PowerBI but before that let's have a Overview of what kind of data we exactly dealing with today. So we are dealing with super data and that's in Excel format. So this is our super store data set and we have four tabs here. The first one is orders tab where we have customer ID, order ID, date, ship date, ship mode and customer name. And in the second tab we have stores data set which has the details about the customer from where he or she is, the state, city, postal code and which category or Subcategory did
they purchase and order sales discount profit everything right and here we have some information about any of the orders which were returned and some people over here. So these are the four tabs that we are dealing with today. Now that we have an overview on the data that we're dealing with, let's quickly now switch to PowerBI. So now we are on the PowerBI window. Can just discard changes. There you go. Let's quickly import the Data from our downloads. This this is the data set that we want to deal with today. Now, it might take a
little while to connect to that particular data set and load the data onto PowerBI. Just a couple of minutes since the data set is a little too heavy. It's about 10,000 rows. So, let's wait. Shouldn't take long. There you go. The data got successfully loaded. So you have the option of loading what kind of data you want. You Have four tabs as we just discussed. You can load the orders tab and you can load the stores tab. And in case if you want the returns tab, you can also load that. And if you want all
of those, just load all of those. Right now I just want the two tabs. orders and stores. Now here I can just directly load to get started working on this. But in case I don't want any kind of discrepancies, in case if I have a doubt that this data might not be cleaned, I shall go with data Transformation. So ideally you should go with data transformation. Check your data first before any kind of analytical processes. So let's go with that transform data. And shortly we should be having the power query window open on our desktop
screens. So there you go. You can see the completed data set has been successfully uploaded both the stores and orders data sets. Now uh let's quickly check the Data from our data set. So here you can see we have orders date right but I can see 42 682 and 4253 this is not in the form of date correct. So this is the simple uh uh step that we discussed changing the wrong data type. So you can just click on the lower arrow button over here and uh or you can right click and here you have
an option to change the type. Correct. So it is considering it as a whole number which Is wrong. So you can change it to date current. So now we are trying to change the order date from a data type of code number to date data type. So there is some error. If you click on error or if you just navigate on to it, PowerBI should be able to show you what error was it and just in case if it's not working. Okay, we are unable to parse the value provided. So you can just remove that
And try it in a different way. Change type to date and time zone. date. I think this should be helpful. Or if it's not working here, let's quickly check what could be the error. Take time. Okay, now let's try to refresh the data. It is taking a little while. Uh we also have another information that we deal with. We have the first row as row headers. So we should be declaring PowerBI that we also have a row header over here. It's taking a little while to refresh. Meanwhile, let's quickly check into the stores data. And
here we have customer name. So let's try to apply second type proof data transformation which will be like let's say split column. We have uh the complete name of u the customer. Let's try to split it Into first name and second name. So by delimiter and we can give multiple options over here. Leftmost del limiter. Let's say a person has three uh parts of his name. First name, middle name and second name or the last name. So, but we just wanted to split into first and second name. So, we would go with the first left
most limiter and split the data set. Press okay. And it should help us to split the data. Let's name and second name. And we can Also name the columns separately as first name and second name instead of customer name. It's taking a little while than the ideal time, but it's completely all right since considering the 10,000 rows of stores data and 10,000 rows of orders data, it is all right. Not a problem. Now, let's try to take a look at the orders data set. It has successfully changed it. No, it is still showing as an
error. Move was wrong here. Just maybe incomplete. Let's quickly refresh all instead preview and check if can it can help us. So there you go after refreshing the first order date is changed to date type. So what we missed is when you change the type you're supposed to add a step. So what exactly I mean by that? Now we are trying to change the second one as well. So just wait for a while and it will give us a choice if we want To add new step or not. That's when we select the option yes
please add a new step. So what do I mean by steps? So applied steps you can see something over here right. So every alteration, every change or every modification that you're doing, every transformation you're including onto your data will be recorded as a macro. So that can be implemented if you are loading a similar data set for the next batch. Let's say this is 2022 data and if you're trying To analyze the 2023 data and every column is similar and you can just follow these applied steps and the same implementation will be automated. You don't
have to spend time and doing the same process once again. There you go. So, uh this time it has recorded this step and it automatically take has taken a new step over here. Change type step one. Change type step two. If you don't want the step to be added, you can just select the red X mark over here. It will Remove it. And similarly, when you when you go back to the stores data set here, you have the first name and second name split successfully. And uh now uh let's say you want to have unique
uh customer data right in that scenario you can just even remove duplicates from this particular column. Just remove duplicates and you'll just have unique uh data. It's possible that one customer might have uh come here to buy the same uh pro you know buy the same product From different dates or one customer might have uh done a repeated purchase. So it's possible but again if in case if you just wanted to know if there is a way to eliminate duplicate entries then you can do that. So for now, let's not delete the customer ids here
because one customer might have visited the same store multiple times and uh might have purchased a different uh product or might have uh made the same order with multiple products, right? So there is a Possibility for that. Let's not disturb the data set. So I just wanted you to know if there is a way to eliminate the duplication of the data set. Yes, it is. Now let's also check another possibility of data transformation. So let's say you wanted to add a new column and identify or include some mathematical operations. For now let's say I have
sales, quantity, discount and profit. But I don't know what's the rate, right? So here the sale is for $261 and quantity Is two. But I don't know what's the rate of one for so I can include that. You can just select the last column or where you want a new column. Just go to the add new column here and here you can just uh choose the custom column should be somewhere over here. Yeah, this is the custom column. And now if you click on the custom column, you can rename the custom column to rate of
product. And here you can choose the mathematical Operations to be applied on the columns. So sales column insert or double click divided by quantity. Okay. Now it should give you the rate of each product individual product. It is taking some sizable time. It shouldn't take so long. So there you go. You have the rate of product. And now let's say you want to combine multiple data sets. For example, here I have stores data set but my stores data set doesn't have any data related to orders And orders does not have any data related to customers.
Now I want to combine these two. Is there a way? Yes, you can do that. Now let's get back to stores and here go to home option and here you have something called merge queries. So now the stores data set has been selected in the first data set. Here just select the drop-down and select the second data type which is orders. So here you can see stores with the current one which is in the first Place. Now select based on which primary key you want to combine both. So I want to go with customer ID
because both of my data sets do have customer ids. So I'll go with customer ID and uh both data sets will be combined at the end at the last column. PowerBI will show me a new table not a new column. It will give me a table combined with all the columns in one column. You just have to expand the column and select which columns from the second data set you want to include In your overall data set. So let's do that practically. So here you can see I just have tables. If I select the expand
column option over here, you can see I have a lot of uh columns here. So just deselect everything. I do have row ID. I do have a customer ID. What I need is order ID, order date, ship date, shipment mode, and customer name. I do have it. So that's all I need. So just press on okay. And I should have them included in my new data set all Together. It might take a little while. So there you go. We have the new orders ID, order date, ship date, and ship mode added to our data set.
Now just click on close and apply. And your data set is all ready for data analysis. Supply changes, but is taking a little time. So at the end you just have to click on the apply changes and your data will be ready for analysis. So that's exactly how you can perform data Transformation in PowerBI desktop version. So there you go. The result got successfully loaded all over here and you can just drag and drop them onto the visualizations part and you can work on your day. We will learn how to build a star schema using
the data modeling process in PowerBI. PowerBI data modeling is crucial for effectively analyzing and visualizing complex data sets. When data resides in multiple tables or sources, it can create Challenges such as inconsistent relationships, redundant data, and slower query performance. Data modeling resolves this by uniting disperate data sets into coherent structure, enabling efficient data retrieval and streamlined analysis. A well-designed model often employs the star schema, which is a relational database structure that organizes data into a central fact table surrounded by dimension tables. The fact table contains numerical metrics like Sales or profits while dimension tables hold
descriptive attributes like product categories or customer names. The star schema simplifies querying and enhances performance by minimizing redundancy and maintaining clear relationships between old data sets. This structure is particularly advantageous for reporting and business intelligence as it ensures faster processing and more intuitive navigation for users. By implementing a robust data model, PowerBI users can Overcome fragmented data and unlock actionable insights with ease. That said, if these are the type of videos you'd like to watch, then hit that like and subscribe buttons and the bell icon to get notified. Now, we will understand what exactly is
a star schema in PowerBI. So in other terms people also prefer to call this as a snowflake schema in PowerBI because the shape of the schema represents a star shape or a snowflake shape. So basically there will Be one fact table which stores the major part of the data set and there will be some subts or these subordinate tables which uh store the relevant data to that particular fact table. So the fact table maintains many to one relationship with these dimension tables right. So basically it will be interconnected with each and every table in the
particular schema and you can extract information in the way you like. Um this might be a little confusing theoretically but let Me navigate you through the session through a practical demonstration. Let's actually create a data set using generative AI and moving ahead we will extract or we will pull the same data onto the PowerBI platform and then let's look at the star schema practically. Now let's go to the chart JPD or any of the generative AI platforms to create the data set that we are looking at. So this will be my prompt for creating a
star schema. So we will be trying to generate Five different date tables with 2,000 rows each. So we will be creating one fact table which is the sales data. Followed by that we have four dimension tables which are customer data, product data, orders data and shipping data. So this is the prompt that we will be using today. Now let's select the entire prompt. Copy that and get back to chat GPD. And here you'll just paste that prompt and then hit the go. I've already created one such data Table. It would take a little time to
generate the data set. While a chart GPD is working on creating it, let's look at the data that I already have in my inventory. This is the data that we have in inventory. So as for the inventory, the sales data which happens to be our fact table has order ID, price, discount, cost to company. Let me expand this profit and rate right and then the fact table is then connected to the dimension tables which is about the Customer information customer data customer name state city etc and the product details to which category or which product
is being bought by the customer and then we have the orders data and shipping data right so shipping data includes the type of shipment mode chosen by the customer and also the order date and u the ship date let's save this. Now let's have a look at uh the generative AI prompt that we just wrote. So there you go. So Chad GBD has Successfully created us the data set. And here the link is you can just click on this link and you'll get the downloaded data set which looks something similar to this one. Right now
how exactly star schema or snowflake schema works. Now let's say I have this order details here the sales data here. I have order 00 0 1 2 3 4 and 5. Let's say I wanted to track this particular order with OD triple05. So I want to find out the customer who bought that. Now I can get connected with customer data having the order key uh order number as the key in between which is common between both the tables and based on that I can extract the customer details the customer name the customer city the customer
state uh country and the region and now let's say I wanted also to extract the product details what the customer 005 has been buying or on the order number 005 is at right now if I trace down the order details further. I can identify the product ID, product name and product category along with the subcategory of the product name which happens to be tables. All right. And now if I drill down a little more bit then I have uh these many number of traffic leads and orders received to buy 13 quantities of that particular item
called table and they preferred you know kind of same day delivery. So this is how the star schema works. All tables are interlin to each other. Now let's Quickly switch to the PowerBI platform and try to extract or pull the same data set onto the PowerBI platform. And then let's wait for PowerBI to generate us a schema. There you go. Now we are on the PowerBI platform and now the data that we are working with is in the form of Excel. So let's prefer the Excel workbook as our source. Now this is the data the
demo data that we just created. You can just open it and the data will be pulled for you onto the platform. It Might take a little while. Let's wait. So here we have the data ready and we have five tables according to the discussion. One fact table and remaining else are the uh dimension table. So the customer data, orders data, product data, shipping data are the dimension tables and the fact table is sales data you know. uh and then you can just load or transform. As far as I'm concerned, my data is clean and I
don't have to proceed with the transformation. But Just in case if you want to perform some data cleaning and you can go ahead with transformation of data and if you have to look at an exclusive tutorial on data transformation using power query then we have linked it in the description box below and you have a clean reference to it. So far so good. I'll continue with loading the data since I'm confident with my data set. Just load it. And it might take a couple of moments. The data is slowly loading. Let's give It some time.
There you go. The data has been successfully loaded. And here you have the view of your data set. So here we have leads, order ID, quantity, etc. Product details and sales details. All of them that you needed. And if you proceed with this particular table view, you can see the tabular version of the data set. And here lies the most important part of today's discussion, the model view. And if you go here, the PowerBI platform has already created the relationships between your data sets. And if you just rearrange a couple of those schemas, you can
literally make a star pattern out of it. And also if you look at this particular position here you can see that we can establish some new relationships or you can try to edit some existing relationships with the manage relationships option. Now you just have to drag and drop a few of these tables over here so that you can Exactly maintain a star type of pattern. There you go. So that's how you can generally create a star type schema or also known as the snowflake type of schema in PowerBI. Today we will learn how to use
Microsoft Power Query. That said, if these are the type of videos you'd like to watch, then hit that like and subscribe buttons and the bell icon to get notified. Let's get started with the topic which is how to use Microsoft Power Query. So imagine You have a lot of data. It can be an Excel file. It it can be a PDF, SQL database, cloud or anything. If it is data, it's your data. Right? So you have a lot of data. Now you have the source of the data. After that, I'm absolutely sure that the data
is not clean enough to perform some analysis. So you go through another step where you have to clean your data from unwanted rows and columns which doesn't make sense or might have some invalid data. Right? So You need to make sure that your data is completely accurate and has some standards for your analysis. Right? So that's the next step which is clean and transform. After the clean and transform step, you have the next step which is load that particular data to your platform. It could be Excel, it could be some data visualization tool like Tableau,
PowerBI etc. Right? After that you generate some meaningful information of that you Create some pivot tables, pivot charts and uh line graphs etc. Right? So which makes the some sense out of that data you'll understand what what exactly is happening with your business how many leads or how many traffic orders what's right and which is the best performing product which is the worst performing product and you need to take some decisions on your business which can make some impact on your overall profit or revenue right so imagine if you want To do this for a
year if you get one year's data and you do it in a day or two it's fine And if it's on a monthly basis, you get month- on-month data and you want to do it. Okay, it could not be worse. You can spend one or two days and it's fine. But if what if it's week on week, you start to think, right? What if it's a daily task? It's a mundane task, right? You have to do all these steps step by step repetitively and it it doesn't make any you interested anymore In that particular type
of job. And uh the major point of this is you end up spending more time on cleaning the data, transforming, loading it. And the worst part you don't get proper time, specific time which you want to actually dedicate to plan out something and bring up strategies to improve your business, right? So what if you could automate this, right? What if there is someone who could perform all these steps for you in the way you want and you can Actually have some time to produce some pivot tables, pivot charts and then understand what exactly is happening
with your business and find out some critical points where you can improvise and bring up some good revenue. What if there was someone who can perform all these steps for you, clean your data, transform it the way you want, load it to your platform and help you create your pivot charts and web tables and actually save you a lot of time where You can spend all that time understanding your critical points where you need attention. You can fix them and plan some strategies out to bring out some good revenue, right? That exact someone who can
do all these steps for you and automate your data cleaning, data transformation and loading tasks for you is exactly the Microsoft Power Query. So you can use Microsoft Power Query either in Microsoft Excel or Microsoft PowerBI. Right now let's u Kind of generate some data. So uh this ch d d d d d d d d d d d d d d d d d d d d d d d d d d d d d d d d d d d d d d d da be generated using chat GPD and we will not
generate data like which is not small like for a week on week analysis or a daybyday analysis we will create data for years so we will take five consecutive years starting from 2020 to 2024 and we will have some uh data generated using chart GPD and we will automate that data and try to get some Analysis done right let's jump on to chart GPD So before we go to chad GPD I have a prompt written here. So this particular prompt will guide or explain chat GPD what exactly you want right so you're giving some uh
like the excel data right over here we have order date or ID ship mode and customer name customer ID everything right so we have the sales quantity discount profit etc. So in the same format or a similar format if not the same format we are Explaining chart GBD that we need these columns the order ID order date delivery date etc and we want all these files in a downloadable excel file links right so you can also specify this column should have certain certain things and this column can have certain certain things right so we will
copy this prompt and head back to chat GPT and you can Just face the prompt and hit enter. There you go. Now your request has been analyzed and shortly you should be receiving some Downloadable Excel files or some recommendations from CH GPD. I've already performed this process and we have already downloaded some data. So there you you go. You have the data from 2020 to 2024. You can just click on this to download the file. So I've already uh done this job and u I've downloaded the files. So those files are exactly in my downloads
here. So if you go to downloads here we have orders data for 2021, 2022, 2023 and 2024. So this Orders data has just one file which is orders 2020. So we will perform the data cleaning. So first we will specify the source of the data. Then we will perform the data cleaning and transformation process. Then we load this particular Excel 2020 data into Excel. And then we load this Excel data into the visualization tool. We'll use Excel for this. And then generate some pivot tables and pivot charts. We will only do this for one file
which is 2020. Right Now what's the surprising part is this particular file will also get data continuously for the next year 2021 for the next year 2022 for the next year 2023 and the current year 2024 right when we just copy paste or cut copy paste these files into this particular folder the same transformation steps the same data cleaning steps and the same data loading steps and also including the analysis will be done for you in the Excel pivot Table. Right? The process has been just automated, right? So you can see that happening. For that,
let's open a new Excel file. So I have the Excel folder right over here. So a new blank Excel workbook will be opened. And if you go to data here, you have get data. As we said, it can be a file. It can be an Excel file, XML, JSON, PDF. It can be from cloud. It can be from a SQL database. Any any other source if it's data you can work from it. So we will Give a file here right. So just uh uh okay. So so we will give a file here. So select file
and go to the last option which reads as from folder. Right. So go to downloads and select that data. Yeah. Go to downloads and select that data. Remember this particular folder should have only one file according to us which is 2020 data the first year's data right select the folder and open and this should include the data of only one file which is orders 2020 right so if you Have multiple files you can either combine transform and load if you have just one file you can just go with the transformation option over here or you
can load if you are comfortable able with what you have. So I feel there are some minor changes which I would like to make to my data. So I would go with the transform option. Now the power query window will shortly open. Now with the power query window opened, you can select the orders 2022 Excel folder and It will be loaded. Now let's try to click on the content button over here which should load our data. Now you can go with the first file. So here's the preview as we expected. We have the order ID,
order date, delivery date, etc. Let's press on okay. So the data has been successfully looked right now. We have the first column as source name which we will not be needing anymore. And you can just remove that. Order ID is fine. Order date is fine, is it? No. Right. So you can just select both the columns and change the data type. Right? So here you have the option of change the data type where you will change the data type from whole number to date so that you get a date right. So you have that the
first transformation step changing data type has been recorded here. So this is like uh v uh xlvx or macro which will save all the steps and will apply uh to all the uh upcoming files right you have to change the type Of delivery date as well. So we will be finding uh the how many days it took to deliver or if if the delivery got delayed right so we can test that and here if you see uh yes we requested customer ID and customer name so we got customer ID along with the name so we
don't want that we want that in two separate columns we can do that so you can just select the entire column go to the split column option over here select by delimiter so I think it'll Automatically recognize that for us. But there is one loophole. We have a space delimiter space option. So just press okay. And this should be helping us with the customer ID. So let's rename the customer ID to just customer ID and this to a customer name space name. Right? And that's done. Here we have product which is fine. Quantity is fine.
Traffic is fine. Leads is fine. orders and here comes the money part. So we have revenue cost to company. So we don't know the Profit yet, right? So to calculate the profit, what you need to do is subtract revenue from cost of the company and automatically you will get your profit. So in some instances you might get a negative value. Don't worry about that because uh that can be a discount offer or some 50% off 75% off that you regularly sell out your products which are selling in less number so that you can just you
know uh move out the products from the store. So that can be Done and order status. So we have order status as delivered in transit not delivered yet or order cancelled. Right? So we might have to rule out some uh status here, right? So we are calculating profit here. So we don't want the profit for cancelled orders or uh not delivered. Okay. Maybe. Okay. Okay. Not delivered orders. uh this might be considered as the cost of company and revenue because uh it takes some delay to refund the amount to the Customers. So we can just
add a filter to this particular column and uh we will unselect not delivered products and auto cancel products. So not delivered happens when the vendor is not available to deliver the product to the delivery agencies. So in that case uh the order gets automatically cancelled and uh the order will be the money will be refunded to the customer. So we will rule out these. Now the data is clean. Now uh as we thought to calculate the profit Select these two columns first revenue then cost of company because we are subtracting cost of company from revenue
right. So we will go to add to column and standard and here we have some options. So we will subtract that. So we will get the profit here. So now you can name it as profit. Done. So we just move that column to the money part of this particular data set. So profit, cost of company and revenue are the money parts. So we will change data type from just Values to currency. Right? So then that should be converted into the dollar format. Now the first one we don't need this source name. So you can just
rule out this. You can just uh remove this column and there you go. Now you have a clean data. You have the order ID, order date, delivery date. Yeah, we forgot uh the another part of this particular data set transformation where we wanted to find out the number of days it took to deliver the product. Okay, I think we Changed uh our ID. So we don't want this. So the first okay let's change this to the to the whole number as it was okay so order ID fine now we wanted to find out the number
of days it took to deliver the product so we will select the delivery date first and then the order date second go to add column and uh we will select subtract in days and we should have that now let's change the uh subtract subtraction name to days to edible And you can move this column right select the whole column and hold it drag it and you can move this column along with the dates part of this data set where we have the ordered date and delivered date right right over here and you will find how
many days it took to deliver a certain product now um let's recheck once order ID order date delivery date to customer ID, customer name, what product did he or she order and what quantity And uh the traffic which we received to the website, the traffic which we received to the website, the number of leads we got out of those traffic numbers, the number of orders we got out of those uh uh leads and what's the revenue we got out of those orders and what was the cost of the company and what is the profit that
we made out of that order. Right? And we also have the order status. Remember we only considered remember we only considered Two types right? Uh we considered in transit and delivered statuses only. We ruled out cancelled orders and not delivered orders so that we have accurate results of our profit made and revenue made out of this. Right now I'm completely satisfied with my transformation and cleaning of my data set. Now let's go to home and here we have the option of close and load. So we will go with that and this particular data set should
be loaded to the data Visualization where we have selected Excel right. Uh so we have the orders data for 20 correct. Okay, let's not add date here because after all we will have some more data for 2021 2 3 and four, right? So let it be the way it is. And if you just check the filter here, we have only one year. And if you just check the filter here, you will have only one year. And if you just check the filter here, you will have only one year, 2020. And if You go to the
delivery date, which let's say if you have an order placed on December 31st and it got delivered on January 5th or something, so you'll have a little bit of a part of 2021 data which is completely all right, not to be worried about. So the main important thing is order date. So we want the audit dates of 2020. So that is loaded here. Right now you can just select the whole data set over here and uh uh you can add or insert some pivot tables here Right from the selected range and uh let's go with
the default option which creates a new sheet and everything in a new location right here you have it. Now let's say you wanted to make out some um insights from this uh data set. Let's delete the other sheet which we don't want. And let's name it as pivot. Now the products will be loaded to the columns and uh profit will be loaded to values. So you have a data set over here. Sum of profit from beds, chairs, Cupboards, sofa, to you have the pivot table which has sum of profit, bed, chair, cupboard, sofa, tap and
grand total. sum of profits made from these these these and these. Now select this entire data and go to the uh option here. Go to the pivot chart and uh here automatically here you can see that it is you know suggesting or recommending us to go for the bar chart which is a lot more better. Just select okay and you have your pivot chart up Here right and see the grand total here 2248114.15. Now this is only for one years of data. Now what we will do is go back to the downloads folder. Select the
remaining years which is 2021 2 3 and 4. Add that to the file location. So let's quickly go to our folder here and go to downloads. Select the folders. I'm going to cut them from here. Go to orders data and paste them here. Right now, our job is done. If we go back to the data here, Go to home and data. Okay. So, now that we have uh added the data to our folder, go to the orders data and here select the data segment and just select refresh all. So everything is refreshed. Now let's check
the audit date filter. And we will have all the five years from 2020 to 1 2 3 and four. And let's press okay for that. And if you go to the pivot table, you can also see the changes here. Just press refresh all. And there you go. You Have the updated data right over here. Now that saved a lot of time for you. And now that time can be utilized to plan a strategy and focus on the lower parts of your business and improvise them and bring a big change to the revenue. And with that
we have come to an end of this session on how to use Microsoft Power Query. Ever wondered what those people with fancy charts on their computers are doing. They are probably business analyst. So what's a Business analyst? Think of them as the Sherlock Holmes of the business world. They look at loads of information and figure out how to make companies run better. And guess what? It's a pretty cool job right now. Loads of companies are trying to hire these folks. Want to know how much they make? On average, about $85,000 per year. Some of them
even make over $120,000. Not bad, right? But here's the thing. To be really good at this job, you need to Know some special words. It's like learning the secret code of business. And in today's video, we are going to look at the 10 important words every business analyst should know. And by the time we are done, you'll be talking like a pro. So let's start with a little story. Meet John. John works at a company and is always overwhelmed by all the data terms his colleagues use during meetings. So words like KPIs, data warehouse, and
analytics were thrown Around. And Jon often felt lost. He knew understanding these terms was crucial for his career growth. So he decided to dive into learning about business intelligence. Joan started by learning what business intelligence actually means. He discovered that BI involves strategies and technologies that help companies analyze and manage their data. For example, if he worked at Spotify, BI could tell him which songs are getting the most plays when people listen the Most and even predict the next big hit. This realization made John see how powerful BI could be for making informed business decisions.
All right, so let's dive in and let's understand what business intelligence actually is. Imagine you're running a popular ice cream shop and business intelligence is like having a super smart helper that tells you everything about your business. Cool, right? So what exactly is business intelligence? It's all the Tricks and tools companies use to understand their data better. It helps them make smart choices based on facts, not just guesses. For example, let's say you use a tool like PowerBI in your ice cream shop. It might show you which flavors sell the most on holidays, what time
your shop is busiest, and which toppings are most popular with kids. This info helps you make smart decisions like stocking up on popular flavors or hiring extra staff for busy times. Business intelligence is super important because it turns boring numbers into useful information that helps businesses grow and succeed. It's like having X-ray vision for your company. Next, let's talk about data visualization. So, data visualization is the graphical representation of information and data. By using visual elements like charts, graphs, and maps, data visualization tools provide an accessible way to see and understand trends, outliers, and Patterns
in data. For example, a bar chart showing the most popular pizza toppings in a delivery app. This is crucial for presenting data in an easy to understand format. Moving on, we have analytics. So, analytics involves the systematic computational analysis of data. For example, using analytics to understand which times of day are busiest for a pizza delivery app. This term is important because it helps in making sense of large amounts of data And aids in decision making. Next up is KPI that is key performance indicator. So a KPI is a measurable value that demonstrates how effectively
a company is achieving key business objectives. For example, tracking the number of pizzas sold each month as a KPI to measure business performance. KPIs are essential for measuring performance and progress towards strategic goals. Now let's discuss data warehouse. So a data warehouse is a centralized repository For storing large volumes of structured data from multiple sources. For example, storing customer data from different gym branches in one place for analysis. It is used for reporting and data analysis and is a core component of business intelligence. Data warehouses are important for consolidating data from different sources and making
it available for analysis. Next we have database. So database is an organized collection of data generally stored and Accessed electronically from a computer system. For example, a database storing member records for a gym. Databases are foundational for storing and managing data efficiently and are critical for any business intelligence processes. Moving forward, let's introduce data mining. So data mining is the process of discovering patterns and knowledge from large amounts of data. For example, a gym analyzing member attendance to identify the most popular class times. Data mining is important for extracting useful information from large data sets.
Next, let's talk about dashboard. So dashboard is a business intelligence tool that allows users to track key metrics and KPIs. For example, a dashboard for a gym showing daily attendance, most used equipment and membership growth. It provides a visual representation of data and helps in monitoring performance at a glance. Dashboards are crucial for real-time Data monitoring and decision making. Next up is reporting. Reporting refers to the process of organizing data intoformational summaries to monitor how different areas of a business are performing. For example, a monthly financial report, summarizing income and expenses for a gym. Reports
are essential for tracking progress, identifying trends, and making informed decisions. Finally, let's discuss SQL, that is structured query language. So, SQL is a standard programming language for managing and manipulating databases. For example, writing an SQL query to retrieve all gym members who joined last month. It is used to query, insert, update, and modify data. SQL is fundamental for anyone working with databases and business intelligent tools. Remember, whether you are running a multi-million dollar corporation or a neighborhood laminate stand, these BI tools and concepts can help you make Smarter decisions and grow your business. They turn
raw data into valuable insights, helping you understand your customer better, optimize your operations, and stay ahead of the competition. So with that guys, we have come to the end of this session. I hope this video has helped demystify some key business intelligent terms for you. If you found this video helpful, don't forget to give it a thumbs up and subscribe to our channel for more Business and tech insights. If you have questions about DI or want to share your opinion, you can comment down in the comment section below. In today's session, we will learn some
important tips to crack the business analyst interview questions. Now without further delay, let's get started. So in this particular video, we will discuss some of the important tips that will help you to crack the upcoming business analyst interview questions and make you one of The most highly sought after business analyst in 2024 and beyond. That said, if these are the type of videos you'd like to watch, then hit that like and subscribe buttons and the bell icon to get notified. So before you attend any of the business analyst interviews, you need to basically take care
of your groundwork. So few suggestions on that are first one check the job postings. When it comes to checking job postings on platforms like LinkedIn, Indeed and Others, it's essential for aspiring business analysts to stay informed about industry requirements and trends. Regularly monitoring these job postings not only keeps you updated but also helps you tailor your skills and the preparations to align with the current needs of business analysis field. The second suggestion is about research the company. Researching about the company is crucial step in preparing for a business analyst interview. It involves Gathering insightful information
about the company's history, values, mission, and recent achievements. Think of it as decoding the company's DNA, enabling you to demonstrate how your skills and expertise can seamlessly integrate into their unique business environment. The third tip is about understanding their domain knowledge. Delving into the domain knowledge of the company is like unlocking the secret language of their industry. It's about comprehending to Specific challenges, trends and intricacies that define their business landscape. This understanding goes beyond generic industry knowledge and focuses on the nuances that set this particular company apart. Now the next one is analyze if your
skills are transferable. Evaluate the transferability of your skills. Identify how your existing expertise can seamlessly apply to the requirements of business analyst role. This analysis Ensures a smooth transition and highlights your adaptability. Now the next one is align your rumé to their domain request. Align your resume like a customized business proposal. Tailor it to emphasize skills and experiences directly relevant to the business analyst role. This approach captures attention, showcasing your perfect fit for the company's specific needs. Or in some situations, you can also hire a rumé writer to do that for you. Or in Some
situations, you can also hire a professional rum writer to do that for you. Hiring a rum writer streamlines the process. Professionals craft a compelling document ensuring key skills and achievements that stand out. It's an investment in presenting your best self on paper. Now moving ahead we will discuss some of the important technical skills required to become a business analyst. Starting with the CRM tools. CRM tools are customer relationship Management tools are the digital solution and enhance interactions with customers. They centralize customer data track communications and manage relationships efficiently. Popular examples include Salesforce, HubSpot and Zoho.
And these tools are essential for optimizing lead generation, improving customer satisfaction and boosting overall revenue. Moving ahead, we have business intelligence tools. Business intelligence tools like Tableau and PowerBI turn data into actionable insights. They facilitate efficient analysis and visualization aiding marketing analysis in competitor analysis and revenue generation. Moving ahead, we have business acumen. Business acumen is savvy application of business principles. It requires understanding trends, financial literacy, a strategic mindset and much more. For marketing analysis, it's key to interpreting data, spotting opportunities, and contributing To revenue generation. It's like having sharp business institution for informed decision
making. Moving ahead, we have a project management skill. Project management skills ensure efficient planning and execution vital for market analyst, orchestrating successful campaigns and collaborating effectively with stakeholders. A green belt in project management could be beneficial. Moving ahead, we have data analytics fundamentals. Data analytics Fundamentals involve extracting data from data sets like Excel, database, web, etc. It's about interpreting information to make informed decisions, a core skill for marketing analysis and driving effective strategies. Moving ahead, another important tip is about brushing up the fundamentals. Brushing upon fundamentals means refreshing essential skills. It's about staying sharp and
maintaining a solid foundation crucial for marketing analysis to Navigate challenges and drive success. Now moving ahead, we will discuss the experience. Now experience is more about the current roles and responsibilities that are you playing with your current organization. Aligning your current organization roles and responsibilities involves tailoring your current task to match the requirements of the new job. It's about highlighting relevant skills and experiences to show a seamless transition demonstrating that you're a Perfect fit for the desired position. Now, moving ahead, we have soft skills. And in soft skills, the first one that we are going
to discuss is about the dress code. Professional dressing is about presenting a polished and appropriate image. It involves choosing attire that aligns with the workplace culture, reflecting a combination of competence and respect. The next step is maintaining a confident smile while you're answering the questions. Smile Confidently for a positive impact. Confidence in delivery enhances your message. Lastly, in soft skills, we have answering language. Use corporate language. Utilizing corporate language for enhanced connection and reliability and it ensures clear communication and fosters a professional rapper. Now moving ahead we will also face some of the critical questions
which might be different from your skill set. Questions like why should I hire you? Now these Are some of the important questions which doesn't have to do anything with your current roles and responsibilities skill set that you have or technical mindset. It is completely about your mentality. How you approach to work? How you approach to your team how you approach to your responsibilities. So the recruiter wants to know how you think about this particular question. So give a wise answer. You can tell him something like you can hire me for my Strong analytical skills, experience
in competitor analysis and success in lead generation. I am confident in datacentric approach that will contribute positively to the company's growth. Thank you for considering my application. So basically you're highlighting your strongest points and also you're giving a subtle answer why they should hire you. Now proceeding ahead we have the second question. So this question is really common. They Might ask you for the reason for leaving your current job. So give them some bright and brilliant answers. So you can say I sought new opportunities to further apply and expand my skills aligning with my career
goals. I believe this role is a better fit for my expertise and aspirations. Now the last question is what are your key strengths and weaknesses? So you can say my key strengths lie in strategic thinking, data analysis and effective Communication. I excel in competitor analysis and digital marketing. As for the weaknesses, I'm continuously refining time management to enhance efficiency. Now apart from this, you can also cross question your interviewer about your agenda. Agenda that covers some critical questions that will impress your interviewer. So some of those questions are as follows. You can ask your interviewer
that how can the current company help me meet my career Objectives? Questions like what new skills will I get to learn when I join the company? And questions also like what does a typical day look like with your new organization or if you have already joined, what does it look like a normal day in your current organization. Right? So questions like these can impress your interviewer and it will explain how enthusiastic are you about your new role and how enthusiastic are you about your new work environment. Right? And with that we have come to an
end of this session on tips to crack business analyst interview questions. And if you have any queries regarding any of the topics covered in this session or if you require the resources that we used in this session then please feel free to let us in the comment section below and our team of experts will be happy to resolve all your queries at the end. Today we will learn about the PowerBI interview questions And answers. So PowerBI is one of the most trending BI tool in the business industry today and most of the data analysts are
supposed to have a comfortable understanding of PowerBI BI tool. So if you are about to attend an interview for a data analyst or a PowerBI developer, a small set of PowerBI interview questions and answers are found to be the most frequently asked questions. So those important questions will be dealt in this Particular session today. Firstly, the first question is what are the different types of filters in PowerBI reports? So if you are an experienced candidate or a beginner, these type of interview questions are the ones which you're definitely going to face. So coming to the
answer, what are the types of filters you'll be facing in PowerBI reports? So there are three main filters available in PowerBI which are visual level filters, page level filters and Report level filters which are almost self-explanatory. So if you want to be someone who stands out out of the group just mention a couple of more like slices which can be also which can also act some type of filters there right so visual filters page filters and report level filters are the three main types and if you want to add onto it you can also add
in some slices right now let's proceed to the next question explain tax in power so what exactly is The meaning of tax And to improvise your question uh or to improvise your answer or if you want to outperform you can also explain with some of the explanations or implementation of DAX in your nearest examples like if you are working if you're a beginner or if you're a fresher if you're working on a portfolio then you can give the example of your DAX implementation on that portfolio or if you're an experienced candidate just explain your latest
Implementation of DAX in your previous projects. So the answer for this question is DAX is basically the full form for data analysis expressions and is a formula language used in PowerBI, Power Pivot and SQL. DAX includes functions, operations and constants that can be used as a formula expression to calculate and written one or more values. So DAX just understand it in this way, right? So if you are about to perform an analysis or if you are about To extract some insights for example you own a superto right and you wanted okay let's assume that you
own a supertore and you have five regions north southoutheast west and central and you are u providing um products from uh few categories let's say it mobile phones desktops everything and Now you wanted to identify one zone which is giving you highest sales in mobile phones and you wanted to identify one zone which is giving you least sales in um mobile Phones. In such scenarios, you can just write up a formula equals to um mobile phones product and m uh you know products uh minus uh products which are less than a number specific number. You
you actually wanted to identify products less than um 100 uh mobile phones, right? You just want to know if there is any region which is uh selling less than 100 mobile phones, then you can extract that. So the expression what you're writing there is nothing but a DAX Expression. So apart from this example, you can also put up some complex examples. If you're an experienced candidate or if you're a fresher, then you can come up with some DAX expressions that you build on your portfolio projects. Moving ahead, we'll discuss the third question. How does PowerBI
integrate with other tools and services? So there might be a situation where you might have to get connected to different sources of data, right? So Let's say you wanted to import Excel data as well as some service like a cloud service. You want to extract data from a cloud service as well. So how can you do that? And if you want to do that, can you do it? Right? So that's the question. So basically this is a kind of question which comes up from two different zones. Can you integrate or can you connect to two
different sources of data or in case if you can connect how to connect? Right? So it's it's like Is it possible to connect? This is also a question to beginners to test your knowledge on the fundamental knowledge in PowerBI. They want to know if you can connect data from two different zones. It's not like two Excel sheets. They want to know from two different platforms altogether. Let's say I have data in Azure. Let's say I have data in SQL. So can I connect them both? Yes, you can do that. So the answer to this question
is PowerBI offers extensive Integration capabilities with various Microsoft products like Azure, Excel, SQL and some third party services as well like a website, any application or uh database, SQL databases, Excel spreadsheets and other cloud services like GCP, AWS, etc. So that's possible. So you just have to let's go for a demonstration on this. Not a complete demonstration. Let's cancel this. So here if you just it's it's just like a overview how to connect. So here if you See we have Excel workbook semantic models data flows data was SQL server web O data feed blank query
and if your uh source is falling different from these available ones you just click on more and select any one of these options right here. Correct. So uh with that let's get back to the next question. What are the differences between PowerBI desktop and PowerBI service? So the terms are self-explanatory. If you're an experienced candidate then you can Define the answer right away. But if you're a beginner especially if you're a fresher then you must be a little cautious here. So if you are building your portfolio, if you are learning the fundamentals of PowerBI, then
you are doing it using PowerBI desktop right. But if you are a professional or an experienced candidate, then you are developing the interactive dashboards, you are developing the interactive charts and graphs and everything and you Are providing that visual only visual only the dashboard to your end user who is your client. So in that scenario your client will be given the PowerBI service model right. So they will be having a service access only just to watch the visuals. So the answer for this question is PowerBI desktop is a free application. So it's not completely uh
free. So there are in situ there are uh situations where you might have to deal with some huge data or you have to deal With some uh uh parameters like security everything in those scenarios you might have to go with the licensed version. So for a learning purpose it is free application installed on a local system that lets you connect to transform visualize modularize your data and few more uh you know implications that you can do and on the other hand powerb service is a cloud-based service that allows you to share as discussed only to
share and collaborate on uh and Distribute your PowerBI reports and uh dashboards with others within your organization. your client is partially a part of your organization because he has some business to do with your organization. So basically understand it in this way. PowerBI desktop is something which allows you to build it will give you the blocks you build the visualization and then you give a screenshot or you give a picture of that particular build to the end user. He can Have a visual on it how it is performing. He doesn't have to worry about how
it is working in the background. He doesn't have to worry about how big or how um large is your data set, how it is extracting, what API is it using, what's the he doesn't care about it. All he cares about is the vision. How is his business doing so? So in that scenario, you just want to give a picture that's when you use the PowerB service. Now let's go with the next Question. How do you handle security in PowerBI? So this is a question given to the experienced candidates. So you can think about it about
the access role wise access. Let's say you are the leadership team and next is the manager. Next is the uh individual contributor. Correct? So here the full access the full visuals of your business. How your business is going on? How are the leads? how are the revenue or how is the RPL how's the traffic everything is visible To the uh leadership or the manager correct but when it comes to the IC or individual contributor who is just working on traffic he will have access only to traffic not the leads not the revenue generated not the
uh RPL and everything because he has nothing to do with it and that is something which you can't reveal to an IC for data security purposes. So it you can also consider this in the way of inheritance right. So uh you can divide It based on the level of the employee right. So level wise you can provide the access to your employee to your manager uh if you're a developer let's say so based on the employee level based on the manager level based on the leadership team you can give separate access to separate candidate. So
the answer for this question is PowerBI provides role level security RLS which allows you to control access. So you're basically controlling the access to the data for Different users within your organization. You can define rules on the data set that filter the data based on the user role. Additionally integrates with Microsoft Azure Active Directory to provide authentication and authorization. Now the sixth question, what is a content pack in PowerBI? Content packs are type of package that includes a related set of dashboard, report and data elements. They used to Share insights, reports and data sets with
colleagues and organization within a broader audience. PowerBI also offers organizational content packs which allows you users to quickly connect and analyze data from popular business applications. So basically content pack you can imagine content pack as a set of charts graphs or a complete dashboard or data set elements. So when you integrate all those inside a bubble that particular bubble can be considered as a Content pack. Now moving ahead we have the seventh question. Describe a scenario where you improvised performance of a PowerBI report. So let's say this is uh so basically before we answer this
question this question is for an experienced candidate not for an intermediate or not for a beginner level right. So if you are someone who has 3 to four years of experience that is when this kind of questions are shoot. So explain a scenario where you improved Performance of a PowerBI report. So there are certain scenarios where let's say you're working with a uh business data Amazon sales data. So you have uh Amazon sales data or any other data for a beginner book. Okay. Let's say some XY Z company. So you're working with XY XY Z
company and you have a sales data from a past couple of years and uh your client asked you key uh last year this particular dashboard was working fine but down the line when we entered the New quarter or new annual year I can see there is some discrepancy or I can see there is some delay for the PowerBI dashboard to load. Now there can be multiple reasons for this. The first reason could be the hardware setup or uh uh maybe the data is increasing day by day. So the hardware is not supporting. So that could
be one reason and provided if uh there are some visuals which are already created visualizations or graphs or dashboards and we are not most mostly Using them. So those can be one of the reasons. So in these scenarios you can just give some uh explanations which you already faced in your current organization. For example like you can say uh the client is not willing to uh scale up the hardware like the the hardware which stores the data or something like that. He's not ready to scale up the hardware but he wants to use the same
hardware but he wants you to increase the speed. In this scenarios You can eliminate the data which is not currently in use. Let's say you're using 2020, 21, 22, 23, and 24 data. Now, all you focus on is the yearon-year growth. So, you just need 2022, 2023, and 2024, not 21 and 20, right? You can erase that data from your existing hardware or service. This will ease up the load on PowerBI and automatically there should be an increase in speed. Another one, if you have already created some visualizations which you're not Currently using. Let's say
you already created two to three dashboards and you rely on one dashboard alone not the rest of those two. You can delete those visualizations. So something like that which can help you. But to consider this question as an assignment I would like the viewers who are an experienced data analyst to answer the question in your way possible so that the viewers can benefit from multiple answers. So you can write down few scenarios where you Face this problem and how you solved it. It can be a oneline answer. It can be a points which you can
mention right a set of points which you can mention that helped you to improve the performance of your PowerBI end report. So the answer for this question which I have is in one scenario I improved the performance by optimizing data model reducing the number of unnecessary columns and rows and data sets and utilizing summary tables to decrease the data volume that Needed to be processed. I also replaced complex calculations in the report with simpler ones which uh were possible and used direct query mode for realtime data requirements to speed up a report loading times. So
this can be a good answer. You can also add up some answers from your end as well to benefit the viewers. Now let's go with question number eight. Explain the difference between a measure and a calculated field. So this can be an important Question for freshers especially right so me personally I've asked this question to the candidates many times so the answer could be a little tricky right so most oftenly the question can be answered what's the difference between measure and a calculated column or measure and a calculated field or measure and dimension right so
the answer should be like this just pay attention patention to what the answer you know interviewer is asking you and Answer wisely. If he's asking you the difference between measure and dimension it's clear self-explanatory. So something like measure is something which can be measured. The number is a measure. Let's say the salary is a measure. The average of uh marks obtained is a measure. Marks obtained in a specific subject is a measure. Rate of interest is a measure. Traffic is a measure. Right? So when it comes to dimensions something like a character Data type maybe
and the name of the store and let's say we didn't have we we had a various regions in the previous question north south east west central this particular data which cannot be measured is called as a dimension. Now for this question here what's the difference between measure and a calculated column. So calculated column is something which you generate out of a calculated field right whenever you try to let's say you have the quantity and You have the price but you don't know rate of each product correct in such scenarios you can write up a calculated
field and build a new column called rate of product that particular column is a calculated column which is newly generated after writing a calculated field. So the answer for this question is calculated column creates a new column based on existing data using formula. It adds to your new you know it's add to your existing table as a new Column as we discussed. Measure creates a dynamic calculation that summarizes your data. Measures are used in visualizations to analyze trends or answer specific questions. Now with this let's move on to the next question. Describe the process of
publishing a PowerBI report to service. Now this can be an answer for both. I mean this can be a question for both freshers as well as experienced. So u now you can give it a try. You can give me the answer for This. You can write down your answers in the comment section below and u everybody has their own way of answering this question. But as simple as that let's keep it normal. Now in PowerBI desktop go to publish and choose powerbs service. Select the workspace to publish and configure settings like sharing access. Click publish
to upload your report to PowerBI cloud service. So this is the answer given by most of the experienced candidates. But when it Comes to pressures, it's okay if you answer in this way. Create multiple charts, create multiple graphs, understand the requirement of the end user, build the require required charts and graphs, integrate all those charts and graphs to the dashboard. It's also a good way to tell it. But if you're an experienced candidate or if you're a beginner, just try to include these points as well. Just go take a step ahead and go a step
further and answer Include these points in your answer. Go to publish, select PowerBI service, select workspace. Right? So these will enhance your answer and make you stand out from the other crowd. Now let's continue with the 10th question. How would you handle missing data in PowerBI? So this can be answered for both. You know, this can be asked for both beginners as well as u experienced candidates. So it's obvious if you're a data analyst, it's obvious that your Data will not have complete uh completely filled rows and completely filled columns. Right? There is a possibility
that you might have blank rows. There is a possibility that you might have blank cells. There is a possibility that you might have irrelevant data. There is a possibility where it can be a typing mistake and you might insert special symbols in place of numbers and uh special operators in place of alphabets everything is Possible. So in such scenarios how could you handle right? So there are multiple approaches. So let's say if you are going through some uh column which you feel is not mandatory to create your report you can ignore those uh cells and
you can just delete it and uh in case of there is um a discrepancy in your existing data and you find that is important then you can always have a way to go back to the source who provided you the report or who provided you the Data as to complete them as to give them them the right number or in few scenarios. You can also if you don't want to eliminate any kind of rows and columns, you can also have an option of taking an aggregate value in it. Maybe the minimum value, maybe the maximum
value, may be an average value in that particular missing cell. So there are multiple possibilities and if you feel like adding those answers, you can also feel free to add them so that everyone Is benefited. So uh my kind of answer is there are several ways to handle missing data. filtering where you can exclude simply that's the first one you can just ignore those and exclude those missing values replace missing values with some aggregate values custom logic create a tax formula to address missing values based on specific scenarios now let's move ahead with the next
question how do you connect multiple data sources to a powerba report so the Answer is powerbi allows connecting various data sources you can import directly use live connections or create composite model to improve and combine data from different sources. So this is not just the PowerBI dashboard you're here. I mean it's not just the PowerBI desktop. You're not just importing data. It's related to the real time dashboard report. So if you have a report and you are ought to connect some external source some realtime streaming data you Can do that using some live connections and
APIs. Now let's move with the next question. What are data flows in PowerBI? So the answer to this question is data flows in PowerBI are cloud-based preparation and management tools that allows users to ingest, transform, integrate and enrich big data from various sources. They are designed to simplify data preparation and enable scalable data integration and storage across multiple PowerBI Projects. Now the 13th question, how can you handle data updates in PowerBI? So this is also one of the simplest but also the trickiest question. Let's say you already connected. Let's say let's we are dealing with
Excel data and your client provided you the Excel data and you already created a dashboard out of it and out of your knowledge you don't know right uh you might not know what happened after they share you the data and the client gets to know there there Is some discrepancy or there is some irrelevant data or there is some missing value in the data provided to and they added and you missed it. Correct. In those scenarios, how can you handle data updates? Right? You can either refresh. There are multiple answers for that. So, let's go
over the answer. So, data updates in PowerBI can be handled through scheduled refreshes or realtime streaming data sets. Schedule refreshes. So, let's say you provide some 6 day or 1 week or 1 day based on requirement. You provide a scheduled refresh and it will uh act upon it and it will refresh its source every single time. It's basically refreshes the dashboard you created. It will extract the data from the source and it will um create a new report out of it. So schedule refreshes can be configured in PowerBI service allowing data to be updated at
specified intervals. Realtime streaming data sets can be used for dashboard tiles that Require continuous updates. Now the 14th question on our list. Can you explain the concept of a slicer in PowerBI? Right? So those who already are comfortable with the Excel, they might know the answer for this. So it's completely similar, right? So let's discuss the answer. A slicer is a PowerBI visualization used to filter the data. Basically, it's nothing different from filter, right? It's just an option like a drop-down that you create in Excel using the data validation. That's the same inpi as well
as Excel. You just have an a slicer option where you can provide your selection and it will give you an end report. So basically it's it's a filter. So it provides a way for users to filter the data that is displayed in other visualization on a report page. Slicers offer a more interactive and intuitive way to make selections and see the data that changes dynamically as selections are made. Let's say you have class 10, class 9, class 8 and you wanted to just check the results of class 8. You just select the option of class
8 on the slicer and you have the report. So that's how it basically works. Now the last question for today's session, what is direct query in PowerBI? So the answer for this question is direct query is a connection type in PowerBI that allows you to directly connect to a supported data source enabling realtime reporting Capabilities without importing or copying a data into PowerBI while it offers upto-date data. It also limits certain functionalities and might impact performance based on the query load on the source system. So with that we have finished the top 15 PowerBI interview
questions and answers that are frequently asked in a data analyst interview provided for both freshers as well as experienced. Should you fail that or be missed out on any of the Important questions that you might have faced already faced in your previous interviews, please feel free to let us know in the comment section below and so we can have them answered or you can also provide us the answers so that it can benefit both of us and also the viewers. Now having said that we have come to the conclusion of this session. Now that's wrap
on a PowerBI full course. If you have any doubts or questions you can ask them in the Comment section below. Our team of experts will reply you as soon as possible. Thank you and keep learning with SimplyLearn. Staying ahead in your career requires continuous learning and upskilling. Whether you're a student aiming to learn today's top skills or a working professional looking to advance your career, we've got you covered. Explore our impressive catalog of certification programs in cuttingedge domains including data science, cloud Computing, cyber security, AI, machine learning or digital marketing. Designed in collaboration with leading
universities and top corporations and delivered by industry experts. Choose any of our programs and set yourself on the path to career success. Click the link in the description to know more. Hi there. If you like this video, subscribe to the SimplyLearn YouTube channel and click here to watch similar videos. To nerd up and get certified, Click here.