query to parameter

[robon]

Hey,

I'am new to Power BI, so maybe you can help me.

I've got a query who pulls a sessionID and a few other thing i need. The sessionID is always changing. How can i create a parameter that contains the changing Sessionid?

thanx for your help

Rob

[selimovd]

Hey @robon ,

your description lets some room for interpretation.

Can you give an example what exactly you need and what exactly you want to do?

Then we can help you better. Maybe another approach is more useful.

Best regards

Denis

[robon]

If got a query that give back the sessionID and other info. I want to use the sessionID in another query. This is the table from the first query that shows the session ID query. The "Table.Table.Table.SessionID" and then the first value i want as parameter (or something else i can use in other query's.

Thanx again,

Rob

[v-kelly-msft]

Hi @robon ,

Create a function as below:

let
    Source = () => let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQz0jNW0lHySE1MSS1SitWJVsorzckBijjlp1QqxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Table.SessionID " = _t, Name = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Table.SessionID ", type text}, {"Name", type text}}),
        #"New ID"=Table.FromRecords({Table.SelectColumns(Source,"Table.SessionID "){0}})
    in
       #"New ID"
in
    Source

And you will see:

The parameter gets the first value of column "Table.SessionID" and can be used in another query.

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[robon]

Hi Kelly,

when the session id is changed the other doesn't change. I'am new to this so i probably do something wrong:(

thanks 

Rob

[v-kelly-msft · Accepted solution]

Hi  @robon ,

Have you refreshed data or applied changes?

As tested here,it works fine:

For "parameter",modify it as below:

let
    Source = ()=> let
       
      #"New ID"=Table.FromRecords({Table.SelectColumns(#"Table","Table.SessionID "){0}})
    in
       #"New ID"
in
    Source

And you will see:

For the updated .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[robon]

Hi Kelly,

one more question How can i use it in my script. I now have a static parameter "SessionID":

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.Dummy.com/"&Character.FromNumber(34)&">
<SessionID>"&SessionID&"</SessionID>
</Header>
</soap:Header>
<soap:Body>
<SelectCompany xmlns="&Character.FromNumber(34)&"http://www.Dummy.com/"&Character.FromNumber(34)&">
<company>"& number &"</company>
</SelectCompany>
</soap:Body>
</soap: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", {"SelectCompanyResult"}, {"Table.Table.Table.SelectCompanyResult"})

in

#"Expanded Table.Table.Table"

[robon]

Thanks Kelly, 

I replaced the parameter and now it is working great.

I'am really happy with this Thanks again

Rob

[robon]

Sorry for my late responce. thanks i gonna try this today i keep you updated thanks again!

[amitchandak]

@robon , Do you mean power query parameter?

[robon]

I don't know if i said it the right way. I want to make a parameter from a query result but it has to be just one cell(SessionID) so i can use the parameter in another query.

Thanx for helping me out, sorry if i ask stupid questions:)