Query iteration

[surfersamu]

Hi

I have a table with list of ids and would like to make a get request that needs to be iterated and interpolated into the query.

This is what I have:

This is what I need:

ID   firstname address folder    phones       links     email     lastname
1     Felipe   12 jdsd   2     571-236-4744   link1 fre@gmail.com Thompson
2
3
4
5

 This is the query of the get request (second table).

let
    
    Source = Json.Document(Web.Contents("https://api.icims.com/customers/1112/people/***This is where each id needs to be interpolated***", [Headers=[Accept="Basic aWfdsfdsfdsfdsfdsfdtNTJ4"]])),
    #"Converted to Table" = Record.ToTable(Source),
    #"Filtered Rows" = Table.SelectRows(#"Converted to Table", each true)
in
    #"Filtered Rows"

Thanks in advance

[v-huizhn-msft]

Hi @surfersamu,

Your values used to iterate come from a table? There is a similar thread for your reference.

Best Regards,
Angelia

[surfersamu]

The values used to iterate come from an array. Because the get request needs the profile number to change.

[v-huizhn-msft]

Hi @surfersamu,

I got it. But I am not specific about Power Query, you can post it to Power Query forum, you will get more professional support. 

Best Regards,
Angelia

[surfersamu]

Thank you. It seems like that website is very technical. I will post my question on there.

[v-huizhn-msft]

Hi @surfersamu,

Got it, I hope your problem will be resolved soon. And you can share the workaround if you got it.

Best Regards,
Angelia

[surfersamu]

I've been getting help from TechNet. I'm attaching a link instead of a picture because the picture looks to small when I embedde it on here.

This is what I have so far:

https://imgur.com/a/cxjYQ

You guys are welcome to  help me too. I will post the answer once I reach a solution.

Thanks you!!

[v-huizhn-msft]

Hi @surfersamu,

Welcome to share your workaround, and mark it as answer, so more people will benefit from here.

Best Regards,
Angelia

[surfersamu · Accepted solution]

After a week of work and with the help from the guys from TechNet! My query has succesfully ran!

All I had to do is create a custom function that takes in a value id as a number and interpolates it:

(id as number) =>
let
   Source = Json.Document(Web.Contents("https://api.icims.com/customers/0000/people/"&Text.From(Id)&"", [Headers=[Accept="Basic fakecredentials"]]))
in
   Source

Another query calling that function(The text highligthed with red is the important stuff):

let
    Source = Json.Document(Web.Contents("https://api.icims.com/customers/0000/search/people?searchJson={ ""filters"": [ { ""name"": ""person.firstname"", ""value"": [""""], ""operator"": ""!="" } ] }", [Headers=[Accept="Basic faketoken"]])),
    #"Converted to Table" = Record.ToTable(Source),
    #"Expanded Value" = Table.ExpandListColumn(#"Converted to Table", "Value"),
    #"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"id"}, {"Value.id"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Value1", each true),
    #"Value id1" = #"Filtered Rows"[Value.id],

    ToTable = Table.FromList(#"Value id1", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Renamed = Table.RenameColumns(ToTable,{{"Column1", "ID"}}),
    Added = Table.AddColumn(Renamed, "Custom", each GetRecords([ID]))
in
    Added

This second query has a column with IDs. The red text.. gets the results of the function I created GetRecords(ID) and returns all of the records.

Here's the link to the TechNet thread: https://social.technet.microsoft.com/Forums/en-US/ef3d163d-aced-4a13-8183-5d529cc5a678/how-can-i-loo...

Thanks everybody! I hope someone finds this useful!

[Anonymous]

Hi @surfersamu,

I gat a question. What exactly this line in the code exactly doing?

 #"Filtered Rows" = Table.SelectRows(#"Converted to Table", each true)

A similar line is showing in my Advance query editor, which I have no clue about.

Thanks

[Phil_Seamark]

Hi @surfersamu

Have you tried creating a function in the Query editor?  I think if you put the core logic in the function and call it 4 times you should get something working.

[surfersamu]

Yes that is what I'm trying to do. I just dont know how the to write the code. I know I need to use the function "each" but can't find documentation of it anywhere.