Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Help creating session ID

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 a...
Need Help
Anonymous's avatar
Anonymous
Not applicable
7 years ago

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's avatar
Nolock
Resident Rockstar
7 years ago

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's avatar
    Anonymous
    Not applicable
    7 years ago

    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?