Help creating session ID

[Anonymous]

Hello,

I am trying to use PowerBI to extract data from our accountancy software. 

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. 

Spoiler

let
 
Source = Xml.Tables(Web.Contents(""& URL_Logon &""
 
,[Content=Text.ToBinary("
<soap:Envelope xmlns:soap="&Character.FromNumber(34)&"http://schemas.xmlsoap.org/soap/envelope/"&Character.FromNumber(34)&" xmlns:xsi="&Character.FromNumber(34)&"http://www.w3.org/2001/XmlSchema-instance"&Character.FromNumber(34)&" xmlns:xsd="&Character.FromNumber(34)&"http://www.w3.org/2001/XmlSchema"&Character.FromNumber(34)&">
 
<soap:Body>
 
 
<Logon xmlns="&Character.FromNumber(34)&"http://www.twinfield.com/"&Character.FromNumber(34)&">
<user>"& User &"</user>
<password>"& Password &"</password>
<organisation>"& Organisation &"</organisation>
</Logon>
</soap:Body>
</soap:Envelope>
 
"),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.

let
 
Source = Xml.Tables(Web.Contents(""& URL_Process &""
 
,[Content=Text.ToBinary("
<soapenv:Envelope xmlns:soapenv="&Character.FromNumber(34)&"http://schemas.xmlsoap.org/soap/envelope/"&Character.FromNumber(34)&" xmlns:twin="&Character.FromNumber(34)&"http://www.twinfield.com/"&Character.FromNumber(34)&">
<soapenv:Header>
<twin:Header>
<twin:SessionID>"& Text.From(List.First(Kweerie)) &"</twin:SessionID>
</twin:Header>
</soapenv:Header>
<soapenv:Body>
<twin:ProcessXmlString>
<twin:xmlRequest><![CDATA[
 
<columns code='030_1'>
<column><field>fin.trs.head.office</field><label>Administratie</label><visible>true</visible><ask>false</ask><operator>none</operator><from></from></column>
<column><field>fin.trs.head.officename</field><label>Adm.naam</label><visible>true</visible><ask>false</ask><operator>none</operator></column>
<column><field>fin.trs.head.year</field><label>Jaar</label><visible>true</visible><ask>true</ask><operator>none</operator><from>2018</from></column>
<column><field>fin.trs.head.period</field><label>Periode</label><visible>true</visible><ask>true</ask><operator>none</operator></column>
<column><field>fin.trs.head.yearperiod</field><label>Jaar/periode (JJJJ/PP)</label><visible>false</visible><ask>true</ask><operator>between</operator><from>2018/03</from><to></to><finderparam></finderparam></column>
<column><field>fin.trs.head.code</field><label>Dagboek</label><visible>true</visible><ask>false</ask><operator>none</operator><from></from><to></to><finderparam></finderparam></column>
<column><field>fin.trs.head.number</field><label>Boekingsnummer</label><visible>true</visible><ask>false</ask><operator>none</operator><from></from><to></to><finderparam></finderparam></column>
<column><field>fin.trs.head.status</field><label>Status</label><visible>true</visible><ask>true</ask><operator>equal</operator><from>normal</from><to></to><finderparam></finderparam></column>
<column><field>fin.trs.head.date</field><label>Boekdatum</label><visible>true</visible><ask>false</ask><operator>none</operator><from></from><to></to><finderparam></finderparam></column>
<column><field>fin.trs.line.dim1</field><label>Grootboekrek.</label><visible>true</visible><ask>true</ask><operator>between</operator><from></from><to></to><finderparam></finderparam></column>
<column><field>fin.trs.line.dim1name</field><label>Grootboekrek.naam</label><visible>true</visible><ask>false</ask><operator>none</operator><from></from><to></to><finderparam></finderparam></column>
<column><field>fin.trs.line.dim1type</field><label>Dimensietype 1</label><visible>true</visible><ask>false</ask><operator>none</operator><from></from><to></to><finderparam></finderparam></column>
<column><field>fin.trs.line.basevaluesigned</field><label>Basisbedrag</label><visible>true</visible><ask>false</ask><operator>none</operator><from></from><to></to><finderparam></finderparam></column>
<column><field>fin.trs.line.debitcredit</field><label>D/C</label><visible>true</visible><ask>false</ask><operator>none</operator><from></from><to></to><finderparam></finderparam></column>
</columns>
 
]]></twin:xmlRequest>
</twin:ProcessXmlString>
</soapenv:Body>
</soapenv:Envelope>
"),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]

Hi @Anonymous ,

Did you get any error message?

[Anonymous]

@v-frfei-msft 

Yes, I get the following error:

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 Error
Details:
DataSourceKind=Web
DataSourcePath=https://accounting.twinfield.com/webservices/processxml.asmx
Url=https://accounting.twinfield.com/webservices/processxml.asmx?wsd

This 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]

Hi @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 Function.InvokeAfter. You set a delay of for example one second and it should work. 

[Anonymous]

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.

let
 
Source = ()=> Xml.Tables(Web.Contents("" & URL_Session &""
 
 
,[Content=Text.ToBinary("
 
<soap:Envelope xmlns:soap="&Character.FromNumber(34)&"http://schemas.xmlsoap.org/soap/envelope/"&Character.FromNumber(34)&" xmlns:xsi="&Character.FromNumber(34)&"http://www.w3.org/2001/XMLSchema-instance"&Character.FromNumber(34)&" xmlns:xsd="&Character.FromNumber(34)&"http://www.w3.org/2001/XMLSchema"&Character.FromNumber(34)&">
<soap:Header>
<Header xmlns="&Character.FromNumber(34)&"http://www.twinfield.com/"&Character.FromNumber(34)&">
<SessionID>"& Text.From(List.First(Kweerie)) &"</SessionID>
</Header>
</soap:Header>
<soap:Body>
<SelectCompany xmlns="&Character.FromNumber(34)&"http://www.twinfield.com/"&Character.FromNumber(34)&">
<company>NL003</company>
</SelectCompany>
</soap:Body>
</soap:Envelope>
 
 
"),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 <selectcompany> 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 though

NL001 will have NL001
NL002 will have NL002
NL003 will have NL002
NL004 will have NL002
NL005 will have NL002

Each query is timed with function.invokeafter to be timed after the other. Any ideas?