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
Hi,
I need to pass the following in the body,
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
Solved! Go to 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?
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
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.
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,
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.
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.
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
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
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
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.
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
68 | |
62 | |
22 | |
18 | |
12 |