Blog – Page 2 – pivotalstats

The process of creating a dashboard in Power BI consist of following steps: Requirement Gathering Data Collection Data Transformation & Modelling Data Visualization Blueprint Dashboard Layout & Design Adding Interactivity & Navigation Testing Sharing Maintenance & Routine Refresh Now lets see all of these steps in detail and develop a Power BI dashboard from scratch. For this exercise we will be using publicly available Healthcare data about the Patient Waiting List. You can download the resources used in this entire process from the below link: Download #1 Requirement Gathering Identify Stakeholders - Determine primary stakeholder and establish a point of contact who might be the domain experts or leaders who will eventually use the dashboard Understand Business Objectives - Through meetings and calls with stakeholders you should get an outline of Goals from the entire endeavor. Asking open ended questions will help you gain more insights to understand the data and how this dashboard will help achieve a specific business goal. High Level Data Study - A high level overview of data is required before you initiate discussions around scope, metrics and other granular topics. So here we will try to understand the data in terms of: Data Sources Column Description Data Type Volume and frequency Data Quality - Missing Values or Anomalies Define Scope - This is the perfect stage to discuss Key Metrics, KPIs & Deployment Timelines. Document the calculations, time frames & scope which will help in setting the expectations and avoiding any future disagreements. Also as a best practice remember to keep a 20% buffer while finalizing deadlines because it's always better to over deliver after a standard commitment than to underdeliver after an extraordinary delivery pitch. What is the problem statement for this exercise? Overall Objective Track current status of patient waiting list Analyze historical monthly trend of waiting list in Inpatient & Outpatient categories Detailed specialty level & age profile analysis Data Scope 2018 - 2021 Metrics Average & Median Waiting List Current Total Wait List View Summary Page Detailed Page for Granular Analysis #2 Data Collection There are over 200 different data connectors available to collect data inside power bi, but below are the most widely used connectors: Excel / CSV Folder Connection SQL Server or Any database Power BI services Cloud Platforms - Azure, AWS, GCP etc. ERP Systems - Salesforce, SAP etc. Sharepoint Web or JSON This is the stage where you decide the source of your data. This step is very important because this will also define how you are going to refresh the dashboard after deploying the solution. For today’s example we are going to use a central folder which will host all the files required for the dashboard refresh process. I’m going to download the zip file from the above download button and extract the file inside a new folder on my desktop. This will act as my data repository going forward. How to import data into Power BI using a folder connector? Open Power BI desktop and click on GET DATA and select FOLDER option. Click Connect button at the bottom right Now browse the folder path where we have stored Inpatient data and press OK The next prompt will show you the files available in the folder path. Then click on Combine button and select Combine & Load Next popup window will show you a preview of the data. If everything looks good then click on Ok Now repeat these 4 steps again for loading the Outpatient data into power bi. #3 Data Transformation Data transformation is a process of changing the structure of your data or applying additional steps which will clean or process your data for final usage. We do these transformations in the Power Query Editor which is inbuilt into Power BI. To get to the Editor, we need to follow below steps: Go to the Report View Click on the drop down on the Transform data Icon You will see 3 Options, select the Transform Data option Now for our purposes we will mainly see below steps: Renaming Columns Rearranging Columns Appending 2 Tables There are many other transformation steps which you can apply like Pivot/Unpivot, Merge, Filtering etc. but for now we only need the above 3 steps. Let's go! Renaming Columns While studying the data, I noticed that the Specialty column in Inpatient data is named as Specialty_Name, whereas in Outpatient it is named as just Specialty. So we will rename the Outpatient column to match with the Inpatient data. Make sure to name it exactly the same, otherwise it will create an issue in following steps. Rearranging Columns We will now rearrange our columns of the outpatient so that it matches with inpatient. You can just left click on the column header and drag it to the required position. Now while rearranging, I noticed that we have an additional column in Inpatient i.e. Case_Type which is missing from Outpatient. So lets create one additional column in Outpatient table called Case_Type by Go to Add Columns Click on Custom Columns Name the column as Case_Type Enter the formula ="Outpatient" Remember to place this new column at the same position as inpatients Appending Tables Now that both our tables have the same column structure, we can safely append them together. To do that come to Home tab and click on "Append Queries" button and click on "Append Queries as New". Select 1st table as inpatient and 2nd table as outpatient. Rename this new table as "All_Data". Finally click on "Close & Apply". Data Modelling Data modelling is a way to create relationship with one table to another, so that we can fetch valuable information from them in our reporting layer. Lets jump into the Data Modelling View, which is located at the left hand panel on Power BI. We will be using All_Data from now onwards, so we can safely hide inpatient and outpatient data. We can also stop it from loading into the data model by disabling it from the power query editor. Just right click on the table name in power query and uncheck Enable Load. Now since specialty name is one of the key attributes that we are looking in this project, lets focus on that column now. As you have seen in the data, we have a huge number of specialty available and using all of them in our report layer directly will create a clutter in our visualization. A better approach would be to distribute them in buckets. So to do this I have created a specialty mapping file which you will find in the downloaded resources. Lets import that file in power bi to create the relationship with All_Data. Once you import this file, Power BI should auto detect relationship and connect both the tables. However if it does not then you can do it manually by following below steps: Go to the model view Click on Specialty_Name column in All_Data Drag this column over the Specialty column in Mapping table Now you should see a line connecting both the tables and an arrow pointing towards All_Data from Mapping table. This means that we have created a relationship between both the tables. The arrow signifies the filter context and tells you that now you can use columns from Mapping table to filter data in All_Data. #4 Visualization Blueprint For this exercise, I have already created a blueprint, however in live scenarios you will sit down with your team and create a wireframe of the required dashboard. You will then get this approved from the end stakeholder before starting your development activities. #5 Dashboard Layout & Design Before you start the designing process, I usually enable below two properties from the View tab. This helps me place and align my visuals in a uniform manner. Gridlines Snap to Grid