Put API with Body

[LarryBroward]

Hi,

I need to pass the following in the body,

<?xml version="1.0" encoding="utf-8" standalone="yes"?>

<ns2:invocationContext xmlns:ns2="http://collectionspace.org/services/common/invocable">

<mode>nocontext</mode>

</ns2:invocationContext> 

I am doing a "Put" but I am getting an excel report, so nothing is being updated a new instance of a report is being created. Is this possible in power query or does it need to be in an application.

I was lookking at this but I wasn't able to get it going with all the special characters. The type needs to be application/xml.

Thanks

`

[ImkeF · Accepted solution]

Hi @LarryBroward ,
I'm not used to work with xml files.
Have you tried

Xml.Document(Web.Contents(url, [Headers=headers, Content = postData ] ) )

or 

Excel.Workbook(Web.Contents(url, [Headers=headers, Content = postData ] ) )

instead?

[LarryBroward]

This works when I remove the outputMIME.

[ImkeF]

Hi @LarryBroward ,
what will be returned if you use this syntax?:

let
//apiKey = "your_api_key_here", -- Api Doesn't require a key
username = PARAM_API_USERNAME,
password = PARAM_API_USER_PASSWORD,
auth = "Basic " & Binary.ToText(Text.ToBinary(username & ":" & password), BinaryEncoding.Base64),
apiUrl = PARAM_API_BASE_URL,

postData = Text.ToBinary(

//Excel is the default and returns without an OutputMime using postman

"<?xml version=""1.0"" encoding=""utf-8"" standalone=""yes""?>
<ns2:invocationContext xmlns:ns2=""http://collectionspace.org/services/common/invocable"">
<mode>nocontext</mode>
<outputMIME>application/xml</outputMIME>
</ns2:invocationContext> "
),

url = apiUrl , headers = [ #"Authorization" = auth,#"Content-Type" = "application/xml"],
source = Xml.Tables(Web.Contents(url, [Headers=headers, Content = postData ] ) )
in
source 

[LarryBroward]

The source is returned in a single line of XML.

It puts the report in a single column called "page.text.textContent.Element:Text" when expanded down. Then I have to pivot every 16 lines for the 16 different columns, which is a workaround but their is a bit more work on my end for setup and I can't be sure it will work in every situation.  It would be nice if I can get the whole excel returned that works from the UI and Postman.

Thanks,

[ImkeF]

Hi @LarryBroward ,
that sounds interesting. Never tried it.
Which error message did you get and how did your code look like?

[LarryBroward]

I did get passed the authentication error. But I wasn’t able to return the excel file I was expecting.

Here’s the code, let me know if you see anything. The error I am getting in the response is Expression.Error: The parameter is expected to be of type Text.Type or Binary.Type. Details:    [Table].

The source works and I can see the xml but the data I am looking for is all put in one column and isn’t formatted in a way that I can use it easily.

Reporting Service RESTful APIs - CollectionSpace Unreleased Documentation - Confluence (atlassian.ne...

let
//apiKey = "your_api_key_here", -- Api Doesn't require a key
username = PARAM_API_USERNAME,
password = PARAM_API_USER_PASSWORD,
auth = "Basic " & Binary.ToText(Text.ToBinary(username & ":" & password), BinaryEncoding.Base64),
apiUrl = PARAM_API_BASE_URL,

postData = Text.ToBinary(

//Excel is the default and returns without an OutputMime using postman

"<?xml version=""1.0"" encoding=""utf-8"" standalone=""yes""?>
<ns2:invocationContext xmlns:ns2=""http://collectionspace.org/services/common/invocable"">
<mode>nocontext</mode>
<outputMIME>application/xml</outputMIME>
</ns2:invocationContext> "
),

url = apiUrl , headers = [ #"Authorization" = auth,#"Content-Type" = "application/xml"],
source = Xml.Tables(Web.Contents(url, [Headers=headers, Content = postData ] ) ),

xmlResponse = Xml.Document(source)
in
xmlResponse

Postman- Send and download gives me an excel file.

[ImkeF]

Hi @LarryBroward ,
that's correct. To use Basic Authentication, you would have to build a custom connector.
Handling authentication for Power Query connectors - Power Query | Microsoft Learn

[LarryBroward]

I was looking at this post Accessing REST APIs with Basic Auth and API Key in Power Query | John Dalesandro

It is suggesting that you can get it to work without using a customer connector, by builiding one URL.  Is this a viable option, I played with it a little bit and can see the key is coming back, but I wasn't able to get it to work.

Thanks 

[ImkeF]

Hi @LarryBroward ,
for each quote in your text, you have to escape it with another quote. Other special characters don't matter here.
This is the general setup:

let
    url = ...,
    headers = [#"Content-Type" = "application/xml"],
    postData = Text.ToBinary(
"<?xml version=""1.0"" encoding=""utf-8"" standalone=""yes""?>
<ns2:invocationContext xmlns:ns2=""http://collectionspace.org/services/common/invocable"">
<mode>nocontext</mode>
</ns2:invocationContext> "
),
    response = Web.Contents(
        url,
        [
            Headers = headers,
            Content = postData
        ]
    ),
    xmlResponse = Xml.Document(response)
in
    xmlResponse

[LarryBroward]

Thats work as far as the syntax goes I was trying to_date('"&Date.ToText(sDate)&"' , 'mm/dd/yyyy') the '"& &"' that I use for my date paramaters. However i am getting this error "Web.Contents with the Content option is only supported when connecting anonymously" . I am using the same setup with a raw body in Postman with Basic Authenticationand it work fine. I am not sure how to get around this in Power BI if that is the actual error.

Thanks

[ImkeF]

Hi @LarryBroward ,
please share your resources that say PUT calls are possible through Power Query.
That's new to me.

[LarryBroward]

This is a put with a body, it should be able to handle it according to other posts out there. I need a  working example that I can follow that uses the same type of body I need to pass or confirmation that this can't be done in power bi. 

[ImkeF]

Hi @LarryBroward ,
Power Query can only do GET and POST calls.

[LarryBroward]

I meant Post. I just realized the code sample I put in from another post which would have been clear was removed by the content screeners on submit.