Blog

How to-derive meaningful analytics across disconnected Power BI excels

biztalk middleware integration

Quite often, we get requests from our customers on how to go about analyzing data across multiple sheets which contain different types of data and making sense out of it. Here we are walking you through step by step process of how we went about creating a solution for a similar scenario where we will identify what are outstanding customer payments based on excels that contains different geographical locations and customer payments over a period of time.


First and foremost, we always check for the below key points

  1. Define proper dimensional structure for the data
  2. Date dimension is required
  3. Relations between normalization-payee and payee-payment need to be restructured.
  4. Many-to-many mapping need to be resolved.
  5. Duplicate values from the dimensional data need to be removed.

Solution:

In this case all the activities can be done easily in Power BI modeling directly.

  1. Load the 2 excels containing the separate sets of data into PowerBI model using Get Data
  2. Create dimensional data for Normalization and Payee with proper one-to-many mapping.
  • Add date dimension to the model.
  • Create a fact table from the given set of data
  • Create relations across the required dimensions 
  • Once all the above is done, it will look something like below for a sample snowflake model

Once all the above steps are complete, data is now ready for slice and dice for the given Normalization, Payee and Territory over a period.

Related Posts


Snowflake Cloud Data Platform

February 8, 2024

Snowflake 101: Why Choose Snowflake Cloud Data Platform for your Business?

This blog is the first in the series of articles on Snowflake. In this blog, let’s take a look at the basics of Snowflake Cloud Data Platform and why businesses should choose Snowflake for managing their data. What is Snowflake Cloud Data Platform? Snowflake Cloud Data Platform is a cloud-based data warehousing platform that helps

Microsoft Power Platform 2024 Release Wave 1 Updates

February 5, 2024

Microsoft Power Platform 2024: Release Wave 1 Plan Announcement

Microsoft announced the 2024 Release Wave 1 plans for Microsoft Power Platform and Microsoft Dynamics 365. This details the features and enhancements scheduled for rollout from April 2024 to September 2024. In the release plan, Microsoft reveals lot of new upcoming features that are planned to be released during the timeframe. Their aim is to