Skip to content

Instantly share code, notes, and snippets.

@petrsvihlik
Last active August 27, 2024 14:13
Show Gist options
  • 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"
@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