Import data with Power Query templates

Article
05/15/2024

Microsoft Sustainability Manager provides Power Query templates to help you with Power Query data imports. They automatically provide the right structure for all data attributes required to successfully import your data with Power Query.

These templates save you time and reduce the risk of errors and data misalignments. They also help users with different levels of experience to create Power Query data imports. For example, if you're an advanced Power Query user, you can set up a Power Query template that other sustainability practitioners can use for data imports.

Note

Power Query templates are only available within the Sustainability Manager where you created them, and you can't import or export them.

Prerequisites

To design a Power Query template, you need:

Sustainability Manager read/write role
Proficiency with Power Query parameters and the advanced editor

To apply a Power Query template, you need the Sustainability Ingestion role.

Design a Power Query template

Follow these steps to create a Power Query template for use within your organization. You can find more details about each step in Import data with Power Query.

In Sustainability Manager, on the left navigation under Data, select Imports.
On the next page, select the Power Query templates tab. On the top menu, select + New.
On the Define your data page, in the Sources column, select a source, and then select Add next to each category that you want to include in your import. You can select other sources and add categories from them, too. If you want to remove a category, select the X next to it on the Data tab in the Data transformation tools pane on the right. Select Next to continue.
Next, you select a data source and set up parameters for the template.
1. On the Manage data source page, select a data source. This example shows how to connect to an Azure SQL database.
2. Enter the necessary information under Connection settings, and then select Next.
3. Under Choose data, select the data you want connect to.
4. On the Manage parameters window, specify the Power Query parameters. These parameters display as fields that users must complete when they start an import with this Power Query template.
  
  Note
  
  This parameterized experience requires advanced Power Query knowledge of how to use parameters.
5. Use the advanced editor to author usage of the defined parameters.
6. Connect to the source and create the template.
On the Map data page, map your data, and then select Next.

Note

These Power Query templates are designed specifically for Sustainability Manager. They include attribute mapping and Sustainability Manager connection management.
On the Review and name page, provide a name and description for your Power Query template, and then select Next. Your new template appears under Data templates on the Power Query templates tab.

Apply a Power Query template

After you or someone else in your organization creates a Power Query template, you can use it for initiating a Power Query data import. For more details about these steps, go to Edit a Power Query import.

In Sustainability Manager, on the left navigation under Data, select Imports.
On the Data imports tab, on the top menu, select + New.
Select the POWER QUERY TEMPLATE tile.
On the Choose a template page, select the Power Query template you want to use.
On the Manage data source page, enter the parameters to use for your import. In the following example, these parameters include:
- ServerURL
- DBName
- TableName
- OldProvider
- NewProvider
On the Schedule data import page, review the options and change them if necessary. Select Next.
On the Review and name page, provide a name and description for your import. Turn the Ready to import toggle to Yes, and then select Connect.
The connections run and import the data like any other import connection.