Get data Web API Variable URL

[soundwave123]

Hello,

I have been stuck on this for days and googled endless times with no luck on getting this to work. I'm trying to get data but cannot figure out how to pass a users column to another get data web URL.

1. UserID column. Examples of userIDs (22, 104, 122)

2. Connection details. 

3.

4. The last thing I need is to pass the results of column ID as seen in the previous step to a get data web URL like this 

http://********:8080/api/users/104/queued/D20170818T152107-CQVCIGOMQ/metadata

5. This is what step 4 produces.

<Document DocumentTypeName="TEST" FusionVersion="2.0.0.34" IdentityKey="523ss57-5f2f-43a7-asaa0-50e6da896aab" OrganizationID="1"> <Capture BatchName="20170403.165221" BatchPath="\\*************\PSIGEN Batch Storage\20170403.165221" FolderName="20170403.1652598559" FolderPath="\\*************\PSIGEN Batch Storage\20170403.165221\20170403.1652598559" DocumentName="00000005" DocumentPath="\\*************\PSIGEN Batch Storage\20170403.165221\20170403.1652598559\00000005" /> <Pages> <Page FileName="TEST TEST TEST Invoice_1003706760_1.tif" ImageWidth="2356" ImageHeight="2991" TileWidth="746" TileHeight="746" TileLevels="13" /> </Pages> <Records> <Record> <Field Name="Vendor Name" Type="Text"><![CDATA[TEST TEST TEST Invoice]]></Field> <Field Name="Invoice Date" Type="Text"><![CDATA[20170327]]></Field> <Field Name="Date Range" Type="Text"><![CDATA[]]></Field> <Field Name="Invoice Number Sub" Type="Text"><![CDATA[Jacob Test]]></Field> <Field Name="Invoice Number" Type="Text"><![CDATA[1003706760]]></Field> <Field Name="Order #" Type="Text"><![CDATA[]]></Field> <Field Name="Subtotal" Type="Text"><![CDATA[]]></Field> <Field Name="Credit Amount" Type="Text"><![CDATA[]]></Field> <Field Name="Sales Tax" Type="Text"><![CDATA[]]></Field> <Field Name="Invoice Amt" Type="Text"><![CDATA[14542.32]]></Field> <Field Name="Remit To" Type="Text"><![CDATA[]]></Field> <Field Name="Remit To Sub" Type="Text"><![CDATA[TEST TEST INC PO BOX 829122 Santa Barbara CA 21882-7896]]></Field> <Field Name="Order Number" Type="Text"><![CDATA[]]></Field> <Field Name="PO Number" Type="Text"><![CDATA[]]></Field> <Field Name="Reviewer" Type="Text"><![CDATA[]]></Field> <Field Name="User Name" Type="Text"><![CDATA[System Administrator]]></Field> <Field Name="Notes1" Type="Memo"><![CDATA[]]></Field> <Field Name="Split Invoice Amount" Type="Text"><![CDATA[]]></Field> <Field Name="Budget Unit #" Type="Text"><![CDATA[4641]]></Field> <Field Name="Object" Type="Text"><![CDATA[]]></Field> <Field Name="Object New" Type="Text"><![CDATA[]]></Field> <Field Name="Department Object" Type="Text"><![CDATA[]]></Field> <Field Name="Activity" Type="Text"><![CDATA[GATV]]></Field> <Field Name="Function" Type="Text"><![CDATA[GFUN]]></Field> <Field Name="Program" Type="Text"><![CDATA[]]></Field> <Field Name="Phase" Type="Text"><![CDATA[]]></Field> <Field Name="Task" Type="Text"><![CDATA[]]></Field> <Field Name="Customer Number" Type="Text"><![CDATA[]]></Field> <Field Name="Status" Type="Text"><![CDATA[]]></Field> <Field Name="Keywords" Type="Text"><![CDATA[]]></Field> <Field Name="TEST Doc Type" Type="Text"><![CDATA[]]></Field> <Field Name="Queue ID" Type="Text"><![CDATA[104]]></Field> </Record> </Records>

I really appreciate any help you can provide as this has me stumped. 

[v-qiuyu-msft]

Hi @soundwave123,

You can covert the table ID to the list in Query Editor via "Convert to list" option:

Then you can create a parameter and get data from this list, when you get data, please replace part "104" as parameter name.

For more information, you can refer to this similar thread: How to setting a dynamic URL connection. 

Best Regards,

Qiuyun Yu 

[soundwave123]

So, I understand this part but the issue is I have to change the parameter to have the URL pulled for each user manually. I was looking to have a list and PowerBI be able to use the whole list to form multiple URLs based on the list then return all users queue from the list without user involvement so that the data can be gathered all at once.

[v-qiuyu-msft · Accepted solution]

Hi @soundwave123,

Another way you can try is that you can create a calculated column contains those kind of URL. Please open Query Editor, create a custom column [Custom]: 

="http://********:8080/api/users/" & Text.From([UserID]) & "/queued/"  

Then pass new column in Web.Contents() function to get data: 

=Web.Contents([Custom]) 

Best Regards,
Qiuyun Yu

[soundwave123]

I hope my post makes sense if not, please let me know and I'll clarify where needed.