cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ajsinger
Frequent Visitor

2 API calls, one passes values to second

I am working on a project that requires 2 api calls, One which finds a list of records, which is used to pass through to the second API call to limit the second call. 

 

The first API call, I used the results and set them to a table, which transforms all records into comma delimited (which is needed for the 2nd API call) Example of field in MergedList table is xx,xy,xz

 

Next API call, I want to insert the merged list value into the call

Source = Json.Document(Web.Contents("https://urlforAPI.service-now.com/api/now/table/APITableName?sysparm_query=parts_transfer_orderIN" & MergedList & "&sysparm_display_value=all")),

 

This is not working, AT ALL. 🙂 Please save me. 

1 ACCEPTED SOLUTION

You started me on the correct path, so I thank you. Here was the final solution. 

After I implemented your recommended changes, I was getting a firewall error saying that my query couldnt call the MergedList because it contained another call. So instead of calling two API calls in two Tables, I combined them into one. 

 

let
Source = Json.Document(Web.Contents("https://APIURLservice-now.com/api/now/table/APITABLEequipment_reserve_transfer_order?sysparm_query=u...")),
#"Converted to Table" = Record.ToTable(Source),
#"Expanded Value" = Table.ExpandListColumn(#"Converted to Table", "Value"),
#"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"sys_id"}, {"Value.sys_id"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Value1",{"Name"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Value.sys_id", type text}}),
#"Transposed Table" = Table.Transpose(#"Changed Type"),
Custom1 = Table.ColumnNames(#"Transposed Table"),
#"Converted to Table1" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
Custom2 = Table.AddColumn(#"Converted to Table1","Custom", each Text.End([Column1],1)),
#"Changed Type1" = Table.TransformColumnTypes(Custom2,{{"Custom", type date}}),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Changed Type1", {"Custom"}),
Column1 = #"Removed Errors"[Column1],
Custom3 = Table.RemoveColumns(#"Transposed Table",Column1),
Custom4 = Table.ColumnNames(Custom3),
Custom5 = Table.CombineColumns(Custom3, Custom4, Combiner.CombineTextByDelimiter(","),"Merged"),
#"Changed Type2" = Table.TransformColumnTypes(Custom5,{{"Merged", type text}}),
TransferNumber = Table.ToList(Table.SelectColumns( #"Changed Type2",{"Merged"})){0},
Final = Json.Document(Web.Contents("https://APIURLservice-now.com/api/now/table/APITABLEquipment_reserve_transfer_order_line_item?syspar..." & TransferNumber & "&sysparm_display_value=all")),
#"Converted to Table2" = Record.ToTable(Final),
#"Expanded Value2" = Table.ExpandListColumn(#"Converted to Table2", "Value"),
#"Expanded Value3" = Table.ExpandRecordColumn(#"Expanded Value2", "Value", {"parts_transfer_order", "short_description", "u_asset", "u_asset_condition", "requested_quantity", "sys_mod_count", "description", "received", "sys_updated_on", "sys_domain_path", "received_quantity", "remaining_quantity", "sys_class_name", "number", "sys_id", "u_reason", "sys_updated_by", "shipment_tracking_link", "sys_created_on", "parts", "shipment_tracking_number", "sys_created_by", "status"}, {"Value.parts_transfer_order", "Value.short_description", "Value.u_asset", "Value.u_asset_condition", "Value.requested_quantity", "Value.sys_mod_count", "Value.description", "Value.received", "Value.sys_updated_on", "Value.sys_domain_path", "Value.received_quantity", "Value.remaining_quantity", "Value.sys_class_name", "Value.number", "Value.sys_id", "Value.u_reason", "Value.sys_updated_by", "Value.shipment_tracking_link", "Value.sys_created_on", "Value.parts", "Value.shipment_tracking_number", "Value.sys_created_by", "Value.status"}),
#"Expanded Value.parts_transfer_order" = Table.ExpandRecordColumn(#"Expanded Value3", "Value.parts_transfer_order", {"display_value", "value"}, {"Value.parts_transfer_order.display_value", "Value.parts_transfer_order.value"})
in
#"Expanded Value.parts_transfer_order"

View solution in original post

4 REPLIES 4
v-juanli-msft
Community Support
Community Support

Hi @ajsinger 

I have a Table2, i want to combine rows from Table and the first rows of Table2,

i add a custom column:

[id]&"-"&Table.ToList(Table.SelectColumns(#"Table 2",{"name"})){0}

Capture1.JPGCapture2.JPG

In your case, if MergedList is a query name(table name), change it to:

MergedList1=Table.ToList(Table.SelectColumns(MergedList,{"Merged"})){0}

 

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

You started me on the correct path, so I thank you. Here was the final solution. 

After I implemented your recommended changes, I was getting a firewall error saying that my query couldnt call the MergedList because it contained another call. So instead of calling two API calls in two Tables, I combined them into one. 

 

let
Source = Json.Document(Web.Contents("https://APIURLservice-now.com/api/now/table/APITABLEequipment_reserve_transfer_order?sysparm_query=u...")),
#"Converted to Table" = Record.ToTable(Source),
#"Expanded Value" = Table.ExpandListColumn(#"Converted to Table", "Value"),
#"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"sys_id"}, {"Value.sys_id"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Value1",{"Name"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Value.sys_id", type text}}),
#"Transposed Table" = Table.Transpose(#"Changed Type"),
Custom1 = Table.ColumnNames(#"Transposed Table"),
#"Converted to Table1" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
Custom2 = Table.AddColumn(#"Converted to Table1","Custom", each Text.End([Column1],1)),
#"Changed Type1" = Table.TransformColumnTypes(Custom2,{{"Custom", type date}}),
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Changed Type1", {"Custom"}),
Column1 = #"Removed Errors"[Column1],
Custom3 = Table.RemoveColumns(#"Transposed Table",Column1),
Custom4 = Table.ColumnNames(Custom3),
Custom5 = Table.CombineColumns(Custom3, Custom4, Combiner.CombineTextByDelimiter(","),"Merged"),
#"Changed Type2" = Table.TransformColumnTypes(Custom5,{{"Merged", type text}}),
TransferNumber = Table.ToList(Table.SelectColumns( #"Changed Type2",{"Merged"})){0},
Final = Json.Document(Web.Contents("https://APIURLservice-now.com/api/now/table/APITABLEquipment_reserve_transfer_order_line_item?syspar..." & TransferNumber & "&sysparm_display_value=all")),
#"Converted to Table2" = Record.ToTable(Final),
#"Expanded Value2" = Table.ExpandListColumn(#"Converted to Table2", "Value"),
#"Expanded Value3" = Table.ExpandRecordColumn(#"Expanded Value2", "Value", {"parts_transfer_order", "short_description", "u_asset", "u_asset_condition", "requested_quantity", "sys_mod_count", "description", "received", "sys_updated_on", "sys_domain_path", "received_quantity", "remaining_quantity", "sys_class_name", "number", "sys_id", "u_reason", "sys_updated_by", "shipment_tracking_link", "sys_created_on", "parts", "shipment_tracking_number", "sys_created_by", "status"}, {"Value.parts_transfer_order", "Value.short_description", "Value.u_asset", "Value.u_asset_condition", "Value.requested_quantity", "Value.sys_mod_count", "Value.description", "Value.received", "Value.sys_updated_on", "Value.sys_domain_path", "Value.received_quantity", "Value.remaining_quantity", "Value.sys_class_name", "Value.number", "Value.sys_id", "Value.u_reason", "Value.sys_updated_by", "Value.shipment_tracking_link", "Value.sys_created_on", "Value.parts", "Value.shipment_tracking_number", "Value.sys_created_by", "Value.status"}),
#"Expanded Value.parts_transfer_order" = Table.ExpandRecordColumn(#"Expanded Value3", "Value.parts_transfer_order", {"display_value", "value"}, {"Value.parts_transfer_order.display_value", "Value.parts_transfer_order.value"})
in
#"Expanded Value.parts_transfer_order"

View solution in original post

mahoneypat
Super User IV
Super User IV

This looks correct.  Does the 2nd API call work when you hard code in the list of values?  If the list from the 1st query formatted as text?  Is Privacy set to None?

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


MergedListTable.PNG

 

 

The MergedList table is in Text format, This is my first API call. The Privacy is set to NONE on that call. 

This is the error I receive when I run the code I listed in my original post

Expression.Error: We cannot apply operator & to types Text and Table.
Details:
Operator=&
Left=https://APIURL.service-now.com/api/now/table/APITABLE_reserve_transfer_order_line_item?sysparm_query...
Right=[Table]

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors