Accessing Cloud Integration via Excel Power Query

[helmut_skolaut3]

Challenge:

I wanted to access my iFlow from an Excel Power Query and i wanted to access it via Oauth2 Login.

 

Prerequisites:

• You have created an iFlow that has an HTTP inbound endpoint and you have assigned a User Role to it.


• You have created a Process Integration Runtime Instance and created an OAuth key for it

How to setup your Excel:

1. You have to create an empty Query:
   


2. Then you have to open the extended editor:
   


3. Then you can enter following code:
   

   let
   
       token_url = "URL TO OAUTH TOKEN",
   
       token_path = "oauth/token",
   
       api_url = "URL TO INTEGRATION SUITE",
   
       api_path ="URL PATH TO iFLOW",
   
   
   
       TokenResponse = Json.Document(
   
           Web.Contents(
   
               token_url, 
   
               [
   
                   RelativePath = token_path,
   
                   Query = [grant_type="client_credentials"],
   
                   Headers = [
   
                       #"Content-Type"="application/x-www-form-urlencoded"
   
                   ]
   
               ]
   
           )
   
       ),
   
   
   
       token = TokenResponse[access_token],
   
   
   
       Source = Xml.Tables(
   
           Web.Contents(
   
               api_url,
   
               [
   
                   RelativePath = api_path,
   
                   Headers = [
   
                       #"Authorization"="Bearer "&token,
   
                       #"Content-Type"="application/xml"
   
                   ]
   
                   
   
               ]
   
           )
   
       ),
   
   	
   
   	Table0 = Source{0}[Table],
   
       #"ChangedType" = Table.TransformColumnTypes(
   
   		Table0,{
   
   			{"PackageName", type text},
   
   		}
   
   	),
   
   	
   
     
   
   in
   
       #"ChangedType"​

   
   


4. You have now two HTTPS Requests and you need to manually configure the access method for each service via this menu:
   --> The URL for the Token Access you have to add the ClientID and ClientSecret in view "Standard"
   --> The URL for the Service call, you have to set up to "Anonym"

What ever you see in the bottom with Table0 i advice to execute your iflow from Postman first, then you download the XML output to your local PC and you create the query via the wizzard. Then you copy code you need into bottom of the code

 

Source that helped me:

I have used this example i have found:

https://www.myonlinetraininghub.com/connecting-to-an-oauth-api-like-paypal-with-power-query

 

Alternatives:

Blog from martin-pankraz : https://learn.microsoft.com/en-us/azure/sap/workloads/expose-sap-odata-to-power-query