Creating an iteration using M to combine multiple nested JSON object from several web sources

[Anonymous]

Hi community,

I have what I experience as a very complex issue ...

I have written several custom functions which retrieve JSON data from the web, this works fine.

Also when iterating then (using a table which contains values for the variable in my function) by adding custom columns ...

Now I have the use case where the data I need is nested inside the json object as a list.

The function I created is : 

(id as text) as table =>

let
Source = Json.Document(Web.Contents("https://xxx.xxx.com/api/"& id & ".json?access_token=xxxxxx")),
tasks = Source[tasks],
#"Converted to Table" = Table.FromList(tasks, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "b_id", "s_id", "name", "created_at", "user_id", "type_id", "lane_id"}, {"T.id", "T.b_id", "T.se_id", "T.name", "T.created_at", "T.assigned_user_id", "T.type_id", "T.lane_id"})
in
#"Expanded Column1"

This works if I try it with a single value ... however when trying to iterate as follows ...

let
Source = Table.SelectColumns(IterationTable,"B.id"),
#"Filtered Rows" = Table.SelectRows(Source, each true),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Boards_Loader", each #"Iteration Loader"([B.id]))
in
#"Added Custom"

It will list row values in the new column as error : 

Expression.Error: We cannot convert the value 481765 to type Text.
Details:
Value=481765
Type=[Type]

481765 being the value it retrieved from the column

Any idea anyone ?

Thanks !

[artemus · Accepted solution]

id is a number, but your function takes id as text. The simple answer is to replace #"Iteration Loader"([B.id]) with #"Iteration Loader"(Text.From([B.id]))

[Anonymous]

I found the solution myself ... due to the fact that I was retrieving my iteration ID already from another source, it threw this error.

If I just hardcoded the list in a table first and referenced that, it prompted for a security override, after accepting this , I can now run the query over multiple sources.

Thanks for the help ! I accept your solution

[Anonymous]

Hi,

thanks for getting back to me 

I had set ID as text in the column itself but I tried your solution ... now I get another error

Formula.Firewall: Query 'Invoked Function' (step 'Added Custom') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

Kind regards

[Anonymous]

Oh and as additional info ... the difference with my earlier functions is solely related to the fact that I need to first extract and convert a list from the root object