Showing results for 
Show  only  | Search instead for 
Did you mean: 

How to Connect PowerBI to HopexGraphQL?

MEGA Partner
MEGA Partner

I recently tried to connect Microsoft Power BI to HopexGraphQL, I am putting my experience here that it may benefit others, having said that there may be a better way to do this which I am not aware of so feel free to drop in your comments or questions: 


Why Power BI? For now it's my favorite analytical tool

Step 1: Create a GetToken Power Query to connect to UAS

Code for the query: 

extension = "/connect/token",
url = base_url &extension,
content1= "grant_type=password&scope=hopex%20openid%20read%20write%20offline_access&client_id=HopexAPI&client_secret=secret&username={GraphQL Username}&password={password}&environmentId=M6LC(Gp3VzSP",
webdata1 = Web.Contents(url, [Headers=[
#"Content-Type" = "application/x-www-form-urlencoded"],Content = Text.ToBinary(content1)]),
response1 = Json.Document(webdata1),
#"Converted to Table" = Record.ToTable(response1),
#"Transposed Table" = Table.Transpose(#"Converted to Table"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"access_token", type text}, {"expires_in", Int64.Type}, {"token_type", type text}, {"refresh_token", type text}})
#"Changed Type"


Step 2: Turn the above Query to function such as GetToken


Step 3: Use the above function to retrieve token and connect to your favorite module in GraphQL for me it's ITPM


token = GetToken(),
#"tbl" = token,
gottoken = #"tbl"[access_token]{0},
Source = Web.Contents(
#"Authorization"="Bearer " & gottoken,
#"X-Hopex-Context" = "{""EnvironmentId"":""M6LC(Gp3VzSP"",""RepositoryId"":""ihd7)1IVRXR9"",""ProfileId"":""757wuc(SGjpJ""}"
// Notice the quote escaping here
Content=Text.ToBinary("{""query"":""query{ application{ id name applicationType businessProcess{ id name} applicationOwner_PersonSystem{ id name } sentApplicationFlow_ApplicationParticipant{ id name } receivedApplicationFlow_ApplicationParticipant{ id name }} }""}")
#"JSON" = Json.Document(Source),
data = JSON[data],
application = data[application],
#"Converted to Table" = Table.FromList(application, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "name", "applicationType", "businessProcess", "applicationOwner_PersonSystem", "sentApplicationFlow_ApplicationParticipant", "receivedApplicationFlow_ApplicationParticipant"}, {"", "", "Column1.applicationType", "Column1.businessProcess", "Column1.applicationOwner_PersonSystem", "Column1.sentApplicationFlow_ApplicationParticipant", "Column1.receivedApplicationFlow_ApplicationParticipant"}),
#"Expanded Column1.businessProcess" = Table.ExpandListColumn(#"Expanded Column1", "Column1.businessProcess"),
#"Expanded Column1.businessProcess1" = Table.ExpandRecordColumn(#"Expanded Column1.businessProcess", "Column1.businessProcess", {"id", "name"}, {"", ""}),
#"Expanded Column1.applicationOwner_PersonSystem" = Table.ExpandListColumn(#"Expanded Column1.businessProcess1", "Column1.applicationOwner_PersonSystem"),
#"Expanded Column1.applicationOwner_PersonSystem1" = Table.ExpandRecordColumn(#"Expanded Column1.applicationOwner_PersonSystem", "Column1.applicationOwner_PersonSystem", {"id", "name"}, {"", ""}),
#"Expanded Column1.sentApplicationFlow_ApplicationParticipant" = Table.ExpandListColumn(#"Expanded Column1.applicationOwner_PersonSystem1", "Column1.sentApplicationFlow_ApplicationParticipant"),
#"Expanded Column1.sentApplicationFlow_ApplicationParticipant1" = Table.ExpandRecordColumn(#"Expanded Column1.sentApplicationFlow_ApplicationParticipant", "Column1.sentApplicationFlow_ApplicationParticipant", {"id", "name"}, {"", ""}),
#"Expanded Column1.receivedApplicationFlow_ApplicationParticipant" = Table.ExpandListColumn(#"Expanded Column1.sentApplicationFlow_ApplicationParticipant1", "Column1.receivedApplicationFlow_ApplicationParticipant"),
#"Expanded Column1.receivedApplicationFlow_ApplicationParticipant1" = Table.ExpandRecordColumn(#"Expanded Column1.receivedApplicationFlow_ApplicationParticipant", "Column1.receivedApplicationFlow_ApplicationParticipant", {"id", "name"}, {"", ""})
#"Expanded Column1.receivedApplicationFlow_ApplicationParticipant1"


Step4: Turn the last repeated code lines into a function as not to repeat in every query


Step5: Remove or Add any unnecessary columns, and create visualizations to your hearts content, or run more queries and create relationships between multiple mega objects for dynamic and real time analysis.  


4 Replies

In V5 this is different.


You don't need to de first a call to get a Bearer.


First you need to create an API Key in the HAS Console.





Then you can directly use this API Key in the call.




Read the updated postman libraries  here 




MEGA Partner
MEGA Partner

Hello Imran,

Thank you for sharing it! I tried to use it and unfortunately it didnt work. I am currently testing it in Hopex V5 and I get as a response (400) Bad Request in PowerBI. Do you still use it in Hopex V5? Authentication with bearer token is since Hopex V5 outdated, is it right? Does this mean we can try another authentication method and try to connect GraphQL and PowerBI through that? And if there is one that you use, what is it?

Thank you for your response!


very helpful ! thank you !!