How to connect to a SOAP API in Power BI/ Power Query

[@MyGarageWorkshop]

❤ Thank you so much! I have been trying to find a solution to this for quite some time. Excellent!

[@CurbalEN]

My pleasure!

[@joanneosborne2428]

Amazing! Thanks, Ruth, I love all your training videos. ❤❤

[@CurbalEN]

Thanks ❤️❤️❤️

[@bjcomrie]

This was brilliant. Thanks for the effort.

[@flaviocarvalhokato6109]

God bless you. You saved me. Thank you very much!

[@CurbalEN]

😃

[@Sterh20]

Very helpful video. You saved me a lot of time. Thank you.

[@CurbalEN]

Music to my ears :)

[@raf_martins]

Im so greatful! Really thank you. It help me a Lot.

[@Silencioraul]

You are a genius !!! Thank you very much.

[@CurbalEN]

☺️

[@jregusci]

Thank you for the video! Also, you don´t need to encode the double quotes, it works fine using the double quotes directly.

[@CurbalEN]

Thanks!

[@vbonetti]

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! 😅

[@CurbalEN]

I dont have a video, but if you ask in the power bi community somebody probably has done it :)

[@alex1s85alex3]

Hi, i really love your videos. Is there a way to make soap get requests with sha256 encryption using a private key?

[@Milhouse77BS]

SOAP :) What 90's madness is this? :) I did my share 10-20 years ago :)

[@CurbalEN]

😂😂

[@arossi901]

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

[@CurbalEN]

Web queries require a personal gateway :(

[@arossi901]

@@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 ?

[@CurbalEN]

@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/

[@l3rvn0]

It's possible do the same using a .svc SOAP webservice?

[@Aaaryaa]

@l3vrn0 - Did you get answer on this ?

[@l3rvn0]

​@@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

[@danieltellesdemenezes8587]

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?

[@AlexisKatopodis]

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?

[@CurbalEN]

Yes, you can. You are probably doing something wrong :(

[@bravucod]

What is a good resource to learn about apis with power query ?

[@CurbalEN]

I have a section dedicated for apis: curbal.com/curbal-learning-portal

[@bravucod]

@@CurbalEN you are awesome thanks

[@talihiggins5947]

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!

[@CurbalEN]

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

[@amrutapatil7159]

I have tried by following what you have done but i have get internal error could please help me

[@leemeiwah]

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! 😅😅

[@CurbalEN]

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.

[@leemeiwah]

@@CurbalEN Thank you! 🙂

[@amrutapatil7159]

I have tried but not get any data could please help me

[@CraigEvans-gd2er]

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

[@CurbalEN]

Convert the int to text and you are good to go :)

[@CraigEvans-gd2er]

Actually my mistake was as simple as changing the SOAP envelope from vstr to vint

[@CurbalEN]

It is always the smallest things that break the code 🤦‍♀️, but on the bright side: congrats for finding it !! 🥳

[@ahv5457]

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")

[@pritichaudhari7596]

how to get Data from RESTful API which has "two Factor Authentication" e.g. Maconomy RESTful API framework.