Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Loading GraphQL data (GitHub API v4) into PowerBI
// This script shows how to use M language (Power Query Formula Language)
// to read data from GitHub API v4 using a POST request.
// This can come in handy when building PowerBI reports that utilize GraphQL endpoints for loading data.
let
vUrl = "https://api.github.com/graphql",
vHeaders =[
#"Method"="POST",
#"Content-Type"="application/json",
#"Authorization"="Bearer <your_personal_token_here>"
],
// Notice the quote escaping here
vContent=Text.ToBinary("{""query"": ""{ organization(login: \""github\"") { name }}""}"),
Source = Web.Contents(vUrl, [Headers=vHeaders, Content=vContent]),
#"JSON" = Json.Document(Source)
in
#"JSON"
@jaswinderahuja

This comment has been minimized.

Copy link

@jaswinderahuja jaswinderahuja commented Oct 22, 2019

Hi,

I am using kind of same approach but it is throwing 400 bad request. can you pls suggest here what I am doing wrong.


let
Source = Web.Contents(
"https://*****.com/api/v1/pack_mkt",
[
Headers=[
#"Method"="POST",
#"Content-Type"="application/json"
],
// Notice the quote escaping here
Content=Text.ToBinary("{""query"": ""{
packMarketPent(periods: {start_date: ""2019-01-01"", end_date: ""2019-10-01"" })
{
service_name
}
} ""}")
]
),
#"JSON" = Json.Document(Source)
in
#"JSON"

I really appreciate, if you could help me.

@MikeAinOz

This comment has been minimized.

Copy link

@MikeAinOz MikeAinOz commented Oct 22, 2019

Hi Jaswinder, on first glance that looks good. Do you have a link to the API documentation? I was wondering if it should be a GET rather than a POST. I normally prototype in Postman, to make sure I've got the request correct.

@jaswinderahuja

This comment has been minimized.

Copy link

@jaswinderahuja jaswinderahuja commented Oct 23, 2019

Thanks MikeAinOz for your response, I had tested the API in postman before using here.

This is the code which worked for me. but now I am using one filter in the power BI which has list of dates and want to pass the selected date to this query to fetch the data. Can you pls provide your valuable input here.

let
Source = Web.Contents(
"https://************.com/api/v1/pack_mkt",
[
Headers=[
#"Method"="POST",
#"Content-Type"="application/json"
],
Content=Text.ToBinary("{""query"": ""{ packMarketPent(periods: {start_date: ""2019-01-01"", end_date: ""2019-10-01"" } ) { channel service_name metric_absolute_actual}}""}")
]
),
#"JSON" = Json.Document(Source)
in
#"JSON"

@jaswinderahuja

This comment has been minimized.

Copy link

@jaswinderahuja jaswinderahuja commented Oct 24, 2019

Hi @petrsvihlik,

I am still trying to figure out that if have a dropdown and on selection of value from dropdown which is startdate and end_date and that date i want to use in the above power Query. How can we do it.
I really appreciate if you can help.

@petrsvihlik

This comment has been minimized.

Copy link
Owner Author

@petrsvihlik petrsvihlik commented Oct 25, 2019

Hi @jaswinderahuja, that's not possible. You have to get the data first and then filter it. This is how PowerBI works.

@SherinMirza

This comment has been minimized.

Copy link

@SherinMirza SherinMirza commented Dec 26, 2019

Hi petrsvihlik
I am getting below error while trying to get as suggested in the post. Can you please help?
DataSource.Error: Web.Contents with the Content option is only supported when connecting anonymously.

@petrsvihlik

This comment has been minimized.

Copy link
Owner Author

@petrsvihlik petrsvihlik commented Jan 2, 2020

Have you replaced the <your_personal_token_here> with a personal access token with appropriate permissions?

The sample works fine for me...

@SherinMirza

This comment has been minimized.

Copy link

@SherinMirza SherinMirza commented Jan 10, 2020

Thank you Petr. I modified data source settings and permission to Anonymous and it worked.
I use GraphQl API explorer to build queries related to GitHub. But I would like to run them in PowerBI with the way you suggested to convert the output to table and create reports.
Is there an easy way to convert graphql queries to the format compatible with PowerBI? Appreciate your recommendations

@petrsvihlik

This comment has been minimized.

Copy link
Owner Author

@petrsvihlik petrsvihlik commented Jan 16, 2020

But they are compatible! 😀 I don't know of any simple way of converting them. You just need to escape " with \"" and wrap the query in {""query"": "" and ""}

Query

image

Result

image

@ThiagoRamos2020

This comment has been minimized.

Copy link

@ThiagoRamos2020 ThiagoRamos2020 commented Jan 26, 2020

But they are compatible! 😀 I don't know of any simple way of converting them. You just need to escape " with \"" and wrap the query in {""query"": "" and ""}

Query

image

Result

image

How can I pass the content between "" as a parameter or "as any parameter" in Power Query?

@petrsvihlik

This comment has been minimized.

Copy link
Owner Author

@petrsvihlik petrsvihlik commented Feb 19, 2020

@SherinMirza I created a simple tool for escaping the queries: https://jsfiddle.net/rockyintel/1sqm7dpf/
any suggestions for improvements are welcome!

@ThiagoRamos2020 I'm not sure I understand. Can you be more specific? What exactly are you trying to achieve? What do you mean by the quotes and by "parameter"? Can my tool above help you?

@Akshay-sudo999

This comment has been minimized.

Copy link

@Akshay-sudo999 Akshay-sudo999 commented Jun 3, 2020

Hi @jaswinderahuja, that's not possible. You have to get the data first and then filter it. This is how PowerBI works.

Hi @petrsvihlik , I am also trying fetch data from GRAPhQL URL as API from Power Bi desktop. Is it not a github URL but not authorizATION IS required can you please help.

@petrsvihlik

This comment has been minimized.

Copy link
Owner Author

@petrsvihlik petrsvihlik commented Jun 3, 2020

Hi @jaswinderahuja, that's not possible. You have to get the data first and then filter it. This is how PowerBI works.

Hi @petrsvihlik , I am also trying fetch data from GRAPhQL URL as API from Power Bi desktop. Is it not a github URL but not authorizATION IS required can you please help.

I'm not sure if I understand. Please correct me if I'm wrong:

  • you are NOT querying GitHub's GraphQL API
  • the API you are querying doesn't require authorization

right?

In that case, you can omit the #"Authorization"="Bearer <your_personal_token_here>" part and change the endpoint URL from "https://api.github.com/graphql" to something else.

@vasugowda

This comment has been minimized.

Copy link

@vasugowda vasugowda commented Sep 30, 2020

@MikeAinOz and @petrsvihlik
Can you please help in resolving below 404 issue while making graphql query

Step 1: Peform this activity in power bi desktop, creating Blank query with below content
let
Source = Web.Contents(
"https://",
[
Headers=[
#"Method"="POST",
#"Content-Type"="application/json",
#"Authorization"=""
],
// Notice the quote escaping here
Content=Text.ToBinary("{""query"": """"}")
]
),
#"JSON" = Json.Document(Source)
in
#"JSON"

Step 2: Get content and split it into multiple columns. This works
Step 3: Save Table. This works
Step 4: Create a report using the table data. This works
Step 5: Save and publish to power bi online service. This will create the report
Step 6: Data connection will throw 404 error
RCA: What we noticed is when we create the query in power bi desktop the request is HTTP POST. Whereas after publishing the request are made with HTTP GET.
Is there a way to change the HTTP request method in published report/data model.

@MikeAinOz

This comment has been minimized.

Copy link

@MikeAinOz MikeAinOz commented Oct 1, 2020

What are you trying to do with a blank query? I don't understand.

@vasugowda

This comment has been minimized.

Copy link

@vasugowda vasugowda commented Oct 5, 2020

@MikeAinOz - Sorry this is not blank query, i tried to explain with the template above. The actual query will return data. As mentioned above this will generate the report in power bi desktop ( as per the template it makes POST call). Where-as once we publish for delta data power bi makes HTTP GET call instead of HTTP POST call.

@petrsvihlik

This comment has been minimized.

Copy link
Owner Author

@petrsvihlik petrsvihlik commented Oct 5, 2020

@vasugowda seems that you're not the only one experiencing this issue.
check out this: https://stackoverflow.com/q/48385504/1332034

I really don't know what's going on. It works perfectly fine for me. Try specifying the headers through dedicated variables as suggested here:

let
vBaseUrl = "https://api.projectmanager.com",
vPath = "api/v1/projects.json",
vHeaders = [ #"apikey"="this is where i put my key" ],
Source = Web.Contents ( vBaseUrl, [RelativePath=vPath, Headers=vHeaders] ),
Json = Json.Document ( Source )
in
Json

does it work for you?

@Priyanka2719

This comment has been minimized.

Copy link

@Priyanka2719 Priyanka2719 commented Oct 6, 2020

@petrsvihlik Thank you for your response. Yes, by sending the headers & content as suggested in your comment helped us resolve our issue. Thank you for your help.
The only change we made was to pass content also as a part of "Web. Contents".
let
vBaseUrl = "https://api.projectmanager.com",
vPath = "api/v1/projects.json",
vHeaders = [ #"apikey"="this is where i put my key" ],
Source = Web.Contents ( vBaseUrl, [RelativePath=vPath, Headers=vHeaders, Contents=<Query_content>] ),
Json = Json.Document ( Source )
in
Json
@vasugowda This worked as expected

@petrsvihlik

This comment has been minimized.

Copy link
Owner Author

@petrsvihlik petrsvihlik commented Oct 6, 2020

thanks for the heads up, I updated the sample!

@michelsolognier

This comment has been minimized.

Copy link

@michelsolognier michelsolognier commented Oct 9, 2020

Hi all, i've tried all of the above advises. I managed to connect power bi via the graphql data of github and got the same result as @petrsvihlik. However now i want to move to a next step by trying to a request specific dataset from a monitoring platform. The query i need to specify needs to follow the structure as follows:

query {
  historicalAlarms(before: "2020-11-01T12:15:04Z", after: "2020-09-30T15:04:05Z", limit: 2){
    edges {
      node {
        addedAt
        comingGoing
        aggregateID
        aggregateName
        aggregateText
        aggregateDescription
      }
    }
  }
}

I tested this in postman (with its header containing the Authorization key) and is works fine!

Now i need to pack this Query up as a content to send this via de POST req. Only i can't seem to get this right. I keep getting an error code 400: BAD REQUEST.Does anyone have any idea? My code in the Power BI Query Editor is as follows:

let
    Source = Web.Contents(
	"http://<......>/api/partner/gql",
	[
		Headers=[
			#"Method"="POST",
			#"Content-Type"="application/json",
			#"Authorization"="Bearer <key>"
		],
		Content=Text.ToBinary("{""query"": ""{ historicalAlarms (before: \""2020-11-01T12:15:04Z\"", after: \""2020-09-30T15:04:05Z\"", limit: 2) {
            edges {
                node {
                addedAt
                comingGoing
                aggregateID
                aggregateName
                aggregateText
                aggregateDescription
                }
            }
        }
    }

    ""}")]),
    #"JSON" = Json.Document(Source)
in
    #"JSON

After a while i made some changes, but now im getting a error code 422: Unprocessable entity. So i think the request is now working fine, only now somewhere in the content, one or more parts isin't suitable to be processed. Am i understanding this right?

let
    vUrl = "http://<............>",
    vPath = "api/partner/gql",
    vHeaders =[
			#"Method"="POST",
			#"Content-Type"="application/json",
			#"Authorization"="Bearer <KEY>"
		],
    vContent=Text.ToBinary("{""query"": ""{   historicalAlarms (before: \""2020-11-01T12:15:04Z\"", after: \""2020-09-30T15:04:05Z\"", limit: 2) {edges{node{addedAT comingGoing aggregateID aggregateName aggregateText aggregateDescription}}}         }""}"),
    Source = Web.Contents(vUrl,	[RelativePath=vPath, Headers=vHeaders, Content=vContent]),
    #"JSON" = Json.Document(Source)
in
    #"JSON"
@petrsvihlik

This comment has been minimized.

Copy link
Owner Author

@petrsvihlik petrsvihlik commented Oct 9, 2020

@michelsolognier there must not be any line breaks. try the JS fiddle I posted above to escape the query.

just by looking at your code, I think you're missing the leading query {...

@michelsolognier

This comment has been minimized.

Copy link

@michelsolognier michelsolognier commented Oct 9, 2020

ahh... i knew i was hanging on a silly mistake. It works now! Many thanks @petrsvihlik!

@petrsvihlik

This comment has been minimized.

Copy link
Owner Author

@petrsvihlik petrsvihlik commented Oct 9, 2020

@mikefede

This comment has been minimized.

Copy link

@mikefede mikefede commented Oct 20, 2020

Thank you! This, and the JS fiddle to escape the query, has been very helpful.

Would you be able to provide an example that makes multiple requests to get multiple pages of data and combine them? GitHub only allows you to request 100 items at a time and to get all of the data you need to get the pageInfo for the endCursor to request the next page of data. I'm struggling to figure out how to do that in Power BI. Thanks!

@petrsvihlik

This comment has been minimized.

Copy link
Owner Author

@petrsvihlik petrsvihlik commented Oct 21, 2020

@mikefede Thanks :) Regarding the pagination, have you tried this? https://datachant.com/2016/06/27/cursor-based-pagination-power-query/
It seems pretty straightforward and I think it should work. If it does, could you please let everybody know here in the discussion (ideally, with a piece of sample code)?

@mikefede

This comment has been minimized.

Copy link

@mikefede mikefede commented Oct 21, 2020

@petrsvihlik Thank you again! Yes this worked, see the sample code below.
I have one other question, do you have any pointers on how we might store the personal access token securely, rather than just embedding it in the script in plain text?

Here's the sample code. This example gets a list of SAML Identities with their corresponding GitHub logins. There's probably a more elegant way to insert the cursor into the query, but this works.

let
	FnGetOnePage = (cursor) =>
	let		
		Source = Json.Document(Web.Contents(
		"https://api.github.com/graphql",
		[
			Headers=[
				#"Method"="POST",
				#"Content-Type"="application/json",
				#"Authorization"="Bearer <your_personal_token_here>"
			],
		Content=Text.ToBinary(Text.Replace("{""query"": ""query($cursor: String) { organization(login: \""<your_organization_here>\"") { samlIdentityProvider { externalIdentities(first: 100, after:$cursor) { totalCount edges { node { guid samlIdentity { nameId } user { login } } } pageInfo { endCursor hasNextPage } } } }}""}", "($cursor: String)", "($cursor: String" & cursor & ")"))
		]
		)),
		data = Source[data],
		organization = data[organization],
		samlIdentityProvider = organization[samlIdentityProvider],
		externalIdentities = samlIdentityProvider[externalIdentities],
		edges = externalIdentities[edges],
		pageInfo = externalIdentities[pageInfo],
		hasNextPage = pageInfo[hasNextPage],
		endcursor = pageInfo[endCursor],
		res = [Data=edges, Cursor=endcursor, HasNext=hasNextPage]
	in 
		res,
	GeneratedList =
		List.Generate(
			()=>[res = FnGetOnePage("")],
			each Comparer.Equals(Comparer.FromCulture("en-us"), [res][HasNext], true),
			each [res = FnGetOnePage("=\""" & [res][Cursor] & "\""")],
			each [res][Data] 
		),
    #"Converted to Table" = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1"),
    #"Expanded Column2" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1", {"node"}, {"Column1.node"}),
    #"Expanded Column1.node" = Table.ExpandRecordColumn(#"Expanded Column2", "Column1.node", {"samlIdentity", "user"}, {"Column1.node.samlIdentity", "Column1.node.user"}),
    #"Expanded Column1.node.samlIdentity" = Table.ExpandRecordColumn(#"Expanded Column1.node", "Column1.node.samlIdentity", {"nameId"}, {"Column1.node.samlIdentity.nameId"}),
    #"Expanded Column1.node.user" = Table.ExpandRecordColumn(#"Expanded Column1.node.samlIdentity", "Column1.node.user", {"login"}, {"Column1.node.user.login"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Column1.node.user", each [Column1.node.user.login] <> null and [Column1.node.user.login] <> ""),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Column1.node.samlIdentity.nameId", "SAML Identity"}, {"Column1.node.user.login", "GitHub Login"}})
in
    #"Renamed Columns"
@petrsvihlik

This comment has been minimized.

Copy link
Owner Author

@petrsvihlik petrsvihlik commented Oct 22, 2020

@mikefede - perfect! thanks for sharing the code!

regarding the secure access storage - well, there are two ways of NOT including the API keys in the PBIXes that I'm aware of:

  • keep them in separate storage such as an excel file...this doesn't make it any safer but at least the keys don't travel with the PBIX file.
  • or better, use the data source permissions as described here

image

@pramitnb1990

This comment has been minimized.

Copy link

@pramitnb1990 pramitnb1990 commented Mar 8, 2021

Hi , Thank you for this post - it really helped me a lot .
I am trying to connect POWEBI to API end point and put in the GraphQL query which is running perfectly on POSTMAN but somehow not working when I write the m code integrating the GraphQL query
let
vUrl = "http://<............>",
vHeaders =[
#"Method"="POST",
#"Content-Type"="application/json",
#"Authorization"="Bearer "
vContent=Text.ToBinary("{""query"": ""{ table_name(where: {colX: {_eq: ""ABC""},
colY:{_eq: ""XYZ""}}limit: 10){
colA
colX
colY

}}""}"),
Source = Web.Contents(vUrl, [Headers=vHeaders, Content=vContent]),
#"JSON" = Json.Document(Source)

However I am getting the below error:
Error in $: Failed reading: satisfy. Expecting ',' or '}' at 'ABC},'
image

Can someone please help, I am stuck at this for almost 4 days :( :(

Sincere Regards

@MikeAinOz

This comment has been minimized.

Copy link

@MikeAinOz MikeAinOz commented Mar 8, 2021

Hey friend! Just looking at it I can see that you haven't terminated the header record with "]," before you define vContent. Is this the problem?

@pramitnb1990

This comment has been minimized.

Copy link

@pramitnb1990 pramitnb1990 commented Mar 8, 2021

Hey friend! Just looking at it I can see that you haven't terminated the header record with "]," before you define vContent. Is this the problem?

Hi MikeAinOz, Thank you for taking out your time, really appreciate it!
No it seems not, I corrected it but the error persists :(

Kind Regards

@MikeAinOz

This comment has been minimized.

Copy link

@MikeAinOz MikeAinOz commented Mar 9, 2021

Hey friend! Just looking at it I can see that you haven't terminated the header record with "]," before you define vContent. Is this the problem?

Hi MikeAinOz, Thank you for taking out your time, really appreciate it!
No it seems not, I corrected it but the error persists :(

Kind Regards

I'd check that content in a separate variable so that you can see what is in it, have you tried that?

@pramitnb1990

This comment has been minimized.

Copy link

@pramitnb1990 pramitnb1990 commented Mar 23, 2021

Hey friend! Just looking at it I can see that you haven't terminated the header record with "]," before you define vContent. Is this the problem?

Hi MikeAinOz, Thank you for taking out your time, really appreciate it!
No it seems not, I corrected it but the error persists :(
Kind Regards

I'd check that content in a separate variable so that you can see what is in it, have you tried that?

Hi MikeAinOZ, Thank you for your valuable input, it worked :) Really appreciate your help!

@anon145

This comment has been minimized.

Copy link

@anon145 anon145 commented Mar 25, 2021

@MikeAinOz @petrsvihlik
Hi Gents, great resource thanks for sharing.
If anyone has a moment to be able to help with my pagination function, i'd greatly appreciate it :)
I'm really struggling but I think what i'm after is -
For the function to pull the first 1000 nodes, check the edge "pageinfo" for "hasNextPage" = true, then insert "endCursor" as the "after:"endCursor" on the next iteration, until "hasNextPage" = false?

Does that sound right?

eg.
"pageInfo": {
"endCursor": "YXJyYXljb25uZWN0aW9uOjA=",
"hasNextPage": true,
"hasPreviousPage": false,
"startCursor": "YXJyYXljb25uZWN0aW9uOjA="
}

Working query without pagination
let
Source = Web.Contents(
"https://website/graphql",
[
Headers =[
#"Method"="POST",
#"Content-Type"="application/json",
#"x-developer-secret"="pass"
],
Content=Text.ToBinary("{""query"": ""query { getResults( first: 1000, input: { dates: { from: ""2021-03-02 00:00:00"", to: ""2021-03-17 23:59:00"" }}){ edges { cursor node { details } } pageInfo { startCursor endCursor hasNextPage hasPreviousPage } }}""}")
]
),
#"JSON" = Json.Document(Source),
#"Converted to Table" = Record.ToTable(JSON),

@petrsvihlik

This comment has been minimized.

Copy link
Owner Author

@petrsvihlik petrsvihlik commented Mar 25, 2021

@anon145 Did the approach suggested by @mikefede not work? You need to use List.Generate and recursively call the query while altering the cursor.

@anon145

This comment has been minimized.

Copy link

@anon145 anon145 commented Mar 25, 2021

@petrsvihlik Hi, I'm sure it would work, I'm just struggling as this is new, all the examples seem to differ slightly :)

@petrsvihlik

This comment has been minimized.

Copy link
Owner Author

@petrsvihlik petrsvihlik commented Mar 25, 2021

Try this sample, make sure it works for you, and make sure you understand it. Then, cut the stuff you don't need and try to make it simpler. Next, replace the query with yours and work from there. If you get stuck, post your query here and we can start elaborating on what's going on wrong :)

@anon145

This comment has been minimized.

Copy link

@anon145 anon145 commented Mar 27, 2021

Try this sample, make sure it works for you, and make sure you understand it. Then, cut the stuff you don't need and try to make it simpler. Next, replace the query with yours and work from there. If you get stuck, post your query here and we can start elaborating on what's going on wrong :)

Thanks for the push @petrsvihlik, managed to get it this far!
The FnGetOnePage function appears to be ok but the query fails on next step GenerateList:
Expression.Error: 1 arguments were passed to a function which expects 3.
Details:
Pattern=
Arguments=[List]

This is how far I got, if anyone can help i'm out of my depth here :)
_let
FnGetOnePage = (cursor) =>
let
Source = Json.Document(Web.Contents(
"https://website/graphql",
[
Headers =[
#"Method"="POST",
#"Content-Type"="application/json",
#"x-developer-secret"="pass"
],
Content=Text.ToBinary(Text.Replace("{""query"": ""query ($cursor: String) { getResults(first: 1000, input: {dates: {from: ""2021-03-02 00:00:00"", to: ""2021-03-17 23:59:00""}}, after: $cursor) { edges { cursor node { "fields" } } } pageInfo { endCursor hasNextPage } }}""}"),
"($cursor: String)", "($cursor: String" & cursor & ")")
]
)),
data = Source[data],
getResults= data[getResults],
edges = getResults[edges],
pageInfo = getResults[pageInfo],
hasNextPage = pageInfo[hasNextPage],
endcursor = pageInfo[endCursor],
res = [Data=edges, Cursor=endcursor, HasNext=hasNextPage]
in
res,
GeneratedList =
List.Generate(
()=>[res = FnGetOnePage("")],
each Comparer.Equals(Comparer.FromCulture("en-us"), [res][HasNext], true),
each [res = FnGetOnePage("=""" & [res][Cursor] & """")],
each [res][Data]
).........

@Wisteru

This comment has been minimized.

Copy link

@Wisteru Wisteru commented Apr 1, 2021

Try this sample, make sure it works for you, and make sure you understand it. Then, cut the stuff you don't need and try to make it simpler. Next, replace the query with yours and work from there. If you get stuck, post your query here and we can start elaborating on what's going on wrong :)

hello @petrsvihlik , I'm trying to use this example, but i have a problem with last page. It's not getting into GeneratedList because "HasNext" is false for last page. Do you maybe know how to handle it?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment