Approaches for maximizing the use of Excel with Power BI

Due to its ability to spot trends and save time, Excel is great for organizing data. It'll make it easier for you to see your data. Using a variety of graphs, charts, and illustrations, you may present your data to stakeholders and clearly convey your point of view. More current versions allow for creating data models using "M" Language and connecting to numerous data sources. Therefore, the topic of why we need Power BI might come up.

Power BI can connect to a wide range of resources, including flat files, the cloud, relational databases, NoSQL databases, data warehouses, and big data technologies. Power BI's unique structure (both a desktop application and a web service) makes it reliable, easy to use, quick, and suitable for real-time analytics. It makes advantage of the robust DAX language for it. It also functions nicely on mobile devices.

For decades, Excel has been the only tool that can be used to create spreadsheets, but it is unrivaled for all types of tabular reporting. Aside from making graphs and charts, you may perform extensive data modeling. However, its functionality ends at that point, and Power BI will now handle all upcoming tasks.

Power BI processes data more quickly than Excel because it is a tool for business intelligence (BI) and data visualization. As opposed to Excel, it can manage a lot.

Power BI dashboards have a higher level of interactivity and allow for sharing with a large number of users.

You will discover the following ways that Excel and Power BI connect through Power BI Service in this post:

Analyze in Excel:

You can connect Excel and the Power BI Service using this method. Browse the Power BI Service and choose a report, dashboard, or data set to analyze using Excel. Click the three dots to select the Analyze in Excel option. When you use Excel in this manner, a file will be created on SharePoint's backend. One more thing: the file you produced will update every time you update your data in Power BI Desktop and publish it to the Power BI Service.

Moreover, data from a graphic or tile can be exported. The format in which you choose to export your data is up to you. It's good to know that Power BI currently offers 2 options for exporting data from a visual or tile: CSV and EXCEL. Excel is only capable of storing up to 150,000 rows. The maximum number of rows that CSV can process is 30,000.

Excel can still be used to evaluate data in one more method. This method of using Excel to analyze data functions in the exact same way as the first method. To do that, select Analyze in Excel from the export menu in the top ribbon. It will launch the Excel backend provided by SharePoint, allowing you to examine your data.

Wow! Take a look at how easy it is to connect Excel to the Power BI Service. See the video below to find out more.

Connect from Excel to Power BI Service: Additionally, you can link the Power BI Dataset from Excel to the Power BI Service in order to retrieve data. To do this, click Get Data on the Data ribbon and then select Power BI from the Power Platform menu. Check out the video below. You can also upload your excel document to the Power BI Service. Your data can also be uploaded to the Power BI Service using the Excel publish method.

By using Excel's publish option, you may also upload your data to the Power BI Service. The first option is to use "Upload your workbook to Power BI," and the second is to "Export workbook data to Power BI." In order to do that, open your excel file, select the publish option from the file menu, and then select "Publish to Power BI" from the list of options.

This is how we can leverage the use of Excel & Power BI and do more impactful analysis.

PDF file link: Approaches for maximizing the use of Excel with Power BI

Health Care Analytics Case Study

Demands to cut costs, improve coordination and outcomes, deliver more with less, and be more patient-centric present challenges for healthcare organizations around the world. However, a growing body of evidence also points to the fact that ingrained inefficiencies and poor clinical outcomes are contributing to the industry's growing problems. These organizations can use "big data" to generate insightful information, establish their future direction, enhance results, and shorten time to value by developing analytics competency. In this case study, I'll discuss the issues I encountered and how I resolved them.

Project Background

Currently, healthcare encompasses more than just physicians and nurses. Healthcare includes medical facilities, medical devices, clinical trials, outsourcing, telemedicine, medical tourism, health insurance, and medical equipment. In a well-functioning hospital management workflow, many important decisions must be made, and they should be done so quickly and effectively. The most important part of our society is its healthcare system, but many medical professionals find it difficult to treat patients effectively. It is challenging to put it into practice today without a dedicated hospital management system. These changes in the healthcare industry have led to a need for a healthcare management system.

While spending literally billions of dollars more than necessary on resources that aren't being effectively utilized, healthcare organizations fall short of important objectives along the treatment continuum. By keeping an eye on them and working to improve the patient experience, these key performance indicators (KPIs) can be improved. KPIs for the healthcare industry can compile and clearly convert vital patient-related data into a dashboard. Assisting you in keeping your attention on giving care, which is where it is most required, they can make complex decisions easier.

I've included some useful KPIs below to help the healthcare organization expand more quickly:

• Number of Patient Rooms in use at a single time: Reveals how efficiently space is used to care for patients and helps assess whether the institution needs more or less space. Take into account that this is an occupancy rate like a hotel.
• Patient Wait Times by Process Step: The patient wait time in the hospital's emergency room is the period of time a patient must wait before receiving a diagnosis or treatment. Long wait times are perceived as a barrier to receiving care, which can be aggravating for both the institution and the patient.
• Patient Discharge Type: This KPI will reveal the condition of the patients being discharged. For instance, do they receive their discharge at their own request or as directed by a medical practitioner?
• Patient Recall Time: It will display how frequently a patient enters a hospital. For instance, increasing the frequency with which your patients visit you—from once a year to twice—has the same impact as doubling your gross earnings each consultation.
• Patient Staying Information: By providing data on how long patients stay in hospitals, this metric aids in the forecasting of revenues in the medical industry. It will give an overview of the resources that are being used by the patient.
• Type of Admissions: It will reveal the state in which a patient was admitted. For instance, if the patient is in need of treatment right away due to an emergency situation. Knowing which conditions patients are admitted for the most often would help us allocate the resources.
• Overall Patient Satisfaction: The aspect that matters most, in the end, is patient satisfaction. The organization's services need to be extensively assessed and restarted if satisfaction is low. Happy patients are more likely to refer their care to others, which contributes to the waiting list remaining full.
• Severity of Illness: You must be aware of their illness state in order to provide superior amenities. This will demonstrate how effectively you are offering the services. A patient had quality care if they were admitted with a minor condition and were released with a moderate condition.
• Bed Or Room Turnover: Shows how quickly patients enter and leave the hospital. The effectiveness of the institution is impacted while evaluating patient satisfaction. You might want to closely correlate this one to readmittance rates to make sure that no sick people are able to leave the facility.
• Gross Profit: The gross profit will demonstrate whether we going to make a profit or not. This data will help us to create our business plan. It also enables hospitals to make improvements in areas that are hurting their bottom line.

In addition to the KPIs described above, I make sure that my report includes more critical metrics to help the business decision-making process go smoothly. For instance, if you want to accelerate the expansion of your company, as an administrator, you must offer more facilities based on gender. How is that even possible? If you know how many patients are admitted based on their gender, this will undoubtedly aid you in making the best choice. Regarding the same business objective, I have created a different KPI where I want the organization to decide how to handle resource-related difficulties.

Come along with me as I am going to develop the report and will make sure the KPIs are included. In the following steps, I’ll outline how I created the report.

Data Preparation:

Data is absolutely necessary for analysis. Your worst dread may come true if the data is not sufficiently clean to deal with. Unfortunately, I did not clean the datasets I utilized. The following list includes the data sources I utilized to create this case study:

Dimension data:

Patient Data: This is all about the patient data, which contains demographic data about patients as well as other patient-related data. Here, I’ve made a few adjustments in accordance with my needs. For instance, I combined the patient’s first and last names to create a new calculated column called Full Name because I just require the patient’s whole name.

City Data: This data source includes the name of the city and a predefined ID. The Healthcare dataset is where this dataset is taken from.

Calendar Data: This is a calculated table developed to cease the time series analysis. I took the date range from the Healthcare data, and it includes the date, week, month, quarter, and year-associated information.

Fact data:

HealthCare Data:

Administrative information is included in this dataset, including the services the patient used and related information. For instance, the length of a patient’s stay and the state in which they are admitted. I have made a number of modifications here. The majority of the data types in the column weren’t in the right format. I had to modify them to the proper format as a result. For instance, a data format with columns for Waiting Time, Cost, Available Bed, etc. Missing values and duplicate values are problems I have dealt with.

Data Modeling:

In this case study, I’m going to use Power BI both for data odelling and reporting. According to Microsoft, The process of analyzing and defining all the many types of data your company creates and collects, as well as the connections among those data points, is known as data odelling. Data odelling techniques produce visual representations of the data as it is used in your company, and the process itself is an effort to comprehend and specify your data needs.

We must link the data sources in order to obtain an accurate result if we are to attain the desired outcome. I choose Start Schema after studying the facts and how they related to one another. Relational data warehouses frequently utilize star schema, an established odelling technique. This mandates that modelers categorize their model tables as either fact or dimension. Dimension tables often have fewer rows than other types of tables. Contrarily, fact tables can have a very large number of rows and can expand over time.

The model I created using the Star Schema for the case study is shown here.

Report Building:

The intuitive depiction of information provided by visualization improves decision-making processes. Choice-makers can more accurately assess the overall picture and add diverse viewpoints to a crucial decision by offering a visual representation of the facts. It can aid in improving task planning and execution and helping business leaders better comprehend their data. Making better decisions more quickly is a benefit of data visualization for leaders.

We will therefore perform the visualization during this phase. I researched the relationship and the best approach to visualize the information based on the criteria. I created a four-page report for administrative purposes that would display all KPI data and satisfy all business criteria. Below are the pages along with brief descriptions:

Home Page:

You can view the executive summary of a few things on this page. First things first: you will learn about patient growth year over year from the very first depiction. You can use this in your planning section. The bed capacity may then be quickly verified branch-wise. Depending on the departments you have, you can also request information on bed capacity. You can glance at the left bottom chart to learn more about the revenue. You can also view a satisfaction study and make service improvements to support the expansion of your business. Your business may be affected by the satisfaction. Therefore, this depiction can quickly and easily make someone aware of this thing. And I used Power BI to create it so that each attribute is connected to the others.

Hospital Information Page:

All the information needed for the hospital is on this page. Here, you can quickly examine the number of admissions based on the time period you select and a comparison to the precise preceding time period. Additionally, you can view how many were released and the revenue you earned from them. Depending on the admission type, the degree of the disease, or the available bed, you can plan your resources. For instance, if I select Baltimore City and the time frame of 10/12/2019 to 10/03/2021, I can see that almost 9k patients used the hospital's services. We can conclude that in Baltimore City, patients in moderately bad health had to undergo trauma-type hospitalization in order to receive medical care from a department that mostly treated gynaecologists.

Patient Information Page:

You must understand what your patients desire if you want your business to grow. This page provides you with the patient-related information you require for professional purposes. You can find out how frequently a patient has used your services. Additionally, you can learn what they thought of the healthcare services they received. You can use the Patient Staying Information visualization to guide your judgments regarding patient stay information. According to the patient page, about 14k patients visited the Southern branch hospital between January 16, 2020, and November 11, 2020. The hospital department's average wait time was 3.38 minutes, which explains why most customers weren't happy with the services they received. Nearly 46k people have visited the facility over the time period given. Most of the patients are men, ranging in age from 51 to 60. The majority of them chose deferred discharge.

Attention Required Page:

Making the most important business decisions with the knowledge of overall satisfaction will be aided by this website. Not only that, but you may compare your revenue to the target amount. Patient Information Page, the portion before this one, showed patient satisfaction. The levels of satisfaction for the Neutral, Good, and Excellent kinds are carefully examined in this section. This will assist you in making decisions and give you a more thorough grasp of contentment. You may also see where the vast bulk of patients are coming from. Additionally helpful is the average wait time each department. The blow-up image reveals that the majority of the patients are from Baltimore City and that they are dissatisfied with the hospital's level of treatment. When it comes to satisfaction, they are largely in a neutral position. The organization's overall revenue is $1.6 billion, compared to their goal of $2.3 billion.

Conclusion:

In this case study, we focus mostly on making sure the KPI is present because that will ultimately help the decision-maker make the right choice. Power BI was mostly used in this case study to utilize the workload. Adopting new tools like Power BI has become urgently necessary due to the growing amount of data. Users may construct customized dashboards that mix on-premises and cloud-born data in a one view, allowing them to monitor their most important data enterprise wide and from all of their business apps. Dashboards, reports, and datasets form the core of Power BI. In conclusion, I have made sure that the important KPIs will accelerate corporate growth by assisting in decision-making.

PDF file link: Health Care Analytics Case Study