Skip to content

Instantly share code, notes, and snippets.

@petrsvihlik
Last active February 16, 2024 17:16
Show Gist options
  • Star 23 You must be signed in to star a gist
  • Fork 5 You must be signed in to fork a gist
  • Save petrsvihlik/cc34a6cf1882d515139c5b27f37cf99e to your computer and use it in GitHub Desktop.
Save petrsvihlik/cc34a6cf1882d515139c5b27f37cf99e to your computer and use it in GitHub Desktop.
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"
@petrsvihlik
Copy link
Author

thanks for the heads up, I updated the sample!

@michelsolognier
Copy link

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
Copy link
Author

@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
Copy link

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

@petrsvihlik
Copy link
Author

petrsvihlik commented Oct 9, 2020 via email

@mikefede
Copy link

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
Copy link
Author

@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
Copy link

@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
Copy link
Author

@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
Copy link

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
Copy link

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
Copy link

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
Copy link

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
Copy link

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
Copy link

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
Copy link
Author

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
Copy link

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
Copy link
Author

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
Copy link

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
Copy link

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?

@petrsvihlik
Copy link
Author

@Wisteru you are correct, I was able to reproduce it. will try to come up with a fix.

@petrsvihlik
Copy link
Author

@Wisteru see this: https://www.thebiccountant.com/2020/05/15/miss-last-page-paging-power-bi-power-query/

Ben Gribaudo says:

The way documentation describes List.Generate’s third argument (the test function) can be a confusing. That argument is applied to the just-fetched result (page, in this case) to determine both whether to return the current result (page) and whether to try to fetch another result (page)—which will then be handed to the test function which will then determine whether it should be returned and another fetched, and so on…

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.

@talynone
Copy link

talynone commented May 6, 2021

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,

@petrsvihlik
Copy link
Author

@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        
      }
    }
  }
}

@Amby21
Copy link

Amby21 commented Jun 21, 2022

@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]

@petrsvihlik
Copy link
Author

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.

@Amby21
Copy link

Amby21 commented Jun 24, 2022

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

@irykutrfk
Copy link

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.

@elimey
Copy link

elimey commented Oct 2, 2022

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

@petrsvihlik
Copy link
Author

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.

@desaim
Copy link

desaim commented Oct 13, 2022

@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.

@desaim
Copy link

desaim commented Oct 13, 2022

@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

@SantiagoMendozaM
Copy link

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

@alexalvescrp
Copy link

@brandonBotz
Copy link

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"

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