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! 😅
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?
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 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/
@@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
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?
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
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.
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")
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
❤ Thank you so much! I have been trying to find a solution to this for quite some time. Excellent!
My pleasure!
Amazing! Thanks, Ruth, I love all your training videos. ❤❤
Thanks ❤️❤️❤️
This was brilliant. Thanks for the effort.
Thank you for the video!
Also, you don´t need to encode the double quotes, it works fine using the double quotes directly.
Thanks!
God bless you. You saved me. Thank you very much!
😃
Im so greatful! Really thank you. It help me a Lot.
Very helpful video. You saved me a lot of time. Thank you.
Music to my ears :)
Hi, i really love your videos.
Is there a way to make soap get requests with sha256 encryption using a private key?
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.
☺️
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?
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/
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
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 :(
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 have tried by following what you have done but i have get internal error could please help me
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
how to get Data from RESTful API which has "two Factor Authentication" e.g. Maconomy RESTful API framework.
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")
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 !! 🥳
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