Table.View

One of the most powerful features of Power Query is the ability to write custom M code. M is the language used by Power Query to define transformations and data manipulation operations. By writing custom M code, you can extend the capabilities of Power Query and create complex data transformations that are not available out of the box.

In this article, we will explore the M code behind the Power Query M function Table.View. Table.View is a powerful function that lets you visualize the contents of a table in Power Query. We will examine the syntax and parameters of Table.View, and provide examples of how you can use it in your data transformation workflows.

Syntax of Table.View

The syntax of Table.View is straightforward:

The first parameter, `table`, is a required parameter that specifies the table you want to visualize. This can be a table loaded from a data source, or a table generated by a previous step in your transformation workflow.

The second parameter, `columns`, is an optional parameter that lets you specify which columns you want to include in the visualization. By default, all columns in the table are included.

The third parameter, `sorting`, is an optional parameter that lets you specify the sorting order of the table. This can be used to sort the table by one or more columns in ascending or descending order.

The fourth parameter, `ascending`, is an optional parameter that lets you specify the ascending order of the sorting. This should be a list with the same length as the `sorting` list, where each item in the list corresponds to the sorting order of the corresponding column.

The fifth parameter, `maximumRowsToShow`, is an optional parameter that lets you specify the maximum number of rows to include in the visualization. By default, all rows in the table are included.

Examples of Table.View

Let’s look at some examples of how you can use Table.View to visualize tables in Power Query.

Example 1: Visualizing a simple table

Suppose you have a simple table with two columns – “Product” and “Sales”. You want to visualize this table to get a quick overview of the sales by product. Here’s how you can do it:

In this example, we first load the table from the current workbook, change the data type of the “Sales” column to integer, sort the table in descending order by sales, filter the rows to only include sales greater than 100, and finally apply the Table.View function to visualize the table.

When we run this query, we get a visualization of the table that shows the “Product” and “Sales” columns, sorted by sales in descending order, and filtered to only include products with sales greater than 100.

Example 2: Visualizing a table with multiple columns

Suppose you have a more complex table with multiple columns, and you want to visualize only a subset of the columns. Here’s how you can do it:

In this example, we load the table from the current workbook, change the data type of the “Sales”, “Cost”, and “Profit” columns to integer, add a custom column that calculates the profit margin, filter the rows to only include products with a margin greater than 20%, and finally apply the Table.View function to visualize only the “Product”, “Sales”, and “Margin” columns.

When we run this query, we get a visualization of the table that shows only the “Product”, “Sales”, and “Margin” columns, sorted by product name, and filtered to only include products with a margin greater than 20%.

In this article, we explored the M code behind the Power Query M function Table.View. We examined the syntax and parameters of Table.View, and provided examples of how you can use it in your data transformation workflows.

By leveraging the power of custom M code, you can create complex data transformations and visualizations that are tailored to your specific business needs. With Table.View, you can quickly visualize the contents of a table, sort and filter it by multiple columns, and display only the columns that are relevant to your analysis.

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

Upcoming Courses

Contact Us