Help creating session ID

Question

Hello,&nbsp;I am trying to use PowerBI to extract data from our accountancy software.&nbsp;&nbsp;However, every refresh has a different SessionID. I tried circumventing this by first getting the sessionID and then using that in another query. Below is the code for getting the sessionID.&nbsp;Spoiler (Highlight to read)let
 
Source = Xml.Tables(Web.Contents(""&amp; URL_Logon &amp;""
 
,[Content=Text.ToBinary("
&lt;soap:Envelope xmlns:soap="&amp;Character.FromNumber(34)&amp;"http://schemas.xmlsoap.org/soap/envelope/"&amp;Character.FromNumber(34)&amp;" xmlns:xsi="&amp;Character.FromNumber(34)&amp;"http://www.w3.org/2001/XmlSchema-instance"&amp;Character.FromNumber(34)&amp;" xmlns:xsd="&amp;Character.FromNumber(34)&amp;"http://www.w3.org/2001/XmlSchema"&amp;Character.FromNumber(34)&amp;"&gt;
 
&lt;soap:Body&gt;

&lt;Logon xmlns="&amp;Character.FromNumber(34)&amp;"http://www.twinfield.com/"&amp;Character.FromNumber(34)&amp;"&gt;
&lt;user&gt;"&amp; User &amp;"&lt;/user&gt;
&lt;password&gt;"&amp; Password &amp;"&lt;/password&gt;
&lt;organisation&gt;"&amp; Organisation &amp;"&lt;/organisation&gt;
&lt;/Logon&gt;
&lt;/soap:Body&gt;
&lt;/soap:Envelope&gt;
 
"),Headers=[#"Content-Type"="text/xml;charset=UTF-8"]])),
    #"Table uitgevouwen" = Table.ExpandTableColumn(Source, "Table", {"Name", "Table"}, {"Table.Name", "Table.Table"}),
    #"Table.Table uitgevouwen" = Table.ExpandTableColumn(#"Table uitgevouwen", "Table.Table", {"Name", "Table"}, {"Table.Table.Name", "Table.Table.Table"}),
    #"Table.Table.Table uitgevouwen" = Table.ExpandTableColumn(#"Table.Table uitgevouwen", "Table.Table.Table", {"SessionID", "AccessToken", "LogonResult", "nextAction", "cluster"}, {"Table.Table.Table.SessionID", "Table.Table.Table.AccessToken", "Table.Table.Table.LogonResult", "Table.Table.Table.nextAction", "Table.Table.Table.cluster"}),
    #"Filtered Rows" = Table.SelectRows(#"Table.Table.Table uitgevouwen", each ([Name] = "Header")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Name", "Table.Name", "Table.Table.Name", "Table.Table.Table.AccessToken", "Table.Table.Table.LogonResult", "Table.Table.Table.nextAction", "Table.Table.Table.cluster"})
in
    #"Removed Columns"let
 
Source = Xml.Tables(Web.Contents(""&amp; URL_Logon &amp;""
 
,[Content=Text.ToBinary("
&lt;soap:Envelope xmlns:soap="&amp;Character.FromNumber(34)&amp;"http://schemas.xmlsoap.org/soap/envelope/"&amp;Character.FromNumber(34)&amp;" xmlns:xsi="&amp;Character.FromNumber(34)&amp;"http://www.w3.org/2001/XmlSchema-instance"&amp;Character.FromNumber(34)&amp;" xmlns:xsd="&amp;Character.FromNumber(34)&amp;"http://www.w3.org/2001/XmlSchema"&amp;Character.FromNumber(34)&amp;"&gt;
 
&lt;soap:Body&gt;

&lt;Logon xmlns="&amp;Character.FromNumber(34)&amp;"http://www.twinfield.com/"&amp;Character.FromNumber(34)&amp;"&gt;
&lt;user&gt;"&amp; User &amp;"&lt;/user&gt;
&lt;password&gt;"&amp; Password &amp;"&lt;/password&gt;
&lt;organisation&gt;"&amp; Organisation &amp;"&lt;/organisation&gt;
&lt;/Logon&gt;
&lt;/soap:Body&gt;
&lt;/soap:Envelope&gt;
 
"),Headers=[#"Content-Type"="text/xml;charset=UTF-8"]])),
    #"Table uitgevouwen" = Table.ExpandTableColumn(Source, "Table", {"Name", "Table"}, {"Table.Name", "Table.Table"}),
    #"Table.Table uitgevouwen" = Table.ExpandTableColumn(#"Table uitgevouwen", "Table.Table", {"Name", "Table"}, {"Table.Table.Name", "Table.Table.Table"}),
    #"Table.Table.Table uitgevouwen" = Table.ExpandTableColumn(#"Table.Table uitgevouwen", "Table.Table.Table", {"SessionID", "AccessToken", "LogonResult", "nextAction", "cluster"}, {"Table.Table.Table.SessionID", "Table.Table.Table.AccessToken", "Table.Table.Table.LogonResult", "Table.Table.Table.nextAction", "Table.Table.Table.cluster"}),
    #"Filtered Rows" = Table.SelectRows(#"Table.Table.Table uitgevouwen", each ([Name] = "Header")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Name", "Table.Name", "Table.Table.Name", "Table.Table.Table.AccessToken", "Table.Table.Table.LogonResult", "Table.Table.Table.nextAction", "Table.Table.Table.cluster"})
in
    #"Removed Columns"To get this into another query I transformed it into a list called kweerie and referenced it using Text.From(List.First(Kweerie)). See below.&nbsp;let
 
Source = Xml.Tables(Web.Contents(""&amp; URL_Process &amp;""
 
,[Content=Text.ToBinary("
&lt;soapenv:Envelope xmlns:soapenv="&amp;Character.FromNumber(34)&amp;"http://schemas.xmlsoap.org/soap/envelope/"&amp;Character.FromNumber(34)&amp;" xmlns:twin="&amp;Character.FromNumber(34)&amp;"http://www.twinfield.com/"&amp;Character.FromNumber(34)&amp;"&gt;
&lt;soapenv:Header&gt;
&lt;twin:Header&gt;
&lt;twin:SessionID&gt;"&amp; Text.From(List.First(Kweerie)) &amp;"&lt;/twin:SessionID&gt;
&lt;/twin:Header&gt;
&lt;/soapenv:Header&gt;
&lt;soapenv:Body&gt;
&lt;twin:ProcessXmlString&gt;
&lt;twin:xmlRequest&gt;&lt;![CDATA[
 
&lt;columns code='030_1'&gt;
&lt;column&gt;&lt;field&gt;fin.trs.head.office&lt;/field&gt;&lt;label&gt;Administratie&lt;/label&gt;&lt;visible&gt;true&lt;/visible&gt;&lt;ask&gt;false&lt;/ask&gt;&lt;operator&gt;none&lt;/operator&gt;&lt;from&gt;&lt;/from&gt;&lt;/column&gt;
&lt;column&gt;&lt;field&gt;fin.trs.head.officename&lt;/field&gt;&lt;label&gt;Adm.naam&lt;/label&gt;&lt;visible&gt;true&lt;/visible&gt;&lt;ask&gt;false&lt;/ask&gt;&lt;operator&gt;none&lt;/operator&gt;&lt;/column&gt;
&lt;column&gt;&lt;field&gt;fin.trs.head.year&lt;/field&gt;&lt;label&gt;Jaar&lt;/label&gt;&lt;visible&gt;true&lt;/visible&gt;&lt;ask&gt;true&lt;/ask&gt;&lt;operator&gt;none&lt;/operator&gt;&lt;from&gt;2018&lt;/from&gt;&lt;/column&gt;
&lt;column&gt;&lt;field&gt;fin.trs.head.period&lt;/field&gt;&lt;label&gt;Periode&lt;/label&gt;&lt;visible&gt;true&lt;/visible&gt;&lt;ask&gt;true&lt;/ask&gt;&lt;operator&gt;none&lt;/operator&gt;&lt;/column&gt;
&lt;column&gt;&lt;field&gt;fin.trs.head.yearperiod&lt;/field&gt;&lt;label&gt;Jaar/periode (JJJJ/PP)&lt;/label&gt;&lt;visible&gt;false&lt;/visible&gt;&lt;ask&gt;true&lt;/ask&gt;&lt;operator&gt;between&lt;/operator&gt;&lt;from&gt;2018/03&lt;/from&gt;&lt;to&gt;&lt;/to&gt;&lt;finderparam&gt;&lt;/finderparam&gt;&lt;/column&gt;
&lt;column&gt;&lt;field&gt;fin.trs.head.code&lt;/field&gt;&lt;label&gt;Dagboek&lt;/label&gt;&lt;visible&gt;true&lt;/visible&gt;&lt;ask&gt;false&lt;/ask&gt;&lt;operator&gt;none&lt;/operator&gt;&lt;from&gt;&lt;/from&gt;&lt;to&gt;&lt;/to&gt;&lt;finderparam&gt;&lt;/finderparam&gt;&lt;/column&gt;
&lt;column&gt;&lt;field&gt;fin.trs.head.number&lt;/field&gt;&lt;label&gt;Boekingsnummer&lt;/label&gt;&lt;visible&gt;true&lt;/visible&gt;&lt;ask&gt;false&lt;/ask&gt;&lt;operator&gt;none&lt;/operator&gt;&lt;from&gt;&lt;/from&gt;&lt;to&gt;&lt;/to&gt;&lt;finderparam&gt;&lt;/finderparam&gt;&lt;/column&gt;
&lt;column&gt;&lt;field&gt;fin.trs.head.status&lt;/field&gt;&lt;label&gt;Status&lt;/label&gt;&lt;visible&gt;true&lt;/visible&gt;&lt;ask&gt;true&lt;/ask&gt;&lt;operator&gt;equal&lt;/operator&gt;&lt;from&gt;normal&lt;/from&gt;&lt;to&gt;&lt;/to&gt;&lt;finderparam&gt;&lt;/finderparam&gt;&lt;/column&gt;
&lt;column&gt;&lt;field&gt;fin.trs.head.date&lt;/field&gt;&lt;label&gt;Boekdatum&lt;/label&gt;&lt;visible&gt;true&lt;/visible&gt;&lt;ask&gt;false&lt;/ask&gt;&lt;operator&gt;none&lt;/operator&gt;&lt;from&gt;&lt;/from&gt;&lt;to&gt;&lt;/to&gt;&lt;finderparam&gt;&lt;/finderparam&gt;&lt;/column&gt;
&lt;column&gt;&lt;field&gt;fin.trs.line.dim1&lt;/field&gt;&lt;label&gt;Grootboekrek.&lt;/label&gt;&lt;visible&gt;true&lt;/visible&gt;&lt;ask&gt;true&lt;/ask&gt;&lt;operator&gt;between&lt;/operator&gt;&lt;from&gt;&lt;/from&gt;&lt;to&gt;&lt;/to&gt;&lt;finderparam&gt;&lt;/finderparam&gt;&lt;/column&gt;
&lt;column&gt;&lt;field&gt;fin.trs.line.dim1name&lt;/field&gt;&lt;label&gt;Grootboekrek.naam&lt;/label&gt;&lt;visible&gt;true&lt;/visible&gt;&lt;ask&gt;false&lt;/ask&gt;&lt;operator&gt;none&lt;/operator&gt;&lt;from&gt;&lt;/from&gt;&lt;to&gt;&lt;/to&gt;&lt;finderparam&gt;&lt;/finderparam&gt;&lt;/column&gt;
&lt;column&gt;&lt;field&gt;fin.trs.line.dim1type&lt;/field&gt;&lt;label&gt;Dimensietype 1&lt;/label&gt;&lt;visible&gt;true&lt;/visible&gt;&lt;ask&gt;false&lt;/ask&gt;&lt;operator&gt;none&lt;/operator&gt;&lt;from&gt;&lt;/from&gt;&lt;to&gt;&lt;/to&gt;&lt;finderparam&gt;&lt;/finderparam&gt;&lt;/column&gt;
&lt;column&gt;&lt;field&gt;fin.trs.line.basevaluesigned&lt;/field&gt;&lt;label&gt;Basisbedrag&lt;/label&gt;&lt;visible&gt;true&lt;/visible&gt;&lt;ask&gt;false&lt;/ask&gt;&lt;operator&gt;none&lt;/operator&gt;&lt;from&gt;&lt;/from&gt;&lt;to&gt;&lt;/to&gt;&lt;finderparam&gt;&lt;/finderparam&gt;&lt;/column&gt;
&lt;column&gt;&lt;field&gt;fin.trs.line.debitcredit&lt;/field&gt;&lt;label&gt;D/C&lt;/label&gt;&lt;visible&gt;true&lt;/visible&gt;&lt;ask&gt;false&lt;/ask&gt;&lt;operator&gt;none&lt;/operator&gt;&lt;from&gt;&lt;/from&gt;&lt;to&gt;&lt;/to&gt;&lt;finderparam&gt;&lt;/finderparam&gt;&lt;/column&gt;
&lt;/columns&gt;
 
]]&gt;&lt;/twin:xmlRequest&gt;
&lt;/twin:ProcessXmlString&gt;
&lt;/soapenv:Body&gt;
&lt;/soapenv:Envelope&gt;
"),Headers=[#"Content-Type"="text/xml;charset=UTF-8"]])),
    #"Expanded Table" = Table.ExpandTableColumn(Source, "Table", {"Name", "Table"}, {"Table.Name", "Table.Table"}),
    #"Expanded Table.Table" = Table.ExpandTableColumn(#"Expanded Table", "Table.Table", {"Name", "Table"}, {"Table.Table.Name", "Table.Table.Table"}),
    #"Expanded Table.Table.Table" = Table.ExpandTableColumn(#"Expanded Table.Table", "Table.Table.Table", {"ProcessXmlStringResult"}, {"Table.Table.Table.ProcessXmlStringResult"}),
    #"Parsed XML" = Table.TransformColumns(#"Expanded Table.Table.Table",{{"Table.Table.Table.ProcessXmlStringResult", Xml.Tables}}),
    #"Removed Columns" = Table.RemoveColumns(#"Parsed XML",{"Name", "Table.Name", "Table.Table.Name"}),
    #"Expanded Table.Table.Table.ProcessXmlStringResult" = Table.ExpandTableColumn(#"Removed Columns", "Table.Table.Table.ProcessXmlStringResult", {"th", "tr", "Attribute:result", "Attribute:first", "Attribute:last", "Attribute:total"}, {"th", "tr", "Attribute:result", "Attribute:first", "Attribute:last", "Attribute:total"}),
    #"Expanded tr" = Table.ExpandTableColumn(#"Expanded Table.Table.Table.ProcessXmlStringResult", "tr", {"td", "key"}, {"td", "key"}),
    #"Expanded td" = Table.ExpandTableColumn(#"Expanded tr", "td", {"Element:Text", "Attribute:field", "Attribute:hideforuser", "Attribute:type", "Attribute:name"}, {"Element:Text", "Attribute:field", "Attribute:hideforuser", "Attribute:type", "Attribute:name"}),
    #"Expanded key" = Table.ExpandTableColumn(#"Expanded td", "key", {"office", "code", "number", "line"}, {"office", "code", "number", "line"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded key",{"Attribute:result", "Attribute:first", "Attribute:last", "Attribute:total", "Attribute:hideforuser", "Attribute:type", "Attribute:name", "th"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[#"Attribute:field"]), "Attribute:field", "Element:Text")
in
#"Pivoted Column"This loads into powerBI, but crashes on refresh. Any ideas?

v-frfei-msft · Answer

Hi&nbsp;Anonymous&nbsp;,
&nbsp;
Did you get any error message?
&nbsp;

anonymous · Answer

v-frfei-msft&nbsp;&nbsp;Yes, I get the following error:&nbsp;An error occurred in the ‘Query2’ query. DataSource.Error: Web.Contents failed to get contents from 'https://accounting.twinfield.com/webservices/processxml.asmx?wsd' (500): Internal Server ErrorDetails:DataSourceKind=WebDataSourcePath=https://accounting.twinfield.com/webservices/processxml.asmxUrl=https://accounting.twinfield.com/webservices/processxml.asmx?wsdThis is the same error that is produced when a wrong sessionID is used, which is the case here as well I think. When clicking refresh in PowerBI, it loads the queries but the queries wait for eachother to finish. So the query for getting a sessionID will run,getting a new sessionID, but simultaneously the query for getting the transaction data will run, using the old sessionID. Am I making any sense?

nolock · Answer

Hi&nbsp;Anonymous,I've already seen something similar. The solution was using a function, which gets you your sessionID (instead of having a list/table). This function is then executed with help of&nbsp;Function.InvokeAfter. You set a delay of for example one second and it should work.&nbsp;

anonymous · Answer

This seems to be partly working. This concerns 5 different administrations (companies). Before every query for data extraction I make another query which selects the company. See below.&nbsp;let
 
Source = ()=&gt; Xml.Tables(Web.Contents("" &amp; URL_Session &amp;""

,[Content=Text.ToBinary("
 
&lt;soap:Envelope xmlns:soap="&amp;Character.FromNumber(34)&amp;"http://schemas.xmlsoap.org/soap/envelope/"&amp;Character.FromNumber(34)&amp;" xmlns:xsi="&amp;Character.FromNumber(34)&amp;"http://www.w3.org/2001/XMLSchema-instance"&amp;Character.FromNumber(34)&amp;" xmlns:xsd="&amp;Character.FromNumber(34)&amp;"http://www.w3.org/2001/XMLSchema"&amp;Character.FromNumber(34)&amp;"&gt;
&lt;soap:Header&gt;
&lt;Header xmlns="&amp;Character.FromNumber(34)&amp;"http://www.twinfield.com/"&amp;Character.FromNumber(34)&amp;"&gt;
&lt;SessionID&gt;"&amp; Text.From(List.First(Kweerie)) &amp;"&lt;/SessionID&gt;
&lt;/Header&gt;
&lt;/soap:Header&gt;
&lt;soap:Body&gt;
&lt;SelectCompany xmlns="&amp;Character.FromNumber(34)&amp;"http://www.twinfield.com/"&amp;Character.FromNumber(34)&amp;"&gt;
&lt;company&gt;NL003&lt;/company&gt;
&lt;/SelectCompany&gt;
&lt;/soap:Body&gt;
&lt;/soap:Envelope&gt;

"),Headers=[#"Content-Type"="text/xml;charset=UTF-8"]])),

invokeafter4sec = Function.InvokeAfter(Source, #duration(0,0,0,77)),
    #"Expanded Table" = Table.ExpandTableColumn(invokeafter4sec, "Table", {"Name", "Table"}, {"Table.Name", "Table.Table"})
in
    #"Expanded Table"The code between &lt;selectcompany&gt; changes the selected company, in this case NL003. Every query is timed and preceded by a select query. I can't seem to get this to work for more companies thoughNL001 will have NL001NL002 will have NL002NL003 will have NL002NL004 will have NL002NL005 will have NL002&nbsp;Each query is timed with function.invokeafter to be timed after the other. Any ideas?&nbsp;

Forum Discussion

Help creating session ID

4 Replies

Related content

Error while creating the livy session & Failed to create session for executing notebook

Fabric Failed to create session for executing notebook

Your Compute session is disconnected

Spark Session configuration

Your Fabric compute session disconnected because the notebook session completed or timed out.