Import data with Power Query templates

Microsoft Cloud for Sustainability Tech Summit November 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:

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.

  1. In Sustainability Manager, on the left navigation under Data, select Imports.

  2. On the next page, select the Power Query templates tab. On the top menu, select + New.

  3. 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.

    Screenshot showing where to select entities for export.

  4. 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.

      Screenshot showing how to select a data source.

    2. Enter the necessary information under Connection settings, and then select Next.

      Screenshot showing how to enter connection settings for an Azure SQL database.

    3. Under Choose data, select the data you want connect to.

      Screenshot showing how to select a data source from an Azure SQL database.

    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.

      Screenshot showing how to set up parameters.

      Screenshot showing how to replace values.

    5. Use the advanced editor to author usage of the defined parameters.

      Screenshot showing how to use the advanced editor.

    6. Connect to the source and create the template.

      Screenshot showing how to create the template.

  5. 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.

    Screenshot showing how to map the data.

  6. 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.

    Screenshot showing how to name and save your template.

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.

  1. In Sustainability Manager, on the left navigation under Data, select Imports.

  2. On the Data imports tab, on the top menu, select + New.

  3. Select the POWER QUERY TEMPLATE tile.

  4. On the Choose a template page, select the Power Query template you want to use.

  5. 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

    Screenshot showing an example of entering parameters during a Power Query template import.

  6. On the Schedule data import page, review the options and change them if necessary. Select Next.

  7. 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.

  8. The connections run and import the data like any other import connection.