Marketo

Power BI is a business analytics service provided by Microsoft that allows users to create interactive visualizations and business intelligence reports. One of the popular data sources for Power BI is Marketo, a marketing automation software platform. In this article, we will explore how to use Power Query M Language code to connect to the Marketo data source from inside Power BI.

What is Power Query M Language?

Power Query M Language is the formula language used in Power Query, which is a data transformation and data preparation tool in Power BI. Power Query M Language is used to create queries that extract data from various sources, transform the data, and load it into a data model in Power BI. Power Query M Language is a functional programming language, which means that functions are the building blocks of code in Power Query M Language.

Connecting to Marketo Data Source from Power BI

To connect to the Marketo data source from Power BI, we need to follow these steps:

1. Open Power BI desktop

2. Navigate to Home > Get Data

3. Select Marketo from the list of available data sources

4. Enter the Marketo credentials and click Connect

5. Select the desired data from the Marketo data source

6. Load the data into Power BI

The above steps are the traditional way of connecting to a data source in Power BI. However, sometimes we need to use Power Query M Language code to connect to a data source. The following section explains how to use Power Query M Language code to connect to the Marketo data source from inside Power BI.

Using Power Query M Language Code to Connect to Marketo Data Source

To use Power Query M Language code to connect to the Marketo data source from inside Power BI, we need to follow these steps:

1. Open Power BI desktop

2. Navigate to Home > New Source > Blank Query

3. In the formula bar, enter the following code:

---

let

Source = Json.Document(Web.Contents(“https://Marketo-instance>/rest/v1/leads.json?access_token=&fields=id,firstName,lastName,email,createdAt,updatedAt&maxReturn=10000″)),

#”Converted to Table” = Record.ToTable(Source),

#”Expanded Value” = Table.ExpandRecordColumn(#”Converted to Table”, “Value”, {“id”, “firstName”, “lastName”, “email”, “createdAt”, “updatedAt”}, {“Value.id”, “Value.firstName”, “Value.lastName”, “Value.email”, “Value.createdAt”, “Value.updatedAt”})

in

#”Expanded Value”

---

4. Replace `Marketo-instance>` and `` with your Marketo instance name and access token respectively

5. Click Done

6. Load the data into Power BI

The above code uses the Marketo REST API to retrieve the lead data and transform it into a table format. The code retrieves the lead data in JSON format and converts it into a table using the Record.ToTable function. Then, it expands the Value column, which contains the lead data, into individual columns using the Table.ExpandRecordColumn function.

Conclusion

In this article, we explored how to use Power Query M Language code to connect to the Marketo data source from inside Power BI. Power Query M Language is a powerful tool that allows users to customize their data transformation and data preparation workflows. By using Power Query M Language code to connect to the Marketo data source, users can have more control over the data retrieval and transformation process.

Power BI Training Courses by G Com Solutions (0800 998 9248)

Contact Us