Salesforce.Data

D

Back to top

T

Back to top

#

Back to top

The M Code Behind the Power Query M function Salesforce.Data

What is M Code?

M code is the language used by the Power Query formula language, which is used to transform and manipulate data within the Power Query Editor. It’s a functional language that allows you to perform a wide range of data transformation tasks, from basic filtering and sorting to complex data modeling and analysis.

In the case of Salesforce.Data, the M code is used to establish a connection to the Salesforce API, retrieve data from Salesforce objects, and perform any necessary transformations on that data. Let’s take a closer look at the M code behind this powerful function.

Connecting to the Salesforce API

The first step in using Salesforce.Data is to establish a connection to the Salesforce API. This is accomplished using the following M code:

let

Source = Salesforce.Data(“https://login.salesforce.com”, [ApiVersion=51]),

#”Authenticated” = Source{[Name=”Authenticated”]}[Data]

in

#”Authenticated”

This code creates a new query using the Salesforce.Data function, which takes two arguments: the URL of the Salesforce login page and the API version to be used (in this case, version 51).

Once the query is created, the code uses the #"Authenticated" step to retrieve the authentication token from Salesforce. This token is required for all subsequent API requests and is stored in memory for the duration of the query.

Retrieving Data from Salesforce Objects

Once you've established a connection to the Salesforce API, you can start retrieving data from Salesforce objects. This is done using the following M code:

let

Source = Salesforce.Data(“https://login.salesforce.com”, [ApiVersion=51]),

#”Authenticated” = Source{[Name=”Authenticated”]}[Data],

#”Account” = #”Authenticated”{[Name=”Account”]}[Data]

in

#”Account”

This code creates a new query based on the previous one and uses the #"Account" step to retrieve data from the Account object in Salesforce.

To retrieve data from a different object, simply replace "Account" with the name of the desired object.

Performing Transformations on Salesforce Data

Once you've retrieved data from a Salesforce object, you can perform any necessary transformations on that data using the full power of the Power Query M language. For example, you might want to filter the data based on certain criteria, remove duplicate values, or calculate new columns based on existing data.

Here's an example of how you might filter the data to only include accounts with a certain account rating:

let

Source = Salesforce.Data(“https://login.salesforce.com”, [ApiVersion=51]),

#”Authenticated” = Source{[Name=”Authenticated”]}[Data],

#”Account” = #”Authenticated”{[Name=”Account”]}[Data],

#”Filtered Rows” = Table.SelectRows(#”Account”, each [AccountRating] = “Hot”)

in

#”Filtered Rows”

This code creates a new query based on the previous one and uses the Table.SelectRows function to filter the data to only include accounts with an account rating of "Hot".

The Power Query M function Salesforce.Data is a powerful tool for connecting to and retrieving data from Salesforce. By understanding the M code behind this function, you can unleash its full potential and perform even more complex data transformations and analysis. With the right knowledge and skills, the possibilities are endless.

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)