Bygg en Power Query-malkobling

Artikkel
12/20/2024

Viktig!

Noen av eller alle disse funksjonene er tilgjengelig som en del av en forhåndsversjon. Innholdet i og funksjonaliteten kan endres.

Denne artikkelen viser partnere hvordan de kan opprette en Power Query-malkobling for Microsoft Sustainability Manager. Hvis du vil bygge en Power Query-malkobling, må du kjenne til grunnleggende løsningsutvikling for Dynamics 365 og hvordan du bygger en Power Query-mal i Sustainability Manager.

Hvis du må bygge en Power Automate-kobling, går du til Bygg en Power Automate-kobling.

Trinn 1: Opprett et miljø

I dette trinnet oppretter du en løsning som skal rulles ut til Sustainability Manager-brukere som må importere data med koblingen.

Opprett en ny uadministrert løsning i Power Apps ved å følge instruksjonene i Opprett en løsning.
Installer Sustainability Manager i det miljøet.

Trinn 2: Opprett en Power Query-mal

Hvis du vil opprette Power Query-malen for koblingen, følger du instruksjonene i Utform en Power Query-mal.

Trinn 3: Klargjør løsningen

Nå som du har et miljø med Sustainability Manager installert og har opprettet en Power Query-mal, kan du pakke Power Query-malen i en koblingsløsning.

Minimum bør følgende mappestruktur i løsningen som pakkes ut, inneholde følgende:

Mappe: ressurser
- Fil: msdyn_dataconnectors.xml
- Fil: msdyn_dataflowtemplates.xml
Mappe: annet
- Fil: Customizations.xml
- Fil: Solution.xml
Fil: <løsning>.cdsproj

Filene under Ressurser inneholder den nødvendige informasjonen for å lagre Power Query-malkoblingen. Filen msdyn_dataconnectors.xml inneholder viktige metadata om koblingen. Når den er installert, bestemmer den hvordan koblingen vises i Sustainability Manager. Filen msdyn_dataflowtemplates.xml inneholder informasjon om Power Query-malen, inkludert alle transformasjonene som Power Query-malen inneholder. Du kan bruke disse filene til å opprette en eller flere koblinger i én enkelt løsning.

Følg instruksjonene i denne delen for å opprette hver fil.

Opprett filen msdyn_dataconnectors.xml file

I det foretrukne tekstredigeringsprogrammet oppretter du en ny fil med navnet msdyn_dataconnectors.xml.

Kopier og endre følgende XML slik at den passer detaljene dine:

<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></msdyn_dataflowtemplateid>
  </msdyn_dataflowtemplateid>
  </msdyn_dataconnector>
</msdyn_dataconnectors>

Parameter	Detaljer
msdyn_dataconnectorid	Generer en ny GUID for å identifisere koblingen ved å gå til GUID-format. I eksemplet vårt bruker vi GUID = bc9e9b1e-45fa-4d6e-b799-9dcb49aaf981. GUID-en er koblingsidentifikatoren, som du trenger i senere trinn.
msdyn_connectorstatus	La denne verdien være 700610001. Denne verdien representerer en publisert kobling.
msdyn_connectortype	La denne verdien være 700610007. Denne verdien representerer typen kobling. I dette tilfellet er det verdien for en Power Query-malkobling.
msdyn_description	Legg til en kort beskrivelse av koblingen.
msdyn_supportlinkurl	Bytt ut teksten med nettadressen du vil at brukerne skal kontakte hvis tilkoblingen mislykkes. Hvis du vil ha informasjon om testing av mislykkede tilkoblinger, kan du gå til Test for feilhåndtering. Koblingen vises ikke i Sustainability Manager uten denne nettadressen.
msdyn_logo_text	Bruk en koder du velger, til å kode og lagre logobildet i Base 64. Bildet må være 36 x 36 piksler. Lim inn base 64-kodingen i denne parameteren.
msdyn_name msdyn_title	Endre Demo Power Query-malkobling til navnet på koblingen din.
msdyn_dataflowtemplateid	Endre denne verdien til en i Power Query-malen. Denne verdien er en GUID som du også bruker i filen msdyn_dataflowtemplates.xml.

Opprett filen msdyn_dataflowtemplates.xml file

I det foretrukne tekstredigeringsprogrammet oppretter du en ny fil med navnet msdyn_dataflowtemplates.xml.

Kopier og endre følgende XML slik at den passer detaljene dine:

<msdyn_dataflowtemplates>
  <msdyn_dataflowtemplate msdyn_dataflowtemplateid="">
    <msdyn_uniquename>msm_sustainabilitySamplePQTemplate</msdyn_uniquename>
    <msdyn_category>msdyn_sustainability</msdyn_category>
    <msdyn_configuration>{\"msdyn_sustainability\":{\"dataDefinitions\":\", , , \",\"dataDefinitionNames\":\"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\":\"aaaa0000-bb11-2222-33cc-444444dddddd\",\"biz trav types\":\"bbbb1111-cc22-3333-44dd-555555eeeeee\",\"orgs\":\"cccc2222-dd33-4444-55ee-666666ffffff\",\"facilities\":\"dddd3333-ee44-5555-66ff-777777aaaaaa\"}}}</msdyn_configuration>
    <msdyn_description>Demo Power Query template connector</msdyn_description>
    <msdyn_mashupdocument>{\"hostContext\":{\"type\":\"SustainabilityCloud\",\"details\":{\"EnvironmentId\":\"\"}},\"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\":\"\",\"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	Detaljer
msdyn_dataflowtemplateid	Generer en ny GUID for å identifisere malen ved å gå til GUID-format.
msdyn_uniquename	Angi et unikt navn for maloppføringen.
msdyn_category	Kategorien for maloppføringen. La denne verdien være som den er.
msdyn_configuration	Endre dette til JSON som representerer tildelingskonfigurasjonen for malen, til datakategoriene som støttes i Sustainability Manager. Du finner instruksjoner om hvordan du skaffer deg denne JSON-en etter denne tabellen.
msdyn_description	Angi en beskrivelse av Power Query-malen.
msdyn_mashupdocument	Denne parameteren er en tekstrepresentasjon av alle transformasjonene i koblingen. Du finner instruksjoner om hvordan du skaffer deg denne teksten etter denne tabellen.
msdyn_templatename	Angi et navn på malen.
msdyn_templateversion	Angi versjonsnummeret for malen.

Hvis du vil trekke ut informasjonen i parameterne msdyn_configuration og msdyn_mashupdocument, kopierer du og limer inn følgende tekst i nettleseren. Bytt <organisasjonen_din> med navnet på organisasjonens miljø.

https://<your_organization>.crm10.dynamics.com/api/data/v9.0/msdyn_dataflowtemplates

En JSON vises med alle msdyn_dataflowtemplates-oppføringene, der du finner malen du vil flytte. Kopier og lim inn feltene i filen msdyn_dataflowtemplates.xml.