Table.Profile

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Table.Profile

What is the Power Query M Language?

Power Query uses a language called M to perform data transformations. M is a functional programming language that is used to build the queries that Power Query executes. The language is based on a set of functions and expressions that can be combined to perform complex data manipulations.

What is the Table.Profile Function?

The Table.Profile function is a built-in function in Power Query that provides a quick and easy way to analyze data. It returns a table that contains a profile of the data in the input table. The profile includes statistics such as the total number of rows, the number of distinct values, and the data type of each column.

Understanding the M Code Behind Table.Profile

The Table.Profile function is essentially a wrapper for a series of other M functions. Understanding how these functions work together can help you create more powerful data transformations in Power Query.

The M code for the Table.Profile function is as follows:

let

Source = ,

Types = Table.TransformColumnTypes(Source, Table.TransformColumnTypesOptions.Ignore),

Stats = Table.Group(Types, {}, {{"Column", each Text.Combine(List.Transform(Table.ColumnNames(_), Text.From), ", "), type text},

{"Count", each Table.RowCount(_), type number},

{"Distinct Count", each List.Count(List.Distinct(Table.Column(_,"Value")), type number},

{"Null Count", each List.Count(Table.SelectRows(_, each Record.Field(_, "Value") = null), type number},

{"Min", each List.Min(Table.Column(_, "Value")), type any},

{"Max", each List.Max(Table.Column(_, "Value")), type any},

{"Average", each List.Average(Table.Column(_, "Value")), type number},

{"Median", each List.Median(Table.Column(_, "Value")), type any},

{"Standard Deviation", each List.StandardDeviation(Table.Column(_, "Value")), type number}})

in

Stats

Let’s break down this code to understand how it works.

Step 1: Load the Input Table

The first step in the Table.Profile M code is to load the input table into Power Query. This is done using the following line of code:

Source =

The input table is referred to as “Source” throughout the rest of the code.

Step 2: Transform the Column Types

The next step is to transform the data types of each column in the input table. This is done using the Table.TransformColumnTypes function:

Types = Table.TransformColumnTypes(Source, Table.TransformColumnTypesOptions.Ignore)

The Table.TransformColumnTypes function takes two arguments: the input table (in this case, Source), and an options record that specifies how to handle errors and other settings. The TransformColumnTypesOptions.Ignore option is used here to ignore any errors that may occur during the data type transformation.

Step 3: Calculate Statistics for Each Column

The next step is to calculate statistics for each column in the input table. This is done using the Table.Group function:

Stats = Table.Group(Types, {}, {{"Column", each Text.Combine(List.Transform(Table.ColumnNames(_), Text.From), ", "), type text},

{"Count", each Table.RowCount(_), type number},

{"Distinct Count", each List.Count(List.Distinct(Table.Column(_,"Value")), type number},

{"Null Count", each List.Count(Table.SelectRows(_, each Record.Field(_, "Value") = null), type number},

{"Min", each List.Min(Table.Column(_, "Value")), type any},

{"Max", each List.Max(Table.Column(_, "Value")), type any},

{"Average", each List.Average(Table.Column(_, "Value")), type number},

{"Median", each List.Median(Table.Column(_, "Value")), type any},

{"Standard Deviation", each List.StandardDeviation(Table.Column(_, "Value")), type number}})

The Table.Group function takes three arguments: the input table (in this case, Types), a list of grouping columns (in this case, an empty list), and a list of aggregation functions that will be applied to each group.

The aggregation functions used in the Table.Profile M code are:

– Column: This function combines the column names into a single string with commas between each name.

– Count: This function returns the total number of rows in each group.

– Distinct Count: This function returns the number of distinct values in each group.

– Null Count: This function returns the number of null values in each group.

– Min: This function returns the minimum value in each group.

– Max: This function returns the maximum value in each group.

– Average: This function returns the average value in each group.

– Median: This function returns the median value in each group.

– Standard Deviation: This function returns the standard deviation of the values in each group.

The Table.Profile function is a powerful tool for analyzing data in Power Query. Understanding the M code behind the function can help you create more complex data transformations and gain deeper insights into your data. By using the Table.TransformColumnTypes and Table.Group functions, you can create custom data profiles that are tailored to your specific needs.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)