The M Code Behind the Power Query M function Essbase.Cubes

The Essbase.Cubes function allows you to connect to Essbase, a multidimensional database management system. With this function, you can access data from Essbase cubes and bring it into Power Query. But what’s the M code behind the Essbase.Cubes function? Let’s take a closer look.

Understanding the Essbase.Cubes Function

Before diving into the M code behind the Essbase.Cubes function, it’s important to understand what the function does. The Essbase.Cubes function returns a table of cube metadata for a given Essbase server. The metadata includes information about the cubes available on the server, such as the name of the cube, the number of dimensions, and the size of the cube.

The function takes two arguments: the Essbase server name and the optional Essbase login credentials. Here’s an example of the Essbase.Cubes function in action:


Source = Essbase.Cubes(“EssbaseServerName”),

#”Filtered Rows” = Table.SelectRows(Source, each ([Cube Name] = “Sample”))


#”Filtered Rows”

In this example, we're using the Essbase.Cubes function to connect to an Essbase server named "EssbaseServerName" and retrieve metadata for all cubes on that server. We then filter the results to only include the cube named "Sample".

Breaking Down the M Code

Now that we understand what the Essbase.Cubes function does, let's take a closer look at the M code behind it. Here's the M code for the Essbase.Cubes function:


EssbaseCubes = (EssbaseServerName as text, optional EssbaseCredentials as record) =>


EssbaseXmlaEndpoint = “http://” & EssbaseServerName & “/aps/XMLA”,

EssbaseSessionHeader = if EssbaseCredentials = null then [] else [#”X-Essbase-Auth”=EssbaseCredentials[Token]],

EssbaseCubesRequest = Xml.Tables(Web.Contents(EssbaseXmlaEndpoint, [Headers=EssbaseSessionHeader, Query=[#”COMMAND”=”MDSCHEMA_CUBES”]]), “Cube”),

EssbaseCubesTable = Table.FromList(EssbaseCubesRequest, Splitter.SplitByNothing(), null, null, ExtraValues.Error),







At a high level, the M code for the Essbase.Cubes function is defining a function called "EssbaseCubes" that takes two arguments: the Essbase server name and optional login credentials. The function then uses these arguments to build a request to the Essbase XMLA endpoint, which returns metadata about the cubes on the server.

The metadata is returned as an XML table, which is then converted into a Power Query table using the "Table.FromList" function. Finally, the table is expanded into its constituent columns using the "Table.ExpandRecordColumn" function.

The Essbase.Cubes function is a powerful M function that allows you to connect to Essbase and retrieve metadata about the cubes on a server. Understanding the M code behind the function can help you customize its behavior and build more complex queries in Power Query.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us


    Your Name (required)


    Email (required)


    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)