Function.ScalarVector

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Function.ScalarVector

In this article, we will explore the M code behind the Function.ScalarVector function and discuss how it can be used to transform data in Power Query.

Understanding Scalar Functions

Before we dive into the M code behind the Function.ScalarVector function, it’s important to understand scalar functions. Scalar functions are functions that operate on a single value and return a single value. Examples of scalar functions include:

– Text functions, such as Text.Length and Text.Upper

– Math functions, such as Number.Abs and Number.Round

– Date and time functions, such as DateTime.AddDays and DateTime.FromText

Scalar functions are extremely useful in data transformation, as they allow users to apply a consistent operation to each value in a dataset.

Introducing the Function.ScalarVector Function

The Function.ScalarVector function is a powerful tool that allows users to apply a scalar function to each item in a vector. The syntax for the Function.ScalarVector function is as follows:

Function.ScalarVector(vector as list, function as function) as list

The function takes two arguments: a vector (represented as a list) and a scalar function. The function is then applied to each item in the vector, resulting in a new vector with the same number of items.

For example, if we have a vector containing the values {1, 2, 3} and we want to apply the Number.Square function to each item, we would use the following M code:

Function.ScalarVector({1, 2, 3}, Number.Square)

This would result in a new vector containing the values {1, 4, 9}.

The M Code Behind the Function.ScalarVector Function

Now that we understand the basics of the Function.ScalarVector function, let's take a closer look at the M code behind it.

The M code for the Function.ScalarVector function is relatively simple. The function takes two arguments, a vector (represented as a list) and a scalar function. The function then applies the scalar function to each item in the vector, resulting in a new list.

Here is the M code for the Function.ScalarVector function:

( vector as list, function as function) as list =>

List.Transform(vector, each function(_))

Let's break this down line by line:

- The first line defines the function and its arguments. The vector argument is defined as a list, while the function argument is defined as a function.

- The second line uses the List.Transform function to apply the scalar function to each item in the vector. The 'each' keyword is used to apply the function to each item in the list.

Using the Function.ScalarVector Function in Power Query

Now that we understand the M code behind the Function.ScalarVector function, let's discuss how it can be used in Power Query.

One common use case for the Function.ScalarVector function is to apply a scalar function to a column in a table. For example, if we have a table containing a "Price" column and we want to apply a 10% discount to each price, we could use the Function.ScalarVector function in the following way:

1. In Power Query, select the "Price" column in the table.

2. In the "Add Column" tab, select "Custom Column".

3. In the "Custom Column" dialog box, enter the following M code:

Function.ScalarVector([Price], each Number.Round([Price] 0.9, 2))

4. Click "OK" to create the new column.

This will create a new column in the table containing the discounted prices.

The Function.ScalarVector function is a powerful tool that allows users to apply a scalar function to each item in a vector. By understanding the M code behind the function, users can gain a deeper understanding of how it works and how it can be used to transform data in Power Query. Whether you're working with text, math, or date functions, the Function.ScalarVector function can help you to quickly and easily apply your desired operation to a dataset.

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)