Json API with header details

[suebayes]

I have a function that I’ve created that works with a JSON api with a query

let Source = (RelativePath) =>

// function to add as a custom column to pull in relative path from table using the name of the api call
let
Source = Json.Document(Web.Contents(
“https:/********/”,
// url remains static
[
RelativePath = RelativePath,
Query =
[
token=Token // token is parameterised
]
]
)),
#“Converted to Table” = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#“Converted to Table”

in Source

Which works and I can add as a custom column to the table that holds the relative path within a column.

I’d like to try to do the same but using a url with a Headers field but am struggling.

The url is structured like this:

= Json.Document(Web.Contents(“https:///RelativePathstoredincolumn", [Headers=[#“X-SiteToken”="****”]]))

and I want to use a Headers parameter instead of query and token as above. I think it’s because it’s a list within a list and has special characters in it. Any suggestions?

//[Headers=[#"“X-SiteToken”"=""***********""]]))"

[suebayes]

Hi Imke,

Thank you for replying, really appreciate it.

Power BI and Json

I have a table that gives the name of the API call and two methods of calling the API. 

Both work when I use them within a blank query but I want to create a function using create a custom function column that will use the value in the API Call to fill in the APICallName and run the function.

The query for API URL which works is

This allows me to add this into a column as a function, add an index, and expand using index number

Then I can use another function to Extract the Data by entering the index number

The customer would prefer to use the url using the Headers option in the second column but I’m struggling to get the parameterised code working.

This is my latest attempt but I think there must be something to do with the structure of the headers and it being a nested list with special objects in it.  When I add this code into the column I get an error message that I argument was passed to a function which expects 2.

Have been trying all sorts of options, any thoughts would be gratefully appreciated.

Hopefully it makes a bit more sense,

Sue

[mahoneypat]

It looks like you are putting the relative path into the body of the web call in the last image, instead of putting it in the relative path option of the Web.Contents function.  Did you try concatenating the relative path into the url?

url = "https://*****/" & RelativePath

Pat

[suebayes · Accepted solution]

Hi Pat, I got it to work.  I'd tried putting it in as a parameter before but had neglected to change the parameter to a list type! So now when I put it in as a custom function within the column, I could then specify which column to use as the input and therefore which row.  A bit of an epic journey.

@ImkeF  I got it to work and learnt a lot 🙂

[ImkeF]

Hi @suebayes ,

not sure I can follow.
But I would recommend to test out the desired URL in the browser or a tool like Postman to check general correctness.
Then we could try to "translate" it to Power Query and troubleshoot if necessary.