Loading GraphQL data (GitHub API v4) into PowerBI

GitHub-GraphQL-PowerBI.m

// 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"

I was kinda successful with replacing each Comparer.Equals(Comparer.FromCulture("en-us"), [res][HasNext], true), with each [res][Data] <> null,. There are still some errors in the resulting dataset however the data is complete and the errors can be filtered out.

@petrsvihlik Try this instead:

each [res][Cursor] <> null,

@talynone yes, that works! perfect :)

So my resulting query looks like this:

let
	FnGetOnePage = (cursor) =>
	let		
		Source = Json.Document(Web.Contents(
		"https://api.github.com/graphql",
		[
			Headers=[
				#"Method"="POST",
				#"Content-Type"="application/json",
				#"Authorization"="Bearer ******"
			],
		Content=Text.ToBinary(Text.Replace("{""query"": ""query ($cursor: String) { repository(name: \""WopiHost\"", owner: \""petrsvihlik\"") { stargazers(first: 10, after: $cursor) { edges { node { login } starredAt } pageInfo { endCursor hasNextPage } } }}""}", "($cursor: String)", "($cursor: String" & cursor & ")"))
		]
		)),
		data = Source[data],
		repository = data[repository],
		stargazers = repository[stargazers],
		edges = stargazers[edges],
		pageInfo = stargazers[pageInfo],
		hasNextPage = pageInfo[hasNextPage],
		endcursor = pageInfo[endCursor],
		res = [Data=edges, Cursor=endcursor, HasNext=hasNextPage]
	in 
		res,
	Pages =
		List.Generate(
			()=>[res = FnGetOnePage("")],
			each [res][Cursor] <> null,
			each [res = try FnGetOnePage("=\""" & [res][Cursor] & "\""") otherwise null],
			each if [res] <> null then [res][Data] else null
		),
    #"Pages to Table" = Table.FromList(Pages, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Pages to Table",{{"Column1", "Pages"}}),
    #"Pages to Rows" = Table.ExpandListColumn(#"Renamed Columns", "Pages"),
    #"Renamed Columns1" = Table.RenameColumns(#"Pages to Rows",{{"Pages", "Records"}}),
    #"Expanded Column3" = Table.ExpandRecordColumn(#"Renamed Columns1", "Records", {"node", "starredAt"}, {"Records.node", "Records.starredAt"}),
    #"Expanded Column1.node" = Table.ExpandRecordColumn(#"Expanded Column3", "Records.node", {"login"}, {"Records.node.login"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Expanded Column1.node",{{"Records.node.login", "login"}, {"Records.starredAt", "starredAt"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns2",{{"starredAt", type datetime}})
in
    #"Changed Type"

And the original (not inlined) GQL query:

query ($cursor: String) {
  repository(name: "WopiHost", owner: "petrsvihlik") {
    stargazers(first: 10, after: $cursor) {
      edges {
        node {
          login          
        }
        starredAt
      }
      pageInfo {
        endCursor
        hasNextPage        
      }
    }
  }
}

@petrsvihlik Hello! I wanted to ask for your help for a similar problem I am struggling with.. I need to pass dynamic parameters(parameter_address created in powerbi) into graphql queries, the parameter will selected from a dropdown list on the main page, which will be passed into the graphql api for the results which needs to be displayed in a dataframe.
this is the code by far..please suggest.. thanks.

let
Query1 = let
Source = Web.Contents(Parameter_API_URL, [Headers=[
#"Method"="POST",
#"Content-Type"="application/json",
#"Authorization"="Bearer " & Parameter_API_Key
],
Content=Text.ToBinary("{""query"":""query getOneLineAddress(Parameter_Address: String){address(address: '"&Text.From(parameter_address)&"') {one_line_address}""}}")
]
),
#"JSON" = Json.Document(Source),
data = JSON[data]

In Transform Data->Manage Parameter, add your parameter. Then, include it in your query by adding "& your_param @ " instead of static text. There are guides online, that could help you with this: https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters

You didn't say what's not working for you. Are you getting some syntax errors, or do you have other kind of problem? I'd recommend putting the question with more details to StackOverflow for more visibility and linking it here perhaps.

hello thank you for your response. The query inside the advanced editor is returning the values from the GraphQL API when the parameter(your_param) is passed into the query., however, in the visualization screen when the parameter is being selected in the slicer the values returned are not reflecting in the text box which is bound to the return field. the parameter binding is done properly..

thanks

For this you have use Direct Query. https://docs.microsoft.com/en-gb/power-bi/connect-data/desktop-use-directquery
I don't know how you can combine Direct Query and GraphQL.
But I import all dataset and slicer works only that filter then.
You can use loop (List.Generate) for import data for all parameters.

Hello all,
I need your help. I am trying to fetch the data from a graphql endpoint, exactly the way @petrsvihlik suggested. But I am receiving the 404 error.
My code in the advanced Query is as below:

let
    vQuery= "{""query"": ""{  
  account(id: ""88f5d4e6-76b1-44ef-b7f3-f41655e08fe6"") {
    id
    name
    strategies {
      id
      name
    }
  }
}",
    vUrl = "https://demo.net/graphql",
    vHeaders =[
			#"Method"="POST",
			#"Content-Type"="application/json",
			#"Authorization"="Bearer 3523525135151f3r13f1f134134"
		],
    Source = Web.Contents(vUrl, 
	[Headers=vHeaders,
    Content=Text.ToBinary(vQuery) ]
	
	),
    #"JSON" = Json.Document(Source)
in
    #"JSON"

What am I doing wrong?

this is the error I see:
DataSource.Error: Web.Contents failed to get contents from 'https://demo.net/graphql' (400): Bad Request

Are you getting a 400 or 404?
In any case, I'd recommend using Fiddler to observe how your final request looks like nad comparing it with a manually assembled well-functioning request to see where the problem is.

@elimey , I think you may be having issue because your query is not in one line and there are linebreaks. I had similar issues as well and that's what fixed it.

@petrsvihlik on the topic of parameters, I am running a graphql query which pulls a list of team names and I would like to use that as parameter to get list of repo names, I understand that this may not be possible as the query storage type is Import vs Direct Query, is there alternative means to accomplish this objective? I had tried to follow the link you had shared in previous posts but I am not able to do what is described as the values I am getting is a list and I dont know how i can programmatically define the selected value in the query itself. Again, I think this may be a limitation of import vs direct query. I even created a table and entered the list in manually but I am not getting an option to do parameter binding on the actual parameter

Hello, Could one of you please help me with using powerquery to make multiple api calls updating a parameter with the max value from a list of records? I posted the details here: https://stackoverflow.com/questions/75944260/within-powerquery-make-multiple-api-calls-updating-a-parameter-with-the-max-valu

Alguem pode me ajudar? https://gist.github.com/alexalvescrp/01c576728fada8d68c59e5d9e1b7f181

I have a working independent query, but when I'm trying to page on it, I am having trouble getting to the correct depth.

Working Query
let vUrl = "https://api.github.com/graphql", vHeaders =[ #"Method"="POST", #"Content-Type"="application/json", #"Authorization"= "Bearer <token>" ], // Notice the quote escaping here vContent=Text.ToBinary("{""query"": ""query _Welcome_to_Altair_G518 { search(query: \""org: <org>\"", type: REPOSITORY, last: 100) { nodes { ... on Repository { name defaultBranchRef { name target { ... on Commit { history(first: 100, since: \""2024-01-29T00:00:00\"") { totalCount nodes { ... on Commit { committedDate additions deletions url author { name email } } } } } } } } } }}""}"), Source = Web.Contents(vUrl, [Headers=vHeaders, Content=vContent]), #"JSON" = Json.Document(Source), data = JSON[data], search = data[search], nodes = search[nodes], #"Converted to Table" = Table.FromList(nodes, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"name", "defaultBranchRef"}, {"Column1.name", "Column1.defaultBranchRef"}), #"Expanded Column1.defaultBranchRef" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.defaultBranchRef", {"name", "target"}, {"Column1.defaultBranchRef.name", "Column1.defaultBranchRef.target"}), #"Expanded Column1.defaultBranchRef.target" = Table.ExpandRecordColumn(#"Expanded Column1.defaultBranchRef", "Column1.defaultBranchRef.target", {"history"}, {"Column1.defaultBranchRef.target.history"}), #"Expanded Column1.defaultBranchRef.target.history" = Table.ExpandRecordColumn(#"Expanded Column1.defaultBranchRef.target", "Column1.defaultBranchRef.target.history", {"totalCount", "nodes"}, {"totalCount", "nodes"}), #"Expanded nodes" = Table.ExpandListColumn(#"Expanded Column1.defaultBranchRef.target.history", "nodes"), #"Expanded nodes1" = Table.ExpandRecordColumn(#"Expanded nodes", "nodes", {"committedDate", "additions", "deletions", "url", "author"}, {"committedDate", "additions", "deletions", "url", "author"}), #"Expanded author" = Table.ExpandRecordColumn(#"Expanded nodes1", "author", {"name", "email"}, {"name", "email"}), #"Renamed Columns" = Table.RenameColumns(#"Expanded author",{{"Column1.name", "Repo"}}) in #"Renamed Columns"

Query where I'm trying to page and get more than the first 100 commits. Ideally, I'd like to do the repo also, but I can work with a smaller date range to limit the # of repos. I'm currently getting "Error: The parameter is expected to be of type Text.Type or Binary.Type
Details:
data=[Record]"
It seems to be having a hard time with the defaultBranchref list.

let FnGetOnePage = (cursor) => let Source = Json.Document(Web.Contents( "https://api.github.com/graphql", [ Headers=[ #"Method"="POST", #"Content-Type"="application/json", #"Authorization"="Bearer <token>" ], Content=Text.ToBinary(Text.Replace("{""query"": ""query _Welcome_to_Altair_G518($cursor: String){ search(query: \""org:<org>\"", type: REPOSITORY, first: 100) { nodes { ... on Repository { name defaultBranchRef { name target { ... on Commit { history(first: 100, since: \""2024-01-29T00:00:00\"", after:$cursor) { totalCount nodes { ... on Commit { committedDate additions deletions url author { name email } } } pageInfo { endCursor hasNextPage } } } } } } } }}""}", "($cursor: String)", "($cursor: String" & cursor & ")")) ] )), #"JSON" = Json.Document(Source), data = JSON[data], search = data[search], nodes = search[nodes], defaultBranchRef = nodes[defaultBranchRef], target = defaultBranchRef[target], history = target[history], pageInfo = history[pageInfo], hasNextPage = pageInfo[hasNextPage], endcursor = pageInfo[endCursor], res = [Data=nodes, Cursor=endcursor, HasNext=hasNextPage] in res, Pages = List.Generate( ()=>[res = FnGetOnePage("")], each [res][Cursor] <> null, each [res = try FnGetOnePage("=\""" & [res][Cursor] & "\""") otherwise null], each if [res] <> null then [res][Data] else null ), #"Converted to Table" = Table.FromList(Pages, Splitter.SplitByNothing(), null, null, ExtraValues.Error) in #"Converted to Table"