Cube.AttributeMemberProperty

Cube.AttributeMemberProperty

D

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

In this article, we’ll take a deep dive into the M code behind Cube.AttributeMemberProperty. We’ll explore the different parameters you can pass to the function and how they affect the output. We’ll also look at some examples of how to use this function in practice. So, let’s get started.

Understanding the Syntax of Cube.AttributeMemberProperty

Before we dive into the M code behind Cube.AttributeMemberProperty, let’s take a moment to understand the syntax of the function. Here’s the basic syntax of Cube.AttributeMemberProperty:


Cube.AttributeMemberProperty(connection as text, cube as text, measureOrDimension as text, attribute as text, member as text, property as text, optional culture as nullable text) as any


As you can see, the function takes several parameters:

- connection: The name of the Power BI data source or connection.

- cube: The name of the cube that contains the measure or dimension you want to retrieve.

- measureOrDimension: The name of the measure or dimension you want to retrieve the member attribute from.

- attribute: The name of the member attribute you want to retrieve.

- member: The name of the member you want to retrieve the property for.

- property: The name of the property you want to retrieve, such as "Name", "Caption", or "UniqueName".

- culture: An optional parameter that specifies the culture to use when retrieving the property. If not specified, the default culture of the current user is used.

Exploring the M Code Behind Cube.AttributeMemberProperty

Now that we understand the basic syntax of Cube.AttributeMemberProperty, let's take a look at the M code behind the function. Here's the code:


let

Source = AnalysisServices.Database(connection, cube),

GetAttributeMemberProperty =

(attributeName as text, memberName as text, propertyName as text, optional culture as nullable text) =>

let

attribute = Source{[Name=attributeName]}[Content],

member = attribute{[Key=memberName]}[Member],

property = member{[Name=propertyName]}[Value]

in

if culture = null then property else Text.Culture(property, culture)

in

GetAttributeMemberProperty


Let's break this code down into its individual parts.

Step 1: Connect to the Cube

The first step of the M code is to connect to the cube using the AnalysisServices.Database function. This function takes the connection and cube parameters that were passed to Cube.AttributeMemberProperty and returns a table containing the cube's metadata. Here's the code:


Source = AnalysisServices.Database(connection, cube)


Step 2: Define the GetAttributeMemberProperty Function

The next step of the M code is to define the GetAttributeMemberProperty function. This function takes four parameters: attributeName, memberName, propertyName, and culture (which is optional). It then extracts the specified property from the member attribute and returns it. Here's the code:


GetAttributeMemberProperty =

(attributeName as text, memberName as text, propertyName as text, optional culture as nullable text) =>

let

attribute = Source{[Name=attributeName]}[Content],

member = attribute{[Key=memberName]}[Member],

property = member{[Name=propertyName]}[Value]

in

if culture = null then property else Text.Culture(property, culture)


Let's break this code down into its individual parts.

#### Step 2.1: Extract the Attribute

In the first step of the GetAttributeMemberProperty function, we extract the member attribute table from the cube's metadata. We do this by using the Source table we defined earlier and filtering it by the attributeName parameter. Here's the code:


attribute = Source{[Name=attributeName]}[Content]


#### Step 2.2: Extract the Member

In the second step of the GetAttributeMemberProperty function, we extract the member table from the member attribute table. We do this by using the attribute table we defined earlier and filtering it by the memberName parameter. Here's the code:


member = attribute{[Key=memberName]}[Member]


#### Step 2.3: Extract the Property

In the third step of the GetAttributeMemberProperty function, we extract the specified property from the member table. We do this by using the member table we defined earlier and filtering it by the propertyName parameter. Here's the code:


property = member{[Name=propertyName]}[Value]


#### Step 2.4: Optional Culture Parameter

Finally, in the last step of the GetAttributeMemberProperty function, we check if an optional culture parameter was passed to the function. If it was, we use the Text.Culture function to format the property value using the specified culture. If not, we simply return the property value as is. Here's the code:


if culture = null then property else Text.Culture(property, culture)


Step 3: Return the GetAttributeMemberProperty Function

The final step of the M code is to return the GetAttributeMemberProperty function. This function can now be used to retrieve any member attribute property from the cube. Here's the code:


in

GetAttributeMemberProperty


Using Cube.AttributeMemberProperty in Practice

Now that we understand the M code behind Cube.AttributeMemberProperty, let's take a look at some examples of how to use this function in practice.

Example 1: Retrieve the Name of a Member Attribute

Suppose we have a cube that contains a member attribute called "Region" in the "Customers" dimension. We can use Cube.AttributeMemberProperty to retrieve the name of this attribute for a specific member, such as "West". Here's the code:


Cube.AttributeMemberProperty(

“MyDataConnection”,

“MyCube”,

“Customers”,

“Region”,

“West”,

“Name”)


This code will return the string "Region".

Example 2: Retrieve the Caption of a Member Attribute

Suppose we want to retrieve the caption of the "Region" attribute instead of its name. We can do this by passing the "Caption" parameter to Cube.AttributeMemberProperty instead of "Name". Here's the code:


Cube.AttributeMemberProperty(

“MyDataConnection”,

“MyCube”,

“Customers”,

“Region”,

“West”,

“Caption”)


This code will return the string "Sales Region".

Example 3: Retrieve a Member Attribute Property in a Different Culture

Suppose we want to retrieve the "Name" property of the "Region" attribute in French instead of English. We can do this by passing the "fr-FR" culture parameter to Cube.AttributeMemberProperty. Here's the code:


Cube.AttributeMemberProperty(

“MyDataConnection”,

“MyCube”,

“Customers”,

“Region”,

“West”,

“Name”,

“fr-FR”)


This code will return the string "Région".

In this article, we've explored the M code behind the Power Query M function Cube.AttributeMemberProperty. We've seen how this function works behind the scenes and how to use it to retrieve a specified property of a member attribute in a cube. We've also seen some examples of how to use this function in practice. Hopefully, this article has given you a deeper understanding of how Cube.AttributeMemberProperty works and how to use it in your own Power Query projects.

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)

Copyright, G Com Solutions Ltd, 2024.
Tower Bridge Business Centre, 46-48 East Smithfield, London E1W 1AW
0800 998 9248   |   9:00 a.m. till 5:30 p.m.

Connect With Me: