Excel.ShapeTable

D

T

The M Code Behind the Power Query M function Excel.ShapeTable

The Excel.ShapeTable function takes in a single argument, which is a list of shapes. The shapes can be either images or geometric shapes such as rectangles, circles, and lines. When the function is executed, it returns a table with columns for each shape property such as height, width, and shape type.

Understanding the M Code Behind the Excel.ShapeTable Function

The M code behind the Excel.ShapeTable function is what makes it possible for the function to work. The function is written in the M language, which is a functional programming language used by Power Query. The M code behind the Excel.ShapeTable function can be broken down into two parts: the function signature and the function body.

Function Signature

The function signature is the first part of the M code and defines the function name and its input parameters. In the case of the Excel.ShapeTable function, the function signature is as follows:


Excel.ShapeTable = (shapes as list) =>


The function name is Excel.ShapeTable, and it takes in a single parameter called shapes, which is of type list.

Function Body

The function body is the second part of the M code and contains the instructions that the function executes when called. In the case of the Excel.ShapeTable function, the function body is as follows:


let

Source = Table.FromRecords(

List.Transform(

shapes,

each Record.FromList(Shape.ToList(_), Shape.PropertyNames)

),

{“Properties”}

),

#”Expanded Properties” = Table.ExpandRecordColumn(

Source,

“Properties”,

Shape.PropertyNames,

Shape.PropertyNames

)

in

#”Expanded Properties”


The function body contains several M functions that work together to create the table of shapes. Let's break down each of these functions:

#### Table.FromRecords

The Table.FromRecords function creates a table from a list of records. In the case of the Excel.ShapeTable function, the list of records is created using the List.Transform function, which we will discuss in the next section.

#### List.Transform

The List.Transform function applies a function to each element of a list and returns a new list with the transformed elements. In the case of the Excel.ShapeTable function, we use the List.Transform function to transform the list of shapes into a list of records. Each record is created using the Record.FromList function, which we will discuss in the next section.

#### Record.FromList

The Record.FromList function creates a record from a list of values. In the case of the Excel.ShapeTable function, we use the Record.FromList function to create a record for each shape. The record contains the properties of the shape, such as height, width, and shape type. The Shape.ToList and Shape.PropertyNames functions are used to get the list of values and property names for each shape, respectively.

#### Table.ExpandRecordColumn

The Table.ExpandRecordColumn function expands a record column into multiple columns. In the case of the Excel.ShapeTable function, we use the Table.ExpandRecordColumn function to expand the Properties column, which contains the record for each shape, into multiple columns. The expanded columns are the properties of the shape, such as height, width, and shape type.

The Excel.ShapeTable function is a powerful tool that allows users to create a table of shapes in Power Query. The M code behind the function is what makes it possible for the function to work. Understanding the M code behind the function can help users to customize the function to meet their 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)