Table.AlternateRows

D

Back to top

T

Back to top

#

Back to top

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

In this article, we will explore the M code behind the Table.AlternateRows function and learn how to use it effectively.

What is Power Query?

Before we dive into the specifics of the Table.AlternateRows function, let’s first understand what Power Query is. Power Query is a data transformation and cleansing tool that is built into Microsoft Excel and other Office applications.

Power Query allows you to connect to a variety of data sources, combine and transform data, and load the results into Excel or other destinations. With Power Query, you can easily clean and reshape data, automate data preparation tasks, and create powerful data models.

Using Table.AlternateRows to Alternate Row Colors

The Table.AlternateRows function is used to alternate the colors of rows in a table. This function takes two arguments: the name of the table and the number of rows to alternate between.

To use the Table.AlternateRows function, start by selecting the table you want to format. Then, go to the “Transform” tab in the Power Query Editor and select “Add Column” from the ribbon. In the dropdown menu, select “Custom Column”.

In the “Custom Column” dialog, enter a name for the new column and then enter the following formula in the “Custom column formula” field:

= Table.AlternateRows(#"Previous Step Name", Number.ToText(Number of Rows), Number.ToText(Number of Rows))

Replace “Previous Step Name” with the name of the step that contains the table you want to format. Replace “Number of Rows” with the number of rows you want to alternate between. For example, if you want to alternate between every two rows, enter “2” for both arguments.

After entering the formula, click “OK” to create the new column. You should now see a new column in your table with alternating row colors.

Understanding the M Code

Now that we have successfully used the Table.AlternateRows function, let’s take a closer look at the M code behind it.

The Table.AlternateRows function is actually a wrapper function that uses several other M functions to achieve the alternating row colors. These functions include Table.RowCount, List.Repeat, List.Zip, List.Select, and Table.FromColumns.

Here is the M code for the Table.AlternateRows function:

let

Source = (table as table, rowcount as number) =>

let

rows = Table.RowCount(table),

repeat = List.Repeat({true, false}, Number.RoundUp(rows/rowcount)),

zipped = List.Zip({Table.FromColumns(Table.ToColumns(table) & {repeat}), List.Select({repeat}, each _)}),

expanded = Table.ExpandListColumn(zipped, "_2"),

sorted = Table.Sort(expanded, List.Combine({List.Select(Table.ColumnNames(table), each _ <> "_1"), {"_1"}})),

reordered = Table.ReorderColumns(sorted, Table.ColumnNames(table)),

trimmed = Table.SelectRows(reordered, each [Index] < rows)

in

trimmed,

#"Previous Step Name" = Source(#"Previous Step Name", Number of Rows)

in

#"Previous Step Name"

As you can see, the M code is quite complex and uses several M functions to achieve the alternating row colors. However, you don’t need to fully understand the M code to use the Table.AlternateRows function in your Power Query projects.

In this article, we explored the M code behind the Table.AlternateRows function in Power Query. We learned how to use this function to alternate the colors of rows in a table and saw the M code that powers this function.

By using the Table.AlternateRows function, you can make your tables more visually appealing and highlight important data points. Power Query is a powerful tool for data transformation and analysis, and the Table.AlternateRows function is just one of the many functions that make it so useful.

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)