Forum Discussion
2 API calls, one passes values to second
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"
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"