How to connect to a SOAP API in Power BI/ Power Query
HTML-код
- Опубликовано: 9 янв 2023
- Learn how to connect to a SOAP API in power query!.
Link to service:
www.w3schools.com/xml/tempcon...
Join this channel membership to get access to all the recorded bites as they become available:
/ @curbalen
Here you can download all the pbix files: curbal.com/donwload-center
SUBSCRIBE to learn more about Power and Excel BI!
/ @curbalen
Our PLAYLISTS:
- Join our DAX Fridays! Series: goo.gl/FtUWUX
- Power BI dashboards for beginners: goo.gl/9YzyDP
- Power BI Tips & Tricks: goo.gl/H6kUbP
- Power Bi and Google Analytics: goo.gl/ZNsY8l
☼☼☼☼☼☼☼☼☼☼
POWER BI COURSES:
Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:
curbal.com/courses-overview
☼☼☼☼☼☼☼☼☼☼
ABOUT CURBAL:
Website: www.curbal.com
Contact us: www.curbal.com/contact
QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Linkedin ► goo.gl/3VW6Ky
Mastodon ► @ruthpozuelo@techhub.social
Twitter ► @curbalen
Facebook ► goo.gl/bME2sB
#CURBAL #SUBSCRIBE
God bless you. You saved me. Thank you very much!
😃
Amazing! Thanks, Ruth, I love all your training videos. ❤❤
Thanks ❤️❤️❤️
Very helpful video. You saved me a lot of time. Thank you.
Music to my ears :)
This was brilliant. Thanks for the effort.
Im so greatful! Really thank you. It help me a Lot.
amazing video! so useful and the only source available on this topic that I found - thanks so much! Is there a follow up video to learn how to add a parameter in that code so that the value of the main variable can be set in an excel cell for example? ☺That would be so good! 😅
I dont have a video, but if you ask in the power bi community somebody probably has done it :)
You are a genius !!! Thank you very much.
☺️
Hi, i really love your videos.
Is there a way to make soap get requests with sha256 encryption using a private key?
SOAP :) What 90's madness is this? :) I did my share 10-20 years ago :)
😂😂
Great video! It has helped me starting a Power Query (Excel) connection to a webservice. However, although the ws works with SOAP UI, I'm getting an error in Power Query. "The underlying connection was closed: unexpected error on a send." Any suggestions?
I haven't seen that changing the " to #(0022) before. Is that a Power Query thing, or does it work in more languages? I know I've done things like changing a single quote to a double quote, etc, in Python before - could you have just changed it to a single quote? Very curious about this!
It is because it is inside Text.ToBinary so it gets encoded properly.
You can also use Character.FromNumber(34)
community.powerbi.com/t5/Desktop/How-to-get-data-from-a-SOAP-WS-with-Power-BI/td-p/29040
I tried to follow you to see the actual data (read method) of the call to the SOAP web service, but I saw only the column names. Do you believe that I can see them?
Yes, you can. You are probably doing something wrong :(
Works great in Power Query, although I have just encountered a 400 error when the SOAP envelope calls for an Integer (Int) rather than a string value, or at least I think that is the problem because it is the only parameter that has changed
Convert the int to text and you are good to go :)
Actually my mistake was as simple as changing the SOAP envelope from vstr to vint
It is always the smallest things that break the code 🤦♀️, but on the bright side: congrats for finding it !! 🥳
I have tried by following what you have done but i have get internal error could please help me
how to get Data from RESTful API which has "two Factor Authentication" e.g. Maconomy RESTful API framework.
Thank you very much! My report in PowerBi works correctly with 3 soap data sources, however the data update only works locally with PowerBi desktop, when I publish the report I have not found a way to have an update via gateway when one wants as for the other reports, I can schedule it but it is not on request Is there a way to do it ? The correct behaviour shoul be the same of a report with direct query through gateway thanks
Web queries require a personal gateway :(
@@CurbalEN With other PBI reports I use the Microsoft On-premises data gateway, this gateway works for direct query (update on the spot when user ask it) and import (update scheduled in the semantic models). What could be a personal gateway for replicate the update per request with a Soap data source ? I could write something in c# that every 5 minutes push data in a msql table or directly with msql server a stored procedure that take the data from soap for store it in a table. What do you suggest ?
@arossi901 absolutely, you can also push to a pbi dataset:
powerbi.microsoft.com/it-ch/blog/push-rows-to-a-power-bi-streaming-dataset-without-writing-any-code-using-microsoft-flow/
Hi,
I am getting data from an API that has two parameters like below in the URL:
Param1930=2021-01-01&Param1931=2030-12-31&format=JSON
These are the start and end dates of the report that I have manfully set up in the API URL. But, how do I make it dynamic so that everyday that I refresh the data in Power BI connect to the API and pull the latest data? My current M code is something like below:
let
Source = Json.Document(File.Contents("C:\Users\Ali\AppData\Local\Microsoft\Windows\INetCache\IE\EHD7MHFN\1120")),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"txtProvider",
"txtIncidentNo",
"FltDate",
"LogTime")
What is a good resource to learn about apis with power query ?
I have a section dedicated for apis:
curbal.com/curbal-learning-portal
@@CurbalEN you are awesome thanks
Hi Ruth, I've tried by following what u've done but I've a (500) Internal Server Error. I've tried to download your pbix file but unable to find it. Pls help. Thks! 😅😅
Yes, I should put it in the download Center.
I got the 500 error when I had a syntax mistake. Review your code or better yet, start from scratch and carefully paste the code .
Will put the file on the DC on Monday.
@@CurbalEN Thank you! 🙂
I have tried but not get any data could please help me
It's possible do the same using a .svc SOAP webservice?
@l3vrn0 - Did you get answer on this ?
@@Aaaryaa
I was able to successfully connect to my svc webservice using a PowerQuery.
Something like this:
let
url = "",
SOAPEnvelope =
"",
options = [
#"Content-Type"="text/xml;charset=utf-8",
#"SOAPAction"=""
],
Source = Xml.Tables(Web.Contents(url, [Content=Text.ToBinary(SOAPEnvelope), Headers = options]))
in
Source