GraphQL Post query to Power Query format

[Anonymous]

Hi all,

I am trying to convert the following GraphQL post query to a format that is supported by Power Query:

{
  people(first: 100, view: all, filter: {id: {values:"123456789ABC"}}) {
    nodes {
      id
      name
      primaryEmail
      contacts(first: 100) {
        nodes {
          label
          uri
        }
      }
    }
  }
}

Without the ID filter, I am able to get this to work. However, I want to be able to filter directly on an ID (this will be replaced by a variable later, called PersonID). At the moment, I have:

//(PersonID as text) as table =>
let
    vUrl = "https://graphql.###.com/",
    vHeaders =[
			#"Method"="POST",
			#"Content-Type"="application/json",
			#"Authorization"="Bearer 12345ABC"
		],
    // Notice the quote escaping here
    vContent=Text.ToBinary("{""query"": ""{people(first: 100, view: all) {nodes {id name primaryEmail contacts(first:100){nodes {label uri}}}}}""}"),
    Source = Web.Contents(vUrl,	[Headers=vHeaders, Content=vContent]),

My idea was to get it to work the following way:

(PersonID as text) as table =>
let
    vUrl = "https://graphql.###.com/",
    vHeaders =[
			#"Method"="POST",
			#"Content-Type"="application/json",
			#"Authorization"="Bearer 1234ABC"
		],
    // Notice the quote escaping here
    vContent=Text.ToBinary("{""query"": ""{people(first: 100, view: all, filter: {id: {values:123456789ABC}}) {nodes {id name primaryEmail contacts(first:100){nodes {label uri}}}}}""}"),
    Source = Web.Contents(vUrl,	[Headers=vHeaders, Content=vContent]),
    #"JSON" = Json.Document(Source)
in
    #"JSON"

 This returns an error, however: Argument 'values' on InputObject 'IDFilter' has an invalid value (123456789ABC). Expected type '[ID!]!'.

Other variations on filtering the ID has only given me HTTP 400 errors. 

I hope anyone can help me out! 🙂

[Natialuk]

Hi, I tried to use the format but didn't manage to convert it to Power Query. Can someone please assist with translating the following GraphQL query:

query {

  courseProgress {

    nodes {

      user{email}

            courseId

            courseIdNum

            percentComplete

            isCourseCompleted

            completedOn

            courseSeconds

            totalWatchedSeconds

            totalClipsWatched

            firstViewedClipOn

            lastViewedClipOn

            planId

            updatedOn

    }

  }

}

[Anonymous · Accepted solution]

I have found the solution. The fix was to add two backslashes around the ID, as follows:

//(PersonID as text) as table =>
let
    vUrl = "https://graphql.###.com/",
    vHeaders =[
			#"Method"="POST",
			#"Content-Type"="application/json",
			#"Authorization"="Bearer 1234ABC"
		],
    // Notice the quote escaping here
    vContent=Text.ToBinary("{""query"": ""{people(first: 100, view: all, filter: {id: {values:\""123456789ABC\""}}) {nodes {id name primaryEmail contacts(first:100){nodes {label uri}}}}}""}"),
    Source = Web.Contents(vUrl,	[Headers=vHeaders, Content=vContent]),
    #"JSON" = Json.Document(Source)
in
    #"JSON"

As a function:

(PersonID as text) as table =>
let
    vUrl = "https://graphql.###.com/",
    vHeaders =[
			#"Method"="POST",
			#"Content-Type"="application/json",
			#"Authorization"="Bearer 1234ABC"
		],
    // Notice the quote escaping here
    vContent=Text.ToBinary("{""query"": ""{people(first: 100, view: all, filter: {id: {values:\""" & PersonID & "\""}}) {nodes {id name primaryEmail contacts(first:100){nodes {label uri}}}}}""}"),
    Source = Web.Contents(vUrl,	[Headers=vHeaders, Content=vContent]),
    #"JSON" = Json.Document(Source)
in
    #"JSON"

[Icey]

Hi @Anonymous ,

I know little about GraphQL Post query. Just one speculation:

Does the issue caused by the absence of double quotes of id value?

(PersonID as text) as table =>
let
vUrl = "https://graphql.###.com/",
vHeaders =[
#"Method"="POST",
#"Content-Type"="application/json",
#"Authorization"="Bearer 1234ABC"
],
// Notice the quote escaping here
vContent=Text.ToBinary("{""query"": ""{people(first: 100, view: all, filter: {id: {values:"123456789ABC"}}) {nodes {id name primaryEmail contacts(first:100){nodes {label uri}}}}}""}"),
Source = Web.Contents(vUrl, [Headers=vHeaders, Content=vContent]),
#"JSON" = Json.Document(Source)
in
#"JSON"

In addition, here is a document about GraphQL API + PowerBI Integration. Hope it helps.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Icey,

Thanks for your input. I've a couple of variations with quotes, resulting in the following:

id: {values:""123456789ABC""} > HTTP 400 error

id: {values:"123456789ABC"} > Token comma expected

id: {values:'123456789ABC'} > Parse error on "'" (error) at [1, 53]

id: {values:123456789ABC} > Argument 'values' on InputObject 'IDFilter' has an invalid value (123456789ABC). Expected type '[ID!]!'.

As you can see, nothing will work 😞