Data profiling brings a lot of benefits and provides a first step into better understanding your data structure and quality. There are a few dedicated tools, but they have associated costs. What if I told you that you might already have access to a data profiling solution? Well, if you are using Excel 2010 and higher, you are in luck. Installing the free Power Query extension for the 2010 and 2013 versions will provide you with 2 simple functions that I will cover below. Versions 2016 and higher, including the 365 version, should already come with Power Query enabled.
You can apply the following data profiling solution to almost any data source. This includes MySQL, SQL Server, Oracle, SAP HANA, Azure, SharePoint list, Salesforce reports or objects, ODBC, oData feed, XML, JSON, a simple Excel or CSV file, and others.
To make it clear, this is NOT a paid advertisement and the views on this solution are of my own.Â
Without further ado, here is a quick and inexpensive way to perform data profiling on a particular data set:
Table.Profile function
Description: This Power Query function returns a total of 8 columns. The first represents the columns in your data source and the remaining 7 describe the following: min, max, average, standard deviation, count, null count, and distinct count.
How to use it: Create a new “Blank Query” query type and type in the following function
=Table.Profile(#”name of the query you are profiling”)
Example:
I’m using the Cost of Living Index for Selected US Cities data set and after separating the urban area into city and state, I’m doing a quick data profiling to better understand the state of this data. Even at a first glance, I’m spotting the following:
- In the city field, there is the “Akron OH” value, which is obviously including the state as well
- The “Grocery Items” average is way below the rest, which might be correct, but worth analyzing further
- There is a value missing in the “Grocery Items” and “State” columns
- There are 58 unique “State” values, clearly indicating a data quality issue
To keep track of your data quality efforts, here is a  free template of a data quality issues log.
Table.Schema function
Description: This function returns a table describing the columns in the table you are profiling. Each row describes the properties of each of your table columns and includes: type name, type kind, nullable, numeric precision, numeric scale, maximum length, and a few others. Feel free to read in detail about each one on MS’ Power Query Syntax site.
How to use it: Create a new “Blank Query” query type and type in the following function
=Table.Schema(#”name of the query you are profiling”)
Example:
I’m using a free PayStub oData feed which describes information on pay stubs, such as their ID, pay period and date, amount, deducted amount, employee ID and so forth. Just glancing at the results of the schema function, I’m spotting the following:
- “Amount”, “GrossPay” and “Deduction” are of Decimal type and maybe they need to be Currency
- The Decimal Numbers might need to be changed to a Fixed Decimal Number type in order to improve compression and query performance
- The “Period”, which denotes the payment time period the pay stub is for, is currently of Text type, so I’ll have to convert it to DateTime
- “PersonID” is also “Text”, which depending on the rules of generating this unique employee ID, might have to be an Integer
- “PersonID” allows for null values which should not be allowed if you are only providing pay stubs to employees
Within 5 minutes I’m spotting potential data architecture issues which could have an impact on the quality of the data, data usage or data integration efforts.
Conclusion
For professional data profiling techniques, I recommend dedicated tools, but I wanted to share this alternative as it provides a quick and easy solution when you don’t have access to such tools. The schema and profile functions provide a quick analysis of the state of the data set you are planning on working with. It can point you in the right direction to determine the quality and issues of data, helps you compare schemas for data integration tasks, or determine if the data types comply with the business requirements.
Have you used this before? Was it sufficient to get you started?
