Looking for PowerObjects? Don’t worry, you’re in the right place! We’ve been part of HCL for several years, and we’ve now taken the final step in our acquisition journey: moving our website to the HCL domain. Nothing else is changing – we are still fanatically focused on Microsoft Business Applications!

PowerObjects Blog 

for Microsoft Business Applications

Data Quality Assessment & Data Profiling in Power BI

Post Author: Priya Subramanian and Asmita Chakraborti |

Businesses today are pushing to bring their business questions to their data. They are doing so alongside bridging the gap between obtaining data analysis for a business problem and using it to drive decision making. Indeed, they are aiming to maximize their Data Asset Value by:

  • Cost reduction from optimized asset creation process
  • Cost savings from mitigated data security and governance threats
  • Cost savings from improved data architecture
  • Revenue generated by using data as an asset to make impactful and strategic business decisions

This also means there is a lot of focus being put into maintaining quality data, which begs the question of whether a simple visual tool and process exists for evaluating data quality gaps that any business user can take advantage of. These data quality gaps are not confined to only missing, incomplete, and conflicting data formats and types, but also gaps in:

  • Operational process use of technology
  • Inadequate data rules and disconnected data sources
  • Data retention and security enforcement

A deep dive of the HCL Microsoft Business Application Practice’s Data Quality Assessment process will highlight the simple, people-first approach of the Data Governance project cycle that has been developed around Microsoft Power BI’s data profiling feature. In this blog, we focus on that data profiling feature and how it helps derive valuable insights into the quality of your data.

Power BI’s Data Profiling Tools

The data profiling tools provide new and intuitive ways to clean, transform, and understand data in Power Query Editor. They include:

  • Column quality
  • Column distribution
  • Column profile

To enable the data profiling tools, go to the Power Query Editor and click on the View tab on the ribbon. Enable the options you want in the Data preview group, as shown:

Data profiling tools.

After you enable the options, the screen display should mimic the following image in Power Query Editor.

Data profiling tools enabled.

Column quality

The column quality feature labels values in rows in five categories:

  • Valid, shown in green.
  • Error, shown in red.
  • Empty, shown in dark grey.
  • Unknown, shown in dashed green. Indicates when there are errors in a column, the quality of the remaining data is unknown.
  • Unexpected error, shown in dashed red.

These indicators are displayed directly underneath the name of the column as part of a small bar chart, as shown:

Enhanced view of the bar chart with data quality indicators and labels above each column in the table.

By hovering over any of the columns, you are presented with the numerical distribution of the quality of values throughout the column. Additionally, clicking the ellipsis (...) opens some quick action buttons for operations on the values.

A picture containing timeline  Description automatically generated

Column distribution

This feature provides a set of visuals underneath the names of the columns that showcase the frequency and distribution of the values in each of the columns. The data in these visualizations is sorted in descending order from the value with the highest frequency.

Column distribution.

Column profile

This feature provides a more in-depth look at the data in a column. Apart from the column distribution chart, it contains a column statistics chart. This information is displayed underneath the data preview section, as shown:

Column profile.

Viewing the above profiled data in a Table format so that it can be used in a dashboard

We will use a power query function (Table.Profile()) in Power BI that processes a table of all columns and returns a profile for the columns in table as shown below, and then use that table to build dashboards.

  • minimum
  • maximum
  • average
  • standard deviation
  • count
  • null count
  • distinct count

A screenshot of a computer  Description automatically generated with medium confidence

Happy Power BI’ing!

By Joe D365
Joe D365 is a Microsoft Dynamics 365 superhero who runs on pure Dynamics adrenaline. As the face of PowerObjects, Joe D365’s mission is to reveal innovative ways to use Dynamics 365 and bring the application to more businesses and organizations around the world.

Leave a Reply

Your email address will not be published. Required fields are marked *

PowerObjects Recommends