Cdm.Contents

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Cdm.Contents

In this article, we’ll explore the M code behind the Cdm.Contents function and look at how it works.

What is Common Data Model (CDM)?

Before we dive into the M code behind Cdm.Contents, let’s first understand what Common Data Model (CDM) is.

CDM is a standardized, extensible, and semantic data model that is used to simplify the process of integrating data from various sources. It defines entities, attributes, relationships, and semantic metadata for data integration. CDM also provides a consistent and well-documented way of representing data, making it easier to share and integrate data across different systems.

CDM is used by various Microsoft services like Power Apps, Power BI, Dynamics 365, and Azure Data Factory.

Understanding the M Code Behind Cdm.Contents

The Cdm.Contents function is used to connect to a CDM folder and retrieve data from it. Let’s break down the M code behind this function and understand how it works.

Cdm.Contents(path as text, optional options as nullable record) as table

The Cdm.Contents function takes two arguments:

1. path: This is the path of the CDM folder that you want to connect to. It is a required argument and should be specified as a text string.

2. options: This is an optional argument that allows you to specify additional options for the connection. It should be specified as a record.

Now let's take a closer look at the Cdm.Contents function.

Step 1: Establish a Connection

The first step in the Cdm.Contents function is to establish a connection to the CDM folder specified in the path argument. This is done using the following code:

let

source = Cdm.Contents(path, options)

in

source

Here, we're using the let...in syntax to create a variable called source that contains the result of the Cdm.Contents function.

Step 2: Retrieve Data

The next step is to retrieve data from the CDM folder. This is done using the Value.NativeExpression function, which allows us to execute a native query against the data source. The M code for this step looks like this:

let

source = Cdm.Contents(path, options),

data = Value.NativeExpression(source[Data], “SELECT FROM [Entity]”)

in

data

Here, we're using the Value.NativeExpression function to execute a SQL query against the Data property of the source variable. This query retrieves all the data from the Entity table in the CDM folder.

Step 3: Transform Data

Once we have retrieved the data, we can apply transformations to it using the Power Query editor. This is where we can clean, transform, and shape the data to meet our requirements.

Step 4: Load Data

The final step is to load the transformed data into a destination. This can be a table or a database, depending on your requirements. The M code for this step looks like this:

let

source = Cdm.Contents(path, options),

data = Value.NativeExpression(source[Data], “SELECT FROM [Entity]”),

#”Transformed Data” = //Transformations here

#”Loaded Data” = //Load data to destination

in

#”Loaded Data”

In this article, we've explored the M code behind the Cdm.Contents function in Power Query. We've seen how this function is used to connect to a Common Data Model (CDM) folder and retrieve data from it. We've also looked at the steps involved in using this function, including establishing a connection, retrieving data, transforming data, and loading data into a destination.

By understanding the M code behind the Cdm.Contents function, you can gain a deeper understanding of how Power Query works and how you can use it to integrate data from various sources.

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)