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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
LarryBroward
Frequent Visitor

Put API with Body

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

 

`
1 ACCEPTED SOLUTION
ImkeF
Super User
Super User

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?

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

14 REPLIES 14
ImkeF
Super User
Super User

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?

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

This works when I remove the outputMIME.

ImkeF
Super User
Super User

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 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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
Super User
Super User

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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.

LarryBroward_0-1695219707106.png

 

LarryBroward_1-1695219858903.png

 

 

ImkeF
Super User
Super User

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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
Super User
Super User

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

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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
Super User
Super User

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

LarryBroward
Frequent Visitor

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
Super User
Super User

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

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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.

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Solution Authors