Put API with Body

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,

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.

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

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 

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.net)

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.

This works when I remove the outputMIME.