Skip to Content
Technical Articles
Author's profile photo Helmut Skolaut

Accessing Cloud Integration via Excel Power Query

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:
    Empty%20Query
  2. Then you have to open the extended editor:
    open%20extended%20editor
  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

Assigned Tags

      2 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Martin Pankraz
      Martin Pankraz

      Hey Helmut Skolaut,

      thanks for sharing your custom code approach! Might be worth linking to my Microsoft docs entry on the matter here. It discusses the technical options and the benefits if solving the authentication/authorization challenge with the SAP OAuth server in one place to avoid burdening consumer clients like Excel. With that you can use the OData connector on Excel for instance rather than a blank query.

      KR

      Martin

      Author's profile photo Helmut Skolaut
      Helmut Skolaut
      Blog Post Author

      Thanks Martin - also very nice blog. I will mention it as alternative way.