Forum Discussion
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.
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_completedBETWEENlastWeek()now()&sysparm_fields=sys_id,u_completed")),
#"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?sysparm_query=parts_transfer_orderIN" & 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"
4 Replies
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}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_completedBETWEENlastWeek()now()&sysparm_fields=sys_id,u_completed")),
#"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?sysparm_query=parts_transfer_orderIN" & 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"
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
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=parts_transfer_orderIN
Right=[Table]
