Build a Power Query template connector
Microsoft Cloud for Sustainability
Important
Some or all of this functionality is available as part of a preview release. The content and the functionality are subject to change.
This article shows partners how to create a Power Query template connector for Microsoft Sustainability Manager. To successfully build a Power Query template connector, you must know some basic solution development for Dynamics 365 and how to build a Power Query template in Sustainability Manager.
If you need to build a Power Automate connector, go to Build a Power Automate connector.
Step 1: Create an environment
In this step, you create a solution to distribute to Sustainability Manager users who need to import data with your connector.
Create a new unmanaged solution in Power Apps by following the instructions in Create a solution.
Install Sustainability Manager into that environment.
Step 2: Create a Power Query template
To create the Power Query template for your connector, follow the instructions in Design a Power Query template.
Step 3: Prepare your solution
Now that you have an environment with Sustainability Manager installed and have created a Power Query template, you can package the Power Query template into a connector solution.
At a minimum, the following folder structure in your unpacked solution should contain:
- Folder: Assets
- File: msdyn_dataconnectors.xml
- File: msdyn_dataflowtemplates.xml
- Folder: Other
- File: Customizations.xml
- File: Solution.xml
- File: <solution>.cdsproj
The files under Assets contain the necessary information for housing your Power Query template connector. The msdyn_dataconnectors.xml file contains important metadata about your connector. After it's installed, it determines how your connector appears in Sustainability Manager. The msdyn_dataflowtemplates.xml file contains information about your Power Query template, including all of the transformations that your Power Query template contains. You can use these files to create one or more connectors within a single solution.
Follow the instructions in this section to create each file.
Create the msdyn_dataconnectors.xml file
In your preferred text editor, create a new file named msdyn_dataconnectors.xml.
Copy and modify the following XML to fit your details:
<msdyn_dataconnectors> <msdyn_dataconnector msdyn_dataconnectorid="bc9e9b1e-45fa-4d6e-b799-9dcb49aaf981"> <msdyn_connectorstatus>700610001</msdyn_connectorstatus> <msdyn_connectortype>700610007</msdyn_connectortype> <msdyn_description>Demo Power Query templates connector</msdyn_description> <msdyn_supportlinkurl>https://www.microsoft.com</msdyn_supportlinkurl> <msdyn_logo_text> </msdyn_logo_text> <msdyn_name>Demo Power Query templates connector</msdyn_name> <msdyn_title>Demo Power Query templates connector</msdyn_title> <msdyn_dataflowtemplateid> <msdyn_dataflowtemplateid>fb6ee8dd-b84c-4599-adb6-1812b3878052</msdyn_dataflowtemplateid> </msdyn_dataflowtemplateid> </msdyn_dataconnector> </msdyn_dataconnectors>Parameter Details msdyn_dataconnectorid Generate a new GUID to identify your connector, adhering to GUID format. In our example, we use GUID = bc9e9b1e-45fa-4d6e-b799-9dcb49aaf981. Your GUID is your connector identifier, which you need in later steps. msdyn_connectorstatus Leave this value at 700610001. This value represents a published connector. msdyn_connectortype Leave this value at 700610007. This value represents the type of connector. In this case, it's the value for a Power Query template connector. msdyn_description Add a brief description for your connector. msdyn_supportlinkurl Replace the text with the URL you want your users to reach out to if the connection fails. For information about testing failed connections, go to Test for error handling. Your connector won't show up in Sustainability Manager without this URL. msdyn_logo_text Use an encoder of your choice to encode and save your logo image to Base 64. The image should be 36 x 36 px. Paste the base 64 encoding in this parameter. msdyn_name msdyn_title Change Demo Power Query templates connector to the name of your connector. msdyn_dataflowtemplateid Change this value to the one for your Power Query template. This value is a GUID that you also use in the msdyn_dataflowtemplates.xml file.
Create the msdyn_dataflowtemplates.xml file
In your preferred text editor, create a new file named msdyn_dataflowtemplates.xml.
Copy and modify the following XML to fit your details:
<msdyn_dataflowtemplates> <msdyn_dataflowtemplate msdyn_dataflowtemplateid="fb6ee8dd-b84c-4599-adb6-1812b3878052"> <msdyn_uniquename>msm_sustainabilitySamplePQTemplate</msdyn_uniquename> <msdyn_category>msdyn_sustainability</msdyn_category> <msdyn_configuration>{\"msdyn_sustainability\":{\"dataDefintions\":\"bd182f7f-1b7b-4360-9c02-2d0b7cd75b97, 4b1faadb-0ee4-42c8-a3fc-e3afa3987172, c4f9c6f5-dc68-49ca-8297-acbb29fece79, 6c36ce29-f121-47d6-b146-9eece7e869ec\",\"dataDefintionNames\":\"6. Business travel, Facilities, Organizational units, Business travel types\",\"map\":{\"Business Travel\":\"{\\\"fields\\\":[{\\\"destinationField\\\":\\\"msdyn_businesstraveltypeid\\\",\\\"sourceField\\\":\\\"Business Travel Type\\\",\\\"isCustomDimension\\\":false},{\\\"destinationField\\\":\\\"msdyn_consumptionstartdate\\\",\\\"sourceField\\\":\\\"Consumption Start Date\\\",\\\"isCustomDimension\\\":false},{\\\"destinationField\\\":\\\"msdyn_consumptionenddate\\\",\\\"sourceField\\\":\\\"Consumption End Date\\\",\\\"isCustomDimension\\\":false},{\\\"destinationField\\\":\\\"msdyn_name\\\",\\\"sourceField\\\":\\\"Name\\\",\\\"isCustomDimension\\\":false},{\\\"destinationField\\\":\\\"msdyn_description\\\",\\\"sourceField\\\":\\\"Description\\\",\\\"isCustomDimension\\\":false},{\\\"destinationField\\\":\\\"msdyn_facilityid\\\",\\\"sourceField\\\":\\\"Facility\\\",\\\"isCustomDimension\\\":false},{\\\"destinationField\\\":\\\"msdyn_cost\\\",\\\"sourceField\\\":\\\"Cost\\\",\\\"isCustomDimension\\\":false},{\\\"destinationField\\\":\\\"msdyn_costunit\\\",\\\"sourceField\\\":\\\"Cost Unit\\\",\\\"isCustomDimension\\\":false},{\\\"destinationField\\\":\\\"msdyn_distance\\\",\\\"sourceField\\\":\\\"Distance\\\",\\\"isCustomDimension\\\":false},{\\\"destinationField\\\":\\\"msdyn_distanceunit\\\",\\\"sourceField\\\":\\\"Distance Unit\\\",\\\"isCustomDimension\\\":false},{\\\"destinationField\\\":\\\"msdyn_organizationalunitid\\\",\\\"sourceField\\\":\\\"Organizational unt\\\",\\\"isCustomDimension\\\":false},{\\\"destinationField\\\":\\\"msdyn_dataqualitytype\\\",\\\"sourceField\\\":\\\"Data Quality\\\",\\\"isCustomDimension\\\":false},{\\\"sourceField\\\":\\\"Production value\\\",\\\"destinationField\\\":\\\"Production Amplifier\\\",\\\"isCustomDimension\\\":true}]}\",\"biz trav types\":\"{\\\"fields\\\":[{\\\"destinationField\\\":\\\"msdyn_name\\\",\\\"sourceField\\\":\\\"Name\\\",\\\"isCustomDimension\\\":false},{\\\"destinationField\\\":\\\"msdyn_description\\\",\\\"sourceField\\\":\\\"Description\\\",\\\"isCustomDimension\\\":false},{\\\"destinationField\\\":\\\"msdyn_origincorrelationid\\\",\\\"sourceField\\\":\\\"Origin correlation id\\\",\\\"isCustomDimension\\\":false}]}\",\"orgs\":\"{\\\"fields\\\":[{\\\"destinationField\\\":\\\"msdyn_name\\\",\\\"sourceField\\\":\\\"Name\\\",\\\"isCustomDimension\\\":false},{\\\"destinationField\\\":\\\"msdyn_organizationalunittype\\\",\\\"sourceField\\\":\\\"Organizational unit type\\\",\\\"isCustomDimension\\\":false},{\\\"destinationField\\\":\\\"msdyn_addressstreet1\\\",\\\"sourceField\\\":\\\"Address\\\",\\\"isCustomDimension\\\":false}]}\",\"facilities\":\"{\\\"fields\\\":[{\\\"destinationField\\\":\\\"msdyn_name\\\",\\\"sourceField\\\":\\\"Name\\\",\\\"isCustomDimension\\\":false},{\\\"destinationField\\\":\\\"msdyn_addresscity\\\",\\\"sourceField\\\":\\\"City\\\",\\\"isCustomDimension\\\":false},{\\\"destinationField\\\":\\\"msdyn_addresscountryisocode\\\",\\\"sourceField\\\":\\\"Country/Region\\\",\\\"isCustomDimension\\\":false},{\\\"destinationField\\\":\\\"msdyn_addresszippostalcode\\\",\\\"sourceField\\\":\\\"Zip/Postal\\\",\\\"isCustomDimension\\\":false},{\\\"destinationField\\\":\\\"msdyn_addressstreet1\\\",\\\"sourceField\\\":\\\"Address \\\",\\\"isCustomDimension\\\":false}]}\"},\"mapSdds\":{\"Business Travel\":\"bd182f7f-1b7b-4360-9c02-2d0b7cd75b97\",\"biz trav types\":\"4b1faadb-0ee4-42c8-a3fc-e3afa3987172\",\"orgs\":\"c4f9c6f5-dc68-49ca-8297-acbb29fece79\",\"facilities\":\"6c36ce29-f121-47d6-b146-9eece7e869ec\"}}}</msdyn_configuration> <msdyn_description>Demo Power Query template connector</msdyn_description> <msdyn_mashupdocument>{\"hostContext\":{\"type\":\"SustainabilityCloud\",\"details\":{\"EnvironmentId\":\"b9fd4065-c444-e187-a1b1-5ddeb9c39a1a\"}},\"mashupName\":\"Sustainability--04000419112812547\",\"mashupDocument\":\"section Section1;\\r\\nshared #\\\"Business Travel\\\" = let\\n Source = Excel.Workbook(Web.Contents(\\\"contoso/Documents/Apps/Microsoft Power Query/Uploaded Files/business travel 9.xlsx\\\"), null, true),\\n #\\\"Navigation 1\\\" = Source{[Item = \\\"Business Travel\\\", Kind = \\\"Sheet\\\"]}[Data],\\n #\\\"Promoted headers\\\" = Table.PromoteHeaders(#\\\"Navigation 1\\\", [PromoteAllScalars = true]),\\n #\\\"Changed column type\\\" = Table.TransformColumnTypes(#\\\"Promoted headers\\\", {{\\\"Business Travel Type\\\", type text}, {\\\"Consumption Start Date\\\", type datetime}, {\\\"Consumption End Date\\\", type datetime}, {\\\"Data Quality\\\", type text}, {\\\"Name\\\", type text}, {\\\"Organizational unt\\\", type text}, {\\\"Description\\\", type text}, {\\\"Facility\\\", type text}, {\\\"Cost\\\", Int64.Type}, {\\\"Cost Unit\\\", type text}, {\\\"Distance\\\", Int64.Type}, {\\\"Distance Unit\\\", type text}, {\\\"Traveler\\\", type text}, {\\\"Is Good use of money\\\", type text}, {\\\"Budget\\\", Int64.Type}, {\\\"Had Fun\\\", type text}, {\\\"Personal Note\\\", type text}, {\\\"Production value\\\", Int64.Type}}),\\n #\\\"Filtered rows\\\" = Table.SelectRows(#\\\"Changed column type\\\", each ([Traveler] = FullName))\\nin\\n #\\\"Filtered rows\\\";\\r\\nshared #\\\"biz trav types\\\" = let\\r\\n Source = Excel.Workbook(Web.Contents(\\\"contoso/Documents/Apps/Microsoft Power Query/Uploaded Files/business travel 9.xlsx\\\"), null, true),\\r\\n #\\\"Navigation 1\\\" = Source{[Item = \\\"biz trav types\\\", Kind = \\\"Sheet\\\"]}[Data],\\r\\n #\\\"Changed column type\\\" = Table.TransformColumnTypes(#\\\"Navigation 1\\\", {{\\\"Column1\\\", type text}, {\\\"Column2\\\", type text}, {\\\"Column3\\\", type text}}),\\r\\n #\\\"Promoted headers\\\" = Table.PromoteHeaders(#\\\"Changed column type\\\", [PromoteAllScalars = true])\\r\\nin\\r\\n #\\\"Promoted headers\\\";\\r\\nshared orgs = let\\r\\n Source = Excel.Workbook(Web.Contents(\\\"https://contoso/Documents/Apps/Microsoft Power Query/Uploaded Files/business travel 9.xlsx\\\"), null, true),\\r\\n #\\\"Navigation 1\\\" = Source{[Item = \\\"orgs\\\", Kind = \\\"Sheet\\\"]}[Data],\\r\\n #\\\"Changed column type\\\" = Table.TransformColumnTypes(#\\\"Navigation 1\\\", {{\\\"Column1\\\", type text}, {\\\"Column2\\\", type text}, {\\\"Column3\\\", type text}}),\\r\\n #\\\"Promoted headers\\\" = Table.PromoteHeaders(#\\\"Changed column type\\\", [PromoteAllScalars = true])\\r\\nin\\r\\n #\\\"Promoted headers\\\";\\r\\nshared facilities = let\\r\\n Source = Excel.Workbook(Web.Contents(\\\"https://contoso/Documents/Apps/Microsoft Power Query/Uploaded Files/business travel 9.xlsx\\\"), null, true),\\r\\n #\\\"Navigation 1\\\" = Source{[Item = \\\"facilities\\\", Kind = \\\"Sheet\\\"]}[Data],\\r\\n #\\\"Promoted headers\\\" = Table.PromoteHeaders(#\\\"Navigation 1\\\", [PromoteAllScalars = true]),\\r\\n #\\\"Changed column type\\\" = Table.TransformColumnTypes(#\\\"Promoted headers\\\", {{\\\"Name\\\", type text}, {\\\"Address \\\", type text}, {\\\"City\\\", type text}, {\\\"Country/Region\\\", type text}, {\\\"Zip/Postal\\\", Int64.Type}, {\\\"IsLeased\\\", type text}}),\\r\\n #\\\"Renamed columns\\\" = Table.RenameColumns(#\\\"Changed column type\\\", {{\\\"Address \\\", \\\"Address\\\"}})\\r\\nin\\r\\n #\\\"Renamed columns\\\";\\r\\n[Description = \\\"first name last name of the user doing the business travel\\\"]\\r\\nshared FullName = \\\"Stanko Gutalj\\\" meta [IsParameterQuery = true, IsParameterQueryRequired = true, Type = type any];\\r\\n\",\"queryGroups\":[],\"documentLocale\":\"en-US\",\"gatewayObjectId\":null,\"queriesMetadata\":{\"Business Travel\":{\"queryId\":\"c7b76ec4-8e29-42d1-b63d-7710f85a7d4b\",\"queryName\":\"Business Travel\",\"queryGroupId\":null,\"entityName\":null,\"lastKnownIsCalculatedEntity\":false,\"lastKnownIsLinkedEntity\":false,\"lastKnownIsParameter\":false,\"isHidden\":false,\"lastKnownResultTypeName\":\"table\",\"loadEnabled\":true,\"fieldsMetadata\":{},\"deleteExistingDataOnLoad\":false,\"hostProperties\":{},\"jsonOutputDestinations\":null,\"bindToDefaultOutputDestination\":null,\"jsonIncrementalRefreshSettings\":null,\"stagingDefinition\":null},\"biz trav types\":{\"queryId\":\"0c8434c7-29c6-4f7b-a5f5-b5791604061d\",\"queryName\":\"biz trav types\",\"queryGroupId\":null,\"entityName\":null,\"lastKnownIsCalculatedEntity\":false,\"lastKnownIsLinkedEntity\":false,\"lastKnownIsParameter\":false,\"isHidden\":false,\"lastKnownResultTypeName\":\"table\",\"loadEnabled\":true,\"fieldsMetadata\":{},\"deleteExistingDataOnLoad\":false,\"hostProperties\":{},\"jsonOutputDestinations\":null,\"bindToDefaultOutputDestination\":null,\"jsonIncrementalRefreshSettings\":null,\"stagingDefinition\":null},\"orgs\":{\"queryId\":\"94756842-bfc6-4459-9703-627d2f95594a\",\"queryName\":\"orgs\",\"queryGroupId\":null,\"entityName\":null,\"lastKnownIsCalculatedEntity\":false,\"lastKnownIsLinkedEntity\":false,\"lastKnownIsParameter\":false,\"isHidden\":false,\"lastKnownResultTypeName\":\"table\",\"loadEnabled\":true,\"fieldsMetadata\":{},\"deleteExistingDataOnLoad\":false,\"hostProperties\":{},\"jsonOutputDestinations\":null,\"bindToDefaultOutputDestination\":null,\"jsonIncrementalRefreshSettings\":null,\"stagingDefinition\":null},\"facilities\":{\"queryId\":\"87485e4a-ecdb-4617-9c8c-a7c96a2092f7\",\"queryName\":\"facilities\",\"queryGroupId\":null,\"entityName\":null,\"lastKnownIsCalculatedEntity\":false,\"lastKnownIsLinkedEntity\":false,\"lastKnownIsParameter\":false,\"isHidden\":false,\"lastKnownResultTypeName\":\"table\",\"loadEnabled\":true,\"fieldsMetadata\":{},\"deleteExistingDataOnLoad\":false,\"hostProperties\":{},\"jsonOutputDestinations\":null,\"bindToDefaultOutputDestination\":null,\"jsonIncrementalRefreshSettings\":null,\"stagingDefinition\":null},\"FullName\":{\"queryId\":\"488f10a8-9f01-4674-a004-c8a6493785a6\",\"queryName\":\"FullName\",\"queryGroupId\":null,\"entityName\":null,\"lastKnownIsCalculatedEntity\":false,\"lastKnownIsLinkedEntity\":false,\"lastKnownIsParameter\":true,\"isHidden\":false,\"lastKnownResultTypeName\":\"text\",\"loadEnabled\":false,\"fieldsMetadata\":{},\"deleteExistingDataOnLoad\":false,\"hostProperties\":{},\"jsonOutputDestinations\":null,\"bindToDefaultOutputDestination\":null,\"jsonIncrementalRefreshSettings\":null,\"stagingDefinition\":null}},\"connectionOverrides\":[{\"path\":\"https://contoso/Documents/Apps/Microsoft Power Query/Uploaded Files/business travel 9.xlsx\",\"kind\":\"Web\",\"provider\":\"PowerApps\",\"environmentName\":\"b9fd4065-c444-e187-a1b1-5ddeb9c39a1a\",\"apiName\":\"webcontents\",\"connectionName\":\"926cadb147e044fa9da7cffce4f70e82\",\"credentialDetails\":{},\"connectionParameters\":{}}],\"trustedConnections\":null,\"useHostConnectionProvider\":false,\"fastCombine\":false,\"allowNativeQueries\":false,\"allowedModules\":null,\"skipAutomaticTypeAndHeaderDetection\":false,\"disableAutoAnonymousConnectionUpsert\":false,\"hostProperties\":null,\"defaultOutputDestinationConfiguration\":null,\"stagingDefinition\":null}</msdyn_mashupdocument> <msdyn_templatename>Demo Power Query template connector</msdyn_templatename> <msdyn_templateversion>1.0</msdyn_templateversion> </msdyn_dataflowtemplate> </msdyn_dataflowtemplates>Parameter Details msdyn_dataflowtemplateid Generate a new GUID to identify your template, adhering to GUID format. In our example, we use GUID = fb6ee8dd-b84c-4599-adb6-1812b3878052. msdyn_uniquename Enter a unique name for your template record. msdyn_category The category of your template record. Leave this value as is. msdyn_configuration Change this to the JSON that represents the mapping configuration of your template to the data categories supported in Sustainability Manager. Instructions for obtaining this JSON are provided after this table. msdyn_description Enter a description for your Power Query template. msdyn_mashupdocument This parameter is a text representation of all the transformations in your connector. Instructions for obtaining this text are provided after this table. msdyn_templatename Enter a name for your template. msdyn_templateversion Enter the version number of your template. To extract the information to use in the msdyn_configuration and msdyn_mashupdocument parameters, copy and paste the following text in your browser. Replace <your_organization> with the name of your organization's environment.
https://<your_organization>.crm10.dynamics.com/api/data/v9.0/msdyn_dataflowtemplatesA JSON displays with all of your msdyn_dataflowtemplates records, where you can find the template you want to move. Copy and paste the fields to your msdyn_dataflowtemplates.xml file.
