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:

let

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”})

in

#”Removed Columns”