Cube.CollapseAndRemoveColumns
TableCollapses dimension attributes mapped to specified columns, removing them from the cube's tabular view and changing its granularity.
Syntax
Cube.CollapseAndRemoveColumns(cube as table, columnNames as list) as tableParameters
| Name | Type | Required | Description |
|---|---|---|---|
cube | table | Yes | The cube table to collapse columns from. |
columnNames | list | Yes | A list of column names to collapse and remove from the tabular view. |
Return Value
table — The cube table with the specified columns removed and the dimensional granularity collapsed accordingly.
Remarks
Cube.CollapseAndRemoveColumns changes the dimensional granularity of a cube's filter context by collapsing the attributes mapped to the specified columns. The columns are also removed from the tabular view. This effectively reverses an earlier Cube.AddAndExpandDimensionColumn operation for the specified attributes.
When attributes are collapsed, any measure columns in the cube are automatically re-aggregated to the new (coarser) granularity. For example, if you expanded both Product Category and Product Name, then collapsed Product Name, measure values would be aggregated up to the Product Category level.
This function operates on the cube's filter context and is not the same as Table.RemoveColumns, which only removes columns from a regular table without affecting OLAP semantics.
Examples
Example 1: Collapse a dimension column
let
Source = AnalysisServices.Database("localhost", "AdventureWorks"),
Cube = Source{[Name = "Adventure Works"]}[Data],
WithProduct = Cube.AddAndExpandDimensionColumn(
Cube,
{"[Product]"},
{"[Product].[Category]", "[Product].[Product Name]"}
),
WithSales = Cube.AddMeasureColumn(
WithProduct,
"Sales",
{"[Measures].[Internet Sales Amount]"}
),
CollapsedProduct = Cube.CollapseAndRemoveColumns(
WithSales,
{"Product Name"}
)
in
CollapsedProductExample 2: Collapse multiple columns at once
let
Source = AnalysisServices.Database("localhost", "AdventureWorks"),
Cube = Source{[Name = "Adventure Works"]}[Data],
WithDate = Cube.AddAndExpandDimensionColumn(
Cube,
{"[Date]"},
{"[Date].[Calendar Year]", "[Date].[Month]"},
{"Year", "Month"}
),
Collapsed = Cube.CollapseAndRemoveColumns(
WithDate,
{"Year", "Month"}
)
in
Collapsed