Cube.AddAndExpandDimensionColumn

D

Back to top

T

Back to top

#

Back to top

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

One of Power Query’s M functions is Cube.AddAndExpandDimensionColumn. This function adds a new column to a table and expands it into multiple columns, creating new rows for each combination of other columns in the table.

Syntax of Cube.AddAndExpandDimensionColumn Function

The syntax of Cube.AddAndExpandDimensionColumn function is as follows:

Cube.AddAndExpandDimensionColumn(table as table, column as text, values as list, newColumnNames as list, optional missingValue as nullable any) as table

The parameters are:

- table: A table or a reference to a table.

- column: The name of the column to expand.

- values: A list of values to expand into new columns.

- newColumnNames: A list of column names for the new expanded columns.

- missingValue (optional): The value to replace any missing values with.

How Cube.AddAndExpandDimensionColumn Function Works

The Cube.AddAndExpandDimensionColumn function works by adding a new column to the table, then expanding it into multiple columns, creating new rows for each combination of other columns in the table.

For example, consider the following table:

Table.InlineTable({{1, “A”, 10}, {1, “B”, 20}, {2, “A”, 30}, {2, “B”, 40}})

This table has three columns: "Column1", "Column2", and "Column3". We can use the Cube.AddAndExpandDimensionColumn function to add a new column "Column4" with values "X" and "Y", and expand it into two new columns "Column5" and "Column6":

Cube.AddAndExpandDimensionColumn(Table.InlineTable({{1, “A”, 10}, {1, “B”, 20}, {2, “A”, 30}, {2, “B”, 40}}), “Column4”, {“X”, “Y”}, {“Column5”, “Column6”})

The resulting table will have six columns: "Column1", "Column2", "Column3", "Column4", "Column5", and "Column6". The "Column4" will be expanded into two columns "Column5" and "Column6", and new rows will be created for each combination of other columns in the table. The missing values will be replaced with null by default.

Use Cases of Cube.AddAndExpandDimensionColumn Function

The Cube.AddAndExpandDimensionColumn function is used to expand a column with multiple values into separate columns. This is useful in scenarios where you need to transform data from wide to long format or create a pivot table.

For example, consider a table with three columns "Region", "Product", and "Sales". We can use the Cube.AddAndExpandDimensionColumn function to expand the "Product" column into separate columns for each product:

let

Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText(“i45WMjIwMtQ0sTVXS87PUUgnWkGxg8lKizFqWn5+Uk5yQmZyfnl+UklqSnKxJzE2NzYyMLQyMzUwtDQ3MzYyNjE2JzIvPyxKLElNSixJTDorKzQxNjM1Mjk1KSpJLEktK0opLUspNTc7MjQwMjQ1MTUzMTM7OTkwMjM1NzQwMDE7MDsxOzA7MDswOzA7MDswOzA7MDswOzA7MDswOzA7MDswOzA7MDswOzA7MDswOyMqLyo/MDBJzUjMSS0qtvLOMwq3Mz0nUS0rKzNMSUs5sLkwtjE3N7c1MDQ3MzYyJzUwMzQ1NzUyMzY3″, BinaryEncoding.Base64)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#”Region/Product” = _t, Sales = _t])),

#”Split Column by Delimiter” = Table.SplitColumn(Source, “Region/Product”, Splitter.SplitTextByDelimiter(“/”, QuoteStyle.Csv), {“Region”, “Product”}),

#”Changed Type” = Table.TransformColumnTypes(#”Split Column by Delimiter”,{{“Region”, type text}, {“Product”, type text}, {“Sales”, Int64.Type}}),

#”Added Custom” = Cube.AddAndExpandDimensionColumn(#”Changed Type”, “Product”, {“A”, “B”, “C”}, {“Sales for A”, “Sales for B”, “Sales for C”})

in

#”Added Custom”

The resulting table will have six columns: "Region", "Product", "Sales", "Product.A", "Product.B", and "Product.C". The "Product" column will be expanded into three columns "Product.A", "Product.B", and "Product.C", and new rows will be created for each combination of other columns in the table. The missing values will be replaced with null by default.

The Cube.AddAndExpandDimensionColumn function is a powerful M function in Power Query that allows you to expand a column with multiple values into separate columns. It is useful in scenarios where you need to transform data from wide to long format or create a pivot table. By understanding how this function works and its use cases, you can take advantage of its capabilities to transform and clean data.

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)