Connect PowerBI to GraphQL endpoint
In this article, we will be using a GraphQL endpoint, created through MuleSoft Anypoint DataGraph, to connect PowerBI using its Power Query. As PowerBI does not provide an Out-of-the-Box configurable REST API adaptor, Power Query is a great possibility to use M language. To learn more about Power Queries, click here.
Pre-requisites
The scenario for this article contains 4 REST APIs (connected to Salesforce) which are unified into a DataGraph scheme and accessible via a single GraphQL endpoint.
Step 1: Get Anypoint DataGraph endpoint details
As a first step, we need to have the Anypoint DataGraph endpoint information. This includes the endpoint itself, as well as the client-id and client-secret. You can find this information in the "Run Query" wizard by clicking on Copy & Share Query endpoint:
It will open the endpoint dialog with all required information.
Step 2: Open PowerBI and create a Power Query
In PowerBI, click on Get Data from the Home Menu and select More.
In the search box, enter "Blank Query", select the Blank Query on the right side in the list and click Connect.
In the Power Query Editor, open the Advanced Editor from the Home Menu.
Step 3: Write the M script to connect to GraphQL endpoint
In the Advanced Editor, we will be using the data functions of Power BI to connect to web content. All functions are listed here. As we are connecting to GraphQL endpoint, we need to use the data function Web.Contents.
领英推荐
Web.Contents(url as text, optional options as nullable record) as binary
You can find Web.Contents generic examples here.
We need to connect to the GraphQL endpoint using client-id and client-secret. Our script will differ slightly from the generic examples.
let
url = "https://datagraph-xxx.xxx.xxx-xxx.us-e2.cloudhub.io/graphql",
client_id = "xxxxxxxxxxxxxxxx",
client_secret = "xxxxxxxxxxxxxxxxx",
Source = Web.Contents(
url,
[
Headers=[
#"Method"="POST",
#"Content-Type"="application/json",
#"client_id"=client_id,
#"client_secret"=client_secret
],
Content=Text.ToBinary("{""query"": ""{ campaigns{ id name type startDate endDate status expRevenue } accounts{ id name type openOpps openOppsValue annualRevenue } opportunities{ id name type probability amount createdDate closeDate stage leadSource accountName } leads{ id name company annualRevenua leadSource country leadPriority leadStatus totalScore leadQuality }}""}")
]
),
#"JSON" = Json.Document(Source)
in
JSON
After entering the code, click on Done.
You will be prompted to provide credentials to connect to the endpoint. Select Credential Type Anonymous.
Now you should see the data Entry with single Record in the query section.
Step 4: Format and Extract the Data
As the whole query has been returned in a nested and raw format, you need to extract and format the data to your needs - make use of the PowerBI data extraction capabilities.
Finally, click on Close & Apply and you are ready to build your graphs.
Step 5: Create Reports
Once your query is ready, you can create PowerBI reports from the GraphQL endpoint.
That's it! You have successfully connected PowerBI with GraphQL via MuleSoft Anypoint DataGraph.
Happy connecting ?? ?? ???!!!
Try it out yourself
Solutions Builder
9 个月With no prior experience in PowerBI I was trying to integrate it with a GraphQL endpoint for a POC, found this post luckily and saved myself a couple of hours. Thank you for taking time posting this.
Assistant Estimation Engineer
1 年I have done all as per this article but not able to connect power bi to GrapQL, I receive an error for (400): Bad Request. I am using same M script which provide in this article, Can you please tell me why?
SDE @Siteform
1 年Will it work for hasura Graphql??
CTO | Practice Leader | Dreamforce Speaker | Salesforce Trailblazer | GTM Champion | MuleSoft Ambassador Alumni | DNI | Customer Success Senior Manager at Salesforce
2 年Best content creator Amir Khan ????????????