Cube.ReplaceDimensions

D

Back to top

T

Back to top

#

Back to top

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

Understanding Cube.ReplaceDimensions Function

Cube.ReplaceDimensions is a Power Query M function that is used to replace dimensions in a cube. It takes in three arguments: Cube, OldDimension, and NewDimension. The Cube argument specifies the cube that needs to be transformed. The OldDimension argument specifies the dimension that needs to be replaced, while the NewDimension argument specifies the replacement dimension.

The syntax for the Cube.ReplaceDimensions function is as follows:

Cube.ReplaceDimensions(Cube as any, OldDimension as any, NewDimension as any) as any

Here, the 'as any' keyword is used to specify that the arguments can take any data type.

The M Code Behind Cube.ReplaceDimensions

To understand the M code behind Cube.ReplaceDimensions, we need to understand how it works. Cube.ReplaceDimensions function replaces a dimension in a cube with a new dimension. It does this by creating a new cube that has the new dimension and then copying the data from the old cube to the new cube.

The M code behind Cube.ReplaceDimensions function can be broken down into the following steps:

1. Get the CubeData: The CubeData is the data that needs to be transformed. It is obtained by calling the Cube.GetCubeData function.

2. Get the OldDimensionData: The OldDimensionData is the data that needs to be replaced. It is obtained by calling the Cube.GetDimensionData function.

3. Get the NewDimensionData: The NewDimensionData is the replacement data. It can be obtained from a table or by calling another function that returns a table.

4. Replace the OldDimensionData with the NewDimensionData: This is done by using the Table.ReplaceMatchingRows function to replace the OldDimensionData with the NewDimensionData.

5. Create a new CubeData: The new CubeData is created by using the Cube.ReplaceDimensionData function. This function takes in the old CubeData and the new dimension data and creates a new CubeData.

6. Return the new CubeData: The new CubeData is returned as the output of the Cube.ReplaceDimensions function.

Example

Let's take an example to understand how Cube.ReplaceDimensions function works. Suppose we have a cube that contains sales data by region and product. The dimensions are Region and Product. We want to replace the Region dimension with a new dimension called Country. The new dimension contains the same data as the Region dimension but is grouped by country instead of region.

To do this, we can use the following M code:

let

CubeData = Cube.GetCubeData(“Sales Cube”),

OldDimensionData = Cube.GetDimensionData(CubeData, “Region”),

NewDimensionData = GetCountryDimensionData(),

NewCubeData = Cube.ReplaceDimensionData(

CubeData,

“Region”,

NewDimensionData

),

Output = Cube.ReplaceDimensions(

CubeData,

“Region”,

NewDimensionData

)

in

Output

Here, we first get the CubeData using the Cube.GetCubeData function. Then we get the OldDimensionData using the Cube.GetDimensionData function. We get the NewDimensionData by calling the GetCountryDimensionData function (which returns a table). We then use the Cube.ReplaceDimensionData function to create a new CubeData with the new dimension data. Finally, we use the Cube.ReplaceDimensions function to replace the old dimension with the new dimension in the cube.

In this article, we have delved into the M code behind the Power Query M function Cube.ReplaceDimensions. We have seen that this function is used to replace dimensions in a cube and that it works by creating a new cube with the new dimension and copying the data from the old cube to the new cube. We have also seen an example of how this function can be used to replace a dimension in a cube. With this knowledge, users can use the Cube.ReplaceDimensions function to transform data in Power Query.

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)