Connection to GraphQL Works only with some fields

[cillepersille]

Hello!

I am having issues with a query that gets data from Microsoft GraphQL.
I am working on a report on resource management, and i am trying to get out labels from graphQL. I am able to get most of the data, but the data called labels, which is of type Object, I am unable to get it. I feel like I tried everything, but I keep getting the error: 
Expression.Error: 
We cannot apply field access to the type Null

Details:
Value=
Key=report

Below is what the query looks like with labels in it, it does not work. I tried with all the metadata for the LabelsObjetType, but noothing seems to do the trick. Once I remove "Labels" I am able to get the rest of the data in here. Please let me know what I am doing wrong!

let

    vUrl = "https://XXXX.com/graphql",

    vHeaders =[

#"Method"="POST",

#"Content-Type"="application/json",

#"Authorization"="Bearer {Token}"

],

    // Notice the quote escaping here

    vContent=Text.ToBinary("{""query"":""query PowerBI {   report(preset: \""" & QUERY_PRESET & "\"") {     startDateTime  endDateTime  customer { name }  project { name tag labels { description }}   resource { displayName }  title     }}""}"),

    Source = Web.Contents(vUrl, [Headers=vHeaders, Content=vContent]),

    #"JSON" = Json.Document(Source),

    data = JSON[data][report]

in

    data

[hackcrr · Accepted solution]

Hi, @cillepersille 

From the information you provided, "We cannot apply field access to the type Null" indicates that the value of the field you are trying to access is null, which usually means that the API is not returning the data you expect, or that the structure of the returned data does not exactly match your query. expects.
First, make sure that your GraphQL query is correct and that the labels field actually exists in the project object returned by the API. If the labels field exists in some project objects and not in others, you will get this error when you try to access the non-existent labels field.
To resolve this issue, you can try the following steps:
Validate the GraphQL query: run your query in GraphQL or another GraphQL testing tool to make sure it returns the data structure you expect. This can help you verify that the API is indeed returning labels fields.
Handling null values: In Power BI's M query, you need to check each project object to see if it contains the labels field and handle null values accordingly. For example, you can use the tryotherwise function to catch null values and return an empty list or other default value.
Adjusting the query structure: If the labels field is not required for all project objects, you may need to modify the query logic to handle this situation. For example, you could first check to see if the labels field exists, and then try to access its subfields.

You may refer to the following M code:

let  
    vUrl = "https://XXXX.com/graphql",  
    vHeaders = [  
        #"Method"="POST",  
        #"Content-Type"="application/json",  
        #"Authorization"="Bearer {Token}"  
    ],  
    vContent = Text.ToBinary("{""query"":""query PowerBI { report(preset: \""" & QUERY_PRESET & "\"") { startDateTime endDateTime customer { name } project { name tag labels { description } } resource { displayName } title } }""}"),  
    Source = Web.Contents(vUrl, [Headers=vHeaders, Content=vContent]),  
    #"JSON" = Json.Document(Source),  
    data =   
        let  
            reports = #"JSON"[data][report],  
            projectsWithLabels = List.Transform(reports[project], each if Record.HasFields(_, "labels") then [  
                name = _[name],  
                tag = _[tag],  
                labels = try _[labels][description] otherwise null   
            ] else [  
                name = _[name],  
                tag = _[tag],  
                labels = null   
            ])  
        in  
            projectsWithLabels  
in  
    data

Note that this example assumes that the labels field is an array of objects containing the description field. If the structure of the labels field is different, you will need to adjust the query logic accordingly. In addition, you may need to do further processing on the returned labels field to convert it to a format that Power BI can understand.

Best Regards,

hackcrr

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

[wdrain]

Hi HacKrr
Your post on using GraphQL with Power Query was very helpful!

Including escape backslash was very helpful  i.e. \""Variable Name\"" for my GraphQL query.

How would you escape a GraphQL query variable name that has a hypen i.e.   \""Variable - Name\""?

With the Hypen it does not work.

Any help would be much appreciated.

[cillepersille]

Hi, thank you so much for your quick and detailed response. I did have a talk with the developer of the program, who doesn't seem to understand why it's not working either. I am getting errors when running the query in a testing tool, getting feedback that I don't have access, so this might be the root of the issue too. But then again, I don't know why the other values do work. I looked in the dataset, seems there are a lot of rows in the data where the label actually has no value, so might this be the issue? Some do have, others dont. And also I tried your code, sadly didn't work, so I might need another talk with the developer. Thank you so much, though, really appreciate it.

{

  "errors": [

    {

      "message": "Access denied! You need to be authenticated to perform this action!"

    }

  ]

}