Data quality is critical for decision-making and operational efficiency all businesses and organisations. Discrepancies such as incorrect, missing, or outdated data can hinder analytics, misinform stakeholders, and lead to flawed strategies. Microsoft Power BI, my particular favourite data analytics tool, offers a robust platform to identify, visualise, and manage such data issues effectively.
In this blog post, I discuss how Power BI connects seamlessly to corporate systems, to identify data discrepancies through queries and visualisations, and can be a support to responsible officers in the tasks of rectifying errors at the source.
Welcome to the Iamdata Solutions Asset Management Newsletter - February 2025
Power BI Integration with Corporate Systems
I know I've said it many times before but, Power BI stands out as a remarkably versatile data integration platform, offering unparalleled connectivity across diverse technological ecosystems. Its strength lies in the ability to seamlessly connect with a wide array of data sources, ranging from traditional databases like SQL Server and Oracle to complex enterprise systems such as TechOne, Civica Authority, AMS, SAP, and GIS platforms.
Power BI’s robust integration capabilities extend beyond conventional data repositories, encompassing cloud-based applications, custom APIs, and standard file formats like Excel spreadsheets and CSV files. This comprehensive approach enables organisations to transcend traditional data silos, creating a unified and comprehensive view of their operational insights.
The fact that Power BI supports such a broad spectrum of data sources, empowers businesses to consolidate information from virtually any system or application within their technological infrastructure. This flexibility ensures that organisations can easily transform, analyse, and visualise data from multiple touchpoints, ultimately driving more informed decision-making and strategic planning.
Power BI’s automated data refresh capability ensures that dashboards consistently display up-to-date information, reducing the risk of relying on outdated data. This ability to connect and maintain real-time data integrity forms the foundation for detecting and addressing data discrepancies.
Detecting Data Discrepancies and Errors with Power BI
Power BI provides powerful tools to identify data discrepancies, such as incorrect, missing, or outdated information. Through its querying capabilities and robust visualisations, we can pinpoint anomalies and data issues that might otherwise go unnoticed.
For identifying incorrect data, Power BI’s Data Analysis Expressions (DAX) allow users to create complex queries that flag values outside acceptable ranges.
These Quality Assurance Check Cards are using DAX measures to provide the data.
For example, for infrastructure assets, such as water pipes and water nodes, we can build intelligence into the Power BI reports to flag anomalies in dimensions or sizes to identify incorrect data points such as water pipes with diameters that fall outside a specified range (e.g., too small to be functional or unusually large) or sewer nodes with unrealistic size attributes. Categorisation inconsistencies or mismatched data types can also be detected.
Missing data, another common issue, can be identified using logical filters to detect blank or null values in critical fields. This process is particularly useful for finding incomplete records or missing foreign keys in relational datasets.
Conditional formatting in tables can emphasise specific records with discrepancies.
Outdated data can be flagged by building time-based queries that highlight records with no recent updates. For example, fields like last login timestamps or system update dates can be used to filter out stale records.
Key performance indicator (KPI) visualisations can be configured to alert users when records exceed predefined age limits.
Building Power BI Workflows for Data Validation
We can establish comprehensive workflows in Power BI to address data validation needs. Data profiling can be performed using Power Query Editor, where users can identify patterns and anomalies during the initial data preparation phase. Power BI’s built-in artificial intelligence (AI) features can further assist in spotting trends or potential issues automatically.
Custom reports can then be created to display discrepancies in an actionable format. These reports can be tailored to specific roles within the organisation, ensuring that users see data issues most relevant to their responsibilities.
Filters and slicers allow users to drill down into specific departments, regions, or timeframes, enabling targeted analysis. Power BI also have alerting capabilities and we can configure the Report to notify users about critical issues in real time, while integration with Power Automate allows discrepancy reports to be shared with relevant personnel automatically.
Rectifying Discrepancies and Errors in the Core Data
Once Power BI has identified discrepancies, responsible officers can act on this information to correct errors directly in the originating systems. Discrepancy reports can be exported from Power BI or shared through interactive dashboards, providing clear guidance on the issues.
Power BI reports can include direct links to the relevant records in the corporate system, enabling quick navigation for corrections. After errors have been addressed, the Power BI reports will automatically refresh and verify that corrections were successful. There are ways we can build into the reports the means to provide an audit trail, if required and ensure accountability.
Case Study - Improving Data Quality with Power BI
A local government organisation responsible for managing many asset classes leveraged Power BI to improve the accuracy of their asset data.
Client Overview
The organisation faced challenges with incomplete and outdated information in its asset management system, including missing installation dates, incorrect maintenance records, and unlogged repairs. These data issues hindered the ability to prioritise asset maintenance, budget effectively, and comply with regulatory reporting requirements.
Project Overview - How can Power BI help
I created a series of Power BI reports to monitor and visualise discrepancies in the data.
The Power BI Report highlighted assets missing critical details such as installation dates or material specifications, which allowed us to track discrepancies between scheduled and actual maintenance activities.
Power BI Report's conditional formatting and alert features are also helpful to automate notifications for assets with overdue Inspections. For example, assets that hadn’t been inspected within the time frame were flagged in the report (green check meaning good, red cross meaning the asset is overdue for inspection), drawing immediate attention to these assets.
Identifying Spatial Data Errors with Power BI
Spatial visualisations are particularly useful for identifying records that lay outside the council LGA area.
Any coordinates that fall within these limits can be considered ‘In Area’, while those outside will be classified as ‘Out of Area’.
To implement this logic in Power BI, I have created a calculated column using a simple DAX query. This formula checks whether the latitude and longitude values of a given data point fall within the specified range:
InArea =
IF(
[Latitude] >= -34.118 && [Latitude] <= -33.578 &&
[Longitude] >= 150.520 && [Longitude] <= 151.343,
"In Area",
"Out of Area"
)
Here's an example of how this can been a very powerful spatial data check in Power BI:
And for identifying geographic clusters of assets with incomplete or incorrect records.
Armed with these insights, all key staff and stakeholders collaborated to address these data issues and fix them in the core systems. Missing records were updated by cross-referencing 'As Constructed' drawings, and a streamlined workflow was implemented to ensure maintenance data was logged accurately and promptly moving forward.
This case study demonstrates how Power BI can be applied to infrastructure asset management to identify and rectify data discrepancies, ultimately leading to better resource allocation and improved service delivery.
Power BI
Power BI is a versatile and powerful tool for detecting and addressing data discrepancies across corporate systems. Its ability to connect seamlessly with diverse data sources, coupled with its advanced analytical and visualisation capabilities, makes it an indispensable asset for maintaining high data quality standards.
Integrating Power BI into your data management workflows, you can ensure your data remains accurate, complete, and up-to-date, providing a reliable foundation for informed decision-making.
I would love to hear from you. Please contact me: jill.singleton@iamdata.solutions if you'd like to discuss how I can help you in your data cleansing Power BI journey.
I have worked on many different projects with my Local Government clients, from designing and developing Power BI Reports, to building SQL Server databases for spatial data, to managing and maintaining GIS and the Asset Management systems. If you'd like to discuss how we might work together, then please email Jill at ➡️ jill.singleton@iamdata.solutions
If you would like to receive the latest Newsletter Blog straight to your inbox, please subscribe here: ➡️ https://www.iamdata.solutions/subscribe
You can read all our Newsletters and Blogs here:➡️ https://www.iamdata.solutions/blog
You may also be interested in our Projects Page:➡️ https://www.iamdata.solutions/past-projects
Check out what our clients say about us here:➡️ https://www.iamdata.solutions/reviews
If you would like to see a particular topic covered in these newsletters, then please let me know about it. The chances are other people will be interested and would like to hear about it too! Please email me at: ➡️ jill.singleton@iamdata.solutions with your suggestions.
Comments