Excel Power Query Referencing Cell

Question

I am struggling to concatenate an API URL to include a function to take a value in a cell that I have named "testCell". This API will pull details from the customer's tracking number. I just want this tracking number to be dynamic and reference a particular cell in Excel. I have used an & ampersand but it clearly is not working. The query works perfectly if I actually input an actual tracking number with the closing " (quotation marks) as well.

The Query is:

let
    Source = Json.Document(Web.Contents("https://api.*****.com/api/track?tracking_number=" **&** Excel.Workbook(File.Contents(GetValue("testCell"))), [Headers=[#"Key1"="*****", #"Key2"=****"]])),
    #"Converted to Table" = Record.ToTable(Source),
    Value = #"Converted to Table"{0}[Value],
    #"Converted to Table1" = Record.ToTable(Value)

in

   #"Converted to Table1"

End of Query

I've included a picture below also showing the error message

Click here for image to the error

I have also created a GetValue function of:

 (rangeName) => 
    Excel.CurrentWorkbook(){[Name=rangeName]}[Content]{0}[Column1]

Can someone very kindly help?

Wedge Wedge · Accepted Answer · 2018-09-28T16:36:01

The error is because you are trying use the string from your test cell to load an Excel file, which it doesn't sound like is what you are trying to do. Remove the "Excel.Workbook(File.Contents())" and it should work fine.

let
    Source = Json.Document(Web.Contents("https://api.*****.com/api/track?tracking_number=" & GetValue("testCell"), [Headers=[#"Key1"="*****", #"Key2"=****"]])),
    #"Converted to Table" = Record.ToTable(Source),
    Value = #"Converted to Table"{0}[Value],
    #"Converted to Table1" = Record.ToTable(Value)

in

   #"Converted to Table1"

Excel Power Query Referencing Cell

1 Answers