The M Code Behind the Power Query M function Cube.CollapseAndRemoveColumns

What is Cube.CollapseAndRemoveColumns?

Cube.CollapseAndRemoveColumns is a Power Query M function that allows you to collapse multiple columns into a single column. This function is particularly useful when working with large datasets that contain several similar columns, such as multiple columns containing sales data for different regions or months. By collapsing these columns, you can simplify your data and make it easier to work with.

In addition to collapsing columns, Cube.CollapseAndRemoveColumns also allows you to remove unwanted columns from your dataset. This can be useful when you have columns that contain redundant or unnecessary information.

How to use Cube.CollapseAndRemoveColumns

To use Cube.CollapseAndRemoveColumns, you’ll need to create a new query in Power Query. Once you’ve created your query, you can use the following steps to collapse and remove columns:

1. First, select the columns that you want to collapse. You can select multiple columns by holding down the Ctrl key while clicking on each column header.

2. Right-click on one of the selected columns and choose “Transform” from the context menu.

3. Choose “Group By” from the Transform menu.

4. In the Group By dialog box, select the column that you want to collapse into, then click the “Advanced” button.

5. In the Advanced options, choose “All Rows” for the Operation field, and enter a new column name for the New column name field.

6. To remove unwanted columns, select the columns you want to remove from the column headers, right-click and choose “Remove”.

7. Click “Close & Load” to apply your changes and load the new data into your workbook.

The M Code Behind Cube.CollapseAndRemoveColumns

If you’re interested in the technical details behind Cube.CollapseAndRemoveColumns, you can take a look at the M code that powers this function. The M code behind Cube.CollapseAndRemoveColumns is as follows:


Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“i45WMlTSUXJz0qzOzy/KSVEwNlRqsQA=”, BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),

#”Grouped Rows” = Table.Group(Source, {“Column1”}, {{“New Column”, each Text.Combine(List.Transform([Column2], Text.From), “,”), type text}}),

#”Removed Columns” = Table.RemoveColumns(#”Grouped Rows”,{“Column2”})


#”Removed Columns”

This code defines a new table from the input data, groups the data by the specified column, and then combines the values from the other columns into a single column. Finally, any unwanted columns are removed from the resulting dataset.

Managing large datasets with multiple columns can be a challenge, but with Power Query's Cube.CollapseAndRemoveColumns function, you can simplify your data and make it easier to work with. Whether you're working with sales data, customer data, or any other type of data, Cube.CollapseAndRemoveColumns can help you get the most out of your data analysis efforts. So, next time you encounter a dataset with too many columns, consider using Cube.CollapseAndRemoveColumns to streamline your workflow.

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

Upcoming Courses

10-12 Jul 23 (London or Online)
25-27 Sep 23 (London or Online)
20-22 Nov 23 (London or Online)

Contact Us


    Your Name (required)


    Email (required)


    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)