Cube.Transform

D

Back to top

T

Back to top

#

Back to top

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

But what is the M code behind Cube.Transform, and how does it work? In this article, we’ll explore the inner workings of Cube.Transform and how you can use it to streamline your data analysis workflows.

Understanding the M Code Behind Cube.Transform

To understand the M code behind Cube.Transform, it’s important to first understand what Cube.Transform does. At a high level, Cube.Transform allows you to connect to a multidimensional database, such as an OLAP cube, and import data into Power Query. Once the data is imported, you can use the M language to manipulate and analyze it.

The M code behind Cube.Transform is relatively complex and involves a number of different functions and operations. At its core, however, Cube.Transform is essentially a wrapper function that allows you to connect to a multidimensional database and import data into Power Query.

Here’s a basic example of the M code behind Cube.Transform:

let

Source = Cube.Transform(“Data Source=MyServer;Initial Catalog=MyDatabase”, “MyCube”),

#”MyCube_Measures” = Source{[Schema=”Measures”]}[Data],

#”MyCube_Dimensions” = Source{[Schema=”Dimensions”]}[Data]

in

#”MyCube_Measures”

This M code connects to a multidimensional database located on MyServer and imports data from the "MyCube" cube. It then extracts the measures from the cube and returns them as a table.

Advanced M Code Techniques for Cube.Transform

While the basic M code for Cube.Transform is relatively simple, there are a number of advanced techniques you can use to take your data analysis workflows to the next level.

Here are some tips and tricks for working with the M code behind Cube.Transform:

Using Filters to Extract Specific Data

One of the most powerful features of Cube.Transform is its ability to extract specific data from a multidimensional database. To do this, you can use the "Cube.Filter" function in your M code.

For example, let's say you only want to import sales data for a specific product category. You could use the following M code:

let

Source = Cube.Transform(“Data Source=MyServer;Initial Catalog=MyDatabase”, “MyCube”),

#”MyCube_Sales” = Source{[Schema=”Sales”]}[Data],

#”Filtered Rows” = Cube.Filter(#”MyCube_Sales”, {“Product Category”, “Equals”, “Electronics”})

in

#”Filtered Rows”

This M code connects to the "MyCube" cube and extracts the sales data. It then uses the Cube.Filter function to filter the data to only include sales for the "Electronics" product category.

Using the "Cube.TransformPartition" Function for Performance

If you're working with large datasets, you may find that the performance of Cube.Transform is slow. To improve performance, you can use the "Cube.TransformPartition" function in your M code.

The Cube.TransformPartition function allows you to split your data into smaller partitions and import them into Power Query separately. This can significantly improve performance, especially when working with large datasets.

Here's an example of how to use the Cube.TransformPartition function:

let

Source = Cube.TransformPartition(“Data Source=MyServer;Initial Catalog=MyDatabase”, “MyCube”, [Partition = “Partition1”]),

#”MyCube_Sales” = Source{[Schema=”Sales”]}[Data]

in

#”MyCube_Sales”

This M code uses the Cube.TransformPartition function to split the data into partitions and import only the "Sales" schema. This can help improve performance when working with large datasets.

Using Custom M Code to Transform Data

While Cube.Transform provides a powerful set of built-in functions for manipulating and analyzing data, you may find that you need to use custom M code to transform your data in a specific way.

To do this, you can use the "Cube.TransformData" function in your M code. This function allows you to write custom M code to transform your data before it's imported into Power Query.

Here's an example of how to use the Cube.TransformData function:

let

Source = Cube.TransformData(“Data Source=MyServer;Initial Catalog=MyDatabase”, “MyCube”, (cube) =>

let

#”MyCube_Sales” = cube{[Schema=”Sales”]}[Data],

#”Filtered Rows” = Table.SelectRows(#”MyCube_Sales”, each [SalesAmount] > 1000)

in

#”Filtered Rows”)

in

Source

This M code uses the Cube.TransformData function to import data from the "MyCube" cube and then applies custom M code to filter the data to only include sales with amounts greater than 1000. This custom M code can be as complex or simple as you need it to be, allowing you to transform your data in any way you see fit.

Cube.Transform is a powerful function in Power Query that allows you to import and analyze data from a variety of multidimensional databases. By understanding the M code behind Cube.Transform and using advanced techniques such as filters, partitions, and custom M code, you can take your data analysis workflows to the next level. So start exploring the power of Cube.Transform today and see what insights you can uncover from your 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)