The data profile allows a intuitive ways to clean, transform, and understand data which helps you identifying the nuances of the data such as determining anomalies, examining and developing the underlying data structures, and querying data statistics such as row counts, value distributions, minimum and maximum values, averages, errors and so on.
You can use data profiling feature that is available in Power Query, apart that you can also create a Data Profiling report using Power Query M functions that is Table.Profile, this function is a table function which returns a profile for the columns of specified table.
The following information is returned for each column when applicable:
- standard deviation
- null count
- distinct count
Following is the syntax of Table.Profile M function.
Table.Profile(table as table, optional additionalAggregates as nullable list)
Lets see how to create a data profile report in Power BI using M function.
Here we have sample dataset financials that comes by default within Power BI desktop (October 2020 and later version).
Now go to Get data, and select Blank query.
Now you will see, a Query1 in Queries section and a formula bar opened in Power Query Editor that can be seen if have already enabled a formula bar in Power Query editor.
If not, then you can go to View tab and check the formula bar option as shown below.
Now we will write a M code function that will fetch the table profile for table financials.
Also note that if you have a space in your table name then you need to provide table a double quote with a # at the beginning of table name.
Suppose you have a table “financials Details” as you can see there is a space between table name so in this case you need to put a # at the beginning of table name as Table.Profile(#”financials Details”).
And you can see, it returns profiling data for each columns that you have in financials table.
Lets click on Close & Apply to save the changes.
Now you can see, a table named Query1 is created a you data model. Which contains the data profile for each column of financials table.
Lets create a Data Profiling report using this table for that you can simply drag all the table fields into table visual, and can take one slicer and load columns values into this so that you can filter the table visual data based on column name.
Once you are done with this, you can see, it displays a distribution of the data within the column and the counts of distinct values, Max, min, Average, and null counts, which is really helpful to examining the data and taking quick action.
3,303 total views, 4 views today