Forum Discussion

surfersamu's avatar
surfersamu
Helper II
8 years ago
Solved

Query iteration

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 [email protected] 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

Need Help
  • surfersamu's avatar
    surfersamu
    8 years ago

    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-loop-through-an-array-and-interpolate-the-number?forum=powerquery

     

    Thanks everybody! I hope someone finds this useful!

11 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    8 years ago

    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's avatar
      surfersamu
      Helper II
      8 years ago

      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.

    • surfersamu's avatar
      surfersamu
      Helper II
      8 years ago

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