Text.Repeat

Understanding the Text.Repeat Function

The Text.Repeat function is used to repeat a specific text value a certain number of times. The syntax for the Text.Repeat function is as follows:

The function takes two arguments: ‘text’ and ‘count’. The ‘text’ argument is the value you want to repeat, and the ‘count’ argument specifies how many times you want to repeat it.

The M Code Behind Text.Repeat

Now that we understand the basic syntax of the Text.Repeat function, let’s take a look at the M code behind it. The M code for the Text.Repeat function is as follows:

Let’s break this code down into smaller sections to understand what’s happening at each step.

Defining the Function Parameters

The first line of the code defines the function parameters. This is where we specify the ‘text’ and ‘count’ arguments for the Text.Repeat function:

Creating a List of Repeated Values

The next step is to create a list of repeated values using the List.Repeat function. This function takes two arguments: a list of values to repeat and the number of times to repeat them. In our case, we are only repeating one value, so we use a list with a single value:

Converting the List to a Table

The next step is to convert the list of repeated values to a table. We use the Table.FromList function for this, which takes three arguments: the list of values, a splitter function, and a list of column names. Since we only have one column in our table, we use the Splitter.SplitByNothing() function as the splitter and specify the column name as “Text”:

Changing the Data Type of the Text Column

The final step is to change the data type of the “Text” column in our table to “text”. We use the Table.TransformColumnTypes function for this:

Returning the Repeated Text Value

The last line of the code returns the first value from the “Text” column in our table using the index operator:

Using Text.Repeat to Manipulate Your Data

Now that we understand the M code behind the Text.Repeat function, let’s take a look at how we can use it to manipulate our data. Suppose we have a table with a column of product codes, and we want to add a column with a repeated value for each product code. We can do this using the Text.Repeat function in Power Query:

1. Open the Power Query Editor and select your table.

2. Click on the “Add Column” tab and select “Custom Column”.

3. In the “Custom Column” dialog box, enter a name for your new column and the following formula:

This formula will repeat the value in the “Product Code” column three times. You can adjust the repeat count as needed.

4. Click “OK” to add the new column to your table.

The Text.Repeat function is a simple yet powerful tool that can be used to manipulate text values in Power Query. By understanding the M code behind the function, you can gain a deeper understanding of how it works and how to use it to your advantage. Whether you are working with small or large sets of data, the Text.Repeat function can save you time and effort when it comes to data manipulation.

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

Upcoming Courses

Contact Us