Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
eyelikedata2
Helper I
Helper I

Can connect to API from PBI Desktop but cannot complete CURL example for data binary element - Help!

Hello,

 

I have an API that I am able to connect to and are authorized, but I cannot seem to get the data binary part of the cURL example working. How do I enter something like this in PBI as my understanding is in the body, not header.

 

curl 'https://hostname:443/incidents/search' -H 'content-type: application/json' -H 'accept: application/json' -H 'Authorization: <API Key goes here>' --data-binary '{"filter":{"query":"-status:closed -category:job","period":{"by":"day","fromValue":7}}}' --compressed
1 ACCEPTED SOLUTION

I thought I was to use Example 2

That's my mistake, apologies.  Indeed I wanted you to look primarily at example 1 which shows both RelativePath and Query. But your sample curl script indicates that all filter parameters are in the POST body, so the Query part is not relevant (the RelativePath part is).

 

You can convert the curl -d value into a Power Query Content value like this

CURL:

{"filter":{"query":"-status:closed -category:job","period":{"by":"day","fromValue":7}}}

Power Query:

Text.ToBinary("{""filter"":{""query"":""-status:closed -category:job"",""period"":{""by"":""day"",""fromValue"":7}}}")

 

View solution in original post

9 REPLIES 9
lbendlin
Super User
Super User

See example 2 for one option.

 

Web.Contents - PowerQuery M | Microsoft Learn

Here's what I came up with to connect Palo Alto Networks XSOAR formerly Demisto with Power BI based upon your example and the documentation at https://github.com/demisto/demisto-py/blob/master/docs/DefaultApi.md#search_incidents:

 

let
base_url = "https://hostname:443",
api_key = "<Your API Key>",
url = base_url & "/incidents/search",
headers = [
#"Content-Type" = "application/json",
#"Accept" = "application/json",
#"Authorization" = api_key
],
filter = [
"filter" = [
"query" = "-status:closed -category:job",
"period" = [
"by" = "day",
"fromValue" = 7
]
]
],
body = Json.FromValue(filter),

// Make the API request
response = Web.Contents(
url,
[
Headers = headers,
Content = body,
ManualStatusHandling = {404} // Handle 404 errors if needed
]
),

// Handle response
jsonResponse = Json.Document(response)
in
jsonResponse

 

I also found that I got an Invalid identifier error with "filter" highlighted. Hmm, maybe this will work?

 

let
base_url = "https://hostname:443",
api_key = "<Your API Key>",
url = base_url & "/incidents/search",
headers = [
#"Content-Type" = "application/json",
#"Accept" = "application/json",
#"Authorization" = api_key
],
filterPayload = [
"filter" = [
"query" = "-status:closed -category:job",
"period" = [
"by" = "day",
"fromValue" = 7
]
]
],
body = Json.FromValue(filterPayload),

// Make the API request
response = Web.Contents(
url,
[
Headers = headers,
Content = body,
ManualStatusHandling = {404} // Handle 404 errors if needed
]
),

// Handle response
jsonResponse = Json.Document(response)
in
jsonResponse

 

 

@lbendlinI keep getting an invalid identifier on "filter" = [ in all the examples. I'm going to try something like this next but welcome your feedback or anyone else in the community.

let
    base_url = "https://hostname:443",
    api_key = "<Your API Key>",
    url = base_url & "/incidents/search",
    headers = [
        #"Content-Type" = "application/json",
        #"Accept" = "application/json",
        #"Authorization" = api_key
    ],
    filterJson = [
        "filter" = [
            "query" = "-status:closed -category:job",
            "period" = [
                "by" = "day",
                "fromValue" = 7
            ]
        ]
    ],
    bodyText = Text.FromBinary(Json.FromValue(filterJson)),
    
    // Create a custom request
    request = Http.Request(url, [
        Content = Text.ToBinary(bodyText), // Convert payload to binary
        Headers = headers,
        ManualStatusHandling = {404} // Handle 404 errors if needed
    ]),
    
    // Make the API request using Web.Contents
    response = Web.Contents(request),
    
    // Handle response
    jsonResponse = Json.Document(response)
in
    jsonResponse

 

Please read the documentation again and use the RelativePath feature.

 

Do you also have documentation that is not Python library specific?

My misunderstanding @lbendlin I thought I was to use Example 2 but you suggest to use Example 1 that would return that output type or is there another reason for the use of relative path?

 

Also, I'm sorry but all their documentation is python or cURL specific. The cURL example was one taken from here, but there are more examples found at https://docs-cortex.paloaltonetworks.com/r/Cortex-XSOAR-API/Search-incidents-by-filter

I did find the following while playing with the developer tab in firefox. I noticed it used --data-raw instead of --data-binary and more fields as referenced in the API docs I provided. I modified my M query as follows but still needs improvement.

 

let
    base_url = "https://hostname:443",
    api_key = "<Your API Key>",
    url = base_url & "/incidents/search",
    headers = [
        #"Content-Type" = "application/json",
        #"Accept" = "application/json",
        #"Authorization" = api_key
    ],
    filterJson = [
        "userFilter" = false,
        "": [
            "page" = 0,
            "size" = 50,
            "query" = "investigation:id:* -status:Closed -category:job",
            "sort" = {[{"field"="modified", "asc"=false}]},
            "period" = {"by"="day", "fromValue"=7}
        }
    ],
    bodyText = Text.FromBinary(Json.FromValue(filterJson)),
    
    // Create a custom request
    request = Http.Request(url, [
        Content = Text.ToBinary(bodyText), // Convert payload to binary
        Headers = headers,
        ManualStatusHandling = {404} // Handle 404 errors if needed
    ]),
    
    // Make the API request using Web.Contents
    response = Web.Contents(request),
    
    // Handle response
    jsonResponse = Json.Document(response)
in
    jsonResponse

 

I thought I was to use Example 2

That's my mistake, apologies.  Indeed I wanted you to look primarily at example 1 which shows both RelativePath and Query. But your sample curl script indicates that all filter parameters are in the POST body, so the Query part is not relevant (the RelativePath part is).

 

You can convert the curl -d value into a Power Query Content value like this

CURL:

{"filter":{"query":"-status:closed -category:job","period":{"by":"day","fromValue":7}}}

Power Query:

Text.ToBinary("{""filter"":{""query"":""-status:closed -category:job"",""period"":{""by"":""day"",""fromValue"":7}}}")

 

eyelikedata2
Helper I
Helper I

Could I do something like this?

 

Here is the new code:

let
body= [GrantType="**",Username="**",Password="***",ClientId="***",ClientSecret=""],
Source = Json.Document(Web.Contents("https://****/token",
    [ 
     Headers=[#"Content-Type"="application/json"],
     Content= Json.FromValue(body)
    ]))
in
    #"Source"

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.