Converting type table to type text for dynamic URL

[Datadork123]

Hello,

I have been struggling to get a query to work that uses columns from an excel sheet stored on a sharepoint drive and inputing the URL or tracking number (ive tried both) into a Webcall.  I have tried several versions and have had success running them locally but none of them work on the service due to the formula firewall issue.  I am guessing its because I have still not manage to sufficiently "flatten" the query.  I am confident that it is not due to mixed privacy settings unless I am missing something.  I am trying to get a dual source query to work like in the first video but I am running into trouble entering the tracking number into the Query Parameter. The current error I am trying to resolve is the "cannot convert a value of type table to type text" on the Source line highlighted in red below.  The orange text are redactions I did for privacy.   Any help on this or an alternate method for this type of dynamic URL lookup (that will run on the service) would be greatly appreciated.

Thanks

Methods I have tried trying to get this to work on the service.

https://www.youtube.com/watch?v=_MtuqO9Cj1E

https://excelguru.ca/power-query-errors-please-rebuild-this-data-combination/

https://www.youtube.com/watch?v=VkDpwSGWPmk

Query

let
SourceX = SharePoint.Files("<Sharepoint Site>", [ApiVersion = 15]),

#"Filtered Rows" = Table.SelectRows(SourceX, each ([Name] = "<File Name>")),
#"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Ship Date", type date}, {"Customer", type text}, {"City", type text}, {"State", type text}, {"Zip", Int64.Type}, {"Reference Number", Int64.Type}, {"Department Code", type any}, {"Tracking Number", Int64.Type}, {"MISC DELETE", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Department Code", "MISC DELETE"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Tracking Number", type text}, {"Zip", type text}, {"Reference Number", type text}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type1",{"Source.Name"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns1", "Package Tracking URL", each "https://www.bing.com/packagetrackingv2?packNum=" & [Tracking Number] & "&carrier=<Carrier>"),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"Package Tracking URL", type text}}),
TrackingNumber = Table.TransformColumns(
#"Changed Type2",
{"Tracking Number", each try Text.From(_) otherwise false, Text.Type}),

Source = Web.Page(Web.Contents("https://www.bing.com/packagetrackingv2?packNum=<TrackingNumber>&carrier=<Carrier>",
[Query= [packNum=TrackingNumber]])),

#"Added Custom1" = Table.AddColumn(#"Changed Type2", "WebCall", each Web.Contents("https://www.bing.com/packagetrackingv2?packNum=<TrackingNumber>&carrier=<Carrier>",
[Query= [packNum=Source]]))
in
#"Added Custom1"

[lbendlin · Accepted solution]

"TrackingNumber"  refers to a table. What you want is "Tracking Number" which refers to a scalar value (a column in the current row) - if you specify the "each"  iterator which you didn't do.