none
How can I loop through an array and interpolate the number? RRS feed

  • Question

  • let
        values = {1..4},
    
        Source = Json.Document(Web.Contents("https://api.icims.com/customers/1001/people/(values, Splitter.SplitByNothing())", [Headers=[Accept="Basic dasdasd4544ddsdJ4"]]))
    in
        Source

    That's the code I have at the moment. I'm trying to get records from a RestAPI and loop through each profile to be parsed into a row. I think I need to use the keyword "each" somewhere. But I can't find the documentation of that function anywhere.

    Thanks in advance!


    • Edited by devtech1 Tuesday, September 19, 2017 4:09 PM
    Tuesday, September 19, 2017 1:12 PM

Answers

  • "I made a query that is able to pull ids. So I have a table of IDs only"

    In that case, you could convert the second query into a function, and use the function in a custom column in the ID table. For this example, we'll call the function GetRecords

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

    In the ID table:

    let PreviousStep = ....
    #"Added Custom" = Table.AddColumn(PreviousStep, "Custom", each GetRecords([ID]))

    in #"Added Custom"

    Where ID is the column name in the ID table.

    However, function values are returned in the custom column. Examining the URL in Web.Contents, it's not clear what the Splitter function is supposed to accomplish. Splitter functions return new functions that expect a parameter.

    • Marked as answer by devtech1 Friday, September 22, 2017 2:25 PM
    Tuesday, September 19, 2017 9:53 PM

All replies

  • You can get a list of records that you can subsequently turn into a table, e.g.

    let
       values = {1..4},
       Source = (value as number) => Json.Document(Web.Contents("https://api.icims.com/customers/1001/people/("&Text.From(value)&", Splitter.SplitByNothing())", [Headers=[Accept="Basic dasdasd4544ddsdJ4"]])),
       SourceList = List.Transform(values, each Source(_))
    in
       SourceList


    Tuesday, September 19, 2017 2:14 PM
  • Thank you! That helped out me out a little. When I try that code Power BI runs the function and asks me for a parameter called "value" and interpolates it into the query. So now I know how to interpolate.

    But how can I do that without Power BI asking me for a parameter? I would like to iterate the function 1..4 and interpolate at the same time so I get a list of records.


    Tuesday, September 19, 2017 3:06 PM
  • "But how can I do that without Power BI asking me for a parameter? I would like to iterate the function 1..4 and interpolate at the same time so I get a list of records."

    Without the ability to replicate your actual web contents, I don't know. When I run the code in Power BI, I get a list of errors (failed to retrieve contents), but no request for a parameter.

    Tuesday, September 19, 2017 6:04 PM
  • Yea because the credentials you have are not correct and your ip is not whitelisted to access the API.

    Is it possible to grab the values of one table and then interpolate them into the query?

    I made a query that is able to pull ids. So I have a table of IDs only. Now if I could just plug those ids to the second query :

    let
       values = {column of ids},
       Source = (value as number) => Json.Document(Web.Contents("https://api.icims.com/customers/1001/people/("&Text.From(value)&", Splitter.SplitByNothing())", [Headers=[Accept="Basic dasdasd4544ddsdJ4"]])),
       SourceList = List.Transform(values, each Source(_))
    in
       SourceList


    • Edited by devtech1 Tuesday, September 19, 2017 7:42 PM
    Tuesday, September 19, 2017 7:42 PM
  • "I made a query that is able to pull ids. So I have a table of IDs only"

    In that case, you could convert the second query into a function, and use the function in a custom column in the ID table. For this example, we'll call the function GetRecords

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

    In the ID table:

    let PreviousStep = ....
    #"Added Custom" = Table.AddColumn(PreviousStep, "Custom", each GetRecords([ID]))

    in #"Added Custom"

    Where ID is the column name in the ID table.

    However, function values are returned in the custom column. Examining the URL in Web.Contents, it's not clear what the Splitter function is supposed to accomplish. Splitter functions return new functions that expect a parameter.

    • Marked as answer by devtech1 Friday, September 22, 2017 2:25 PM
    Tuesday, September 19, 2017 9:53 PM
  • I get an error: 

    Expression.Error: The name 'GetRecords' wasn't recognized.  Make sure it's spelled correctly.

    This is the ID table:

    let
        Source = Json.Document(Web.Contents("https://api.icims.com/customers/1212/search/people?searchJson={ ""filters"": [ { ""name"": ""person.firstname"", ""value"": [""""], ""operator"": ""!="" } ] }", [Headers=[Authorization="Basic afgsdgfsgngbstNTJ4"]])),
        #"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],
    
        #"Added Custom" = Table.AddColumn(#"Value id1", "Custom", each GetRecords([ID]))
    in
        #"Added Custom"

    I have another question. On the first line of the first query:

    (Id as number) =>

    How does it find Id?

    And yes you are right. I only need "&Text.From(value)&". I don't need the splitter function. Thank you!



    • Edited by devtech1 Wednesday, September 20, 2017 2:02 PM
    Wednesday, September 20, 2017 1:57 PM
  • "I get an error: 
    Expression.Error: The name 'GetRecords' wasn't recognized.  Make sure it's spelled correctly."

    Did you remember to name the custom function as GetRecords?

    How does it find Id?

    I've used "Id" as the name of the column with the ids. In your case, it appears that the column name is Value.Id. If so, then the expression should be: ... each GetRecords([Value.Id]

    There is no need for the #"Value id1" step (this is a list - Table.AddColumn expects a table as the first parameter).

    The added custom step should read:

    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each GetRecords([Value.Id]))

    Wednesday, September 20, 2017 4:26 PM
  • Error is gone! Thank you.

    But at what point am I going to be able to interpolate the Id row into the get request query?

    That query I sent that query is only for getting the IDs from the API. The next query I would use is:

    let
        
        Source = Json.Document(Web.Contents("https://api.icims.com/customers/1111/people/441", [Headers=[Accept="Basic gfsgdfsgngnsfgdfasggs"]]))
    in
        Source

    where the number 441 is the what I need to interpolate for each ID from the the table ID.

    Thank you I really appreciate your help

    Wednesday, September 20, 2017 6:21 PM
  • "That query I sent that query is only for getting the IDs from the API. The next query I would use is....where the number 441 is the what I need to interpolate for each ID from the the table ID."

    Actually there's an error in the GetRecords custom function. It should be:

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

    I forgot to adjust the Source step after turning the query into a function! Sorry for the confusion.

    Note that if you are not adding more steps to the function, you could simplify it as follows:

    (Id as number) =>
    Json.Document(Web.Contents("https://api.icims.com/customers/1001/people/("&Text.From(Id)&")", [Headers=[Accept="Basic dasdasd4544ddsdJ4"]]))


    Wednesday, September 20, 2017 7:45 PM
  • I'm a little confused on where I have to write the second function. I'm sending you the image of everything I've done so far.

    Since I'm not verified verified I can't upload a picture. I am sending you a link to the image.

    https://imgur.com/a/cxjYQ

    Thank you for your help


    • Edited by devtech1 Thursday, September 21, 2017 4:04 PM
    Thursday, September 21, 2017 4:01 PM
  • It is not clear from the image that Query2 has been renamed GetRecords, as required for the AddedCustom step to work. 

    Also, the image shows that the Id column name is Value.id, so Value.Id does not exist as a column. The GetRecords portion must therefore be GetRecords([Value.id])

    Thursday, September 21, 2017 4:27 PM
  • I have updated everything. Query 2 has been name to GetRecords and updated to "Value.id" For some reason query 2 is still asking me for an input.

    https://imgur.com/a/0orgk

    Thanks!

    Thursday, September 21, 2017 6:30 PM
  • Query2 (GetRecords) is now a custom function. The function is used in the id query. If you try running GetRecords on it's own, it will ask you for input (why are you doing this anyway?). Just run the id query with the GetRecords function in the added custom step.

    Thursday, September 21, 2017 7:17 PM
  • Ohh yeah sorry! I got confused for a second. I went back and reviewed your instructions and now it makes sense! I also found this article which is exactly what you were trying to explain to me:

    https://www.mattmasson.com/2014/11/iterating-over-multiple-pages-of-web-data-using-power-query/

    I'm now running the query1 and I get this error:

    Formula.Firewall: Query 'id' (step 'Added') is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination.

    And this is my query:

    let
        Source = Json.Document(Web.Contents("https://api.icims.com/customers/8908/search/people?searchJson={ ""filters"": [ { ""name"": ""person.firstname"", ""value"": [""""], ""operator"": ""!="" } ] }", [Headers=[Accept="Basic aWNpbXN3ZWJzZTUzODpcUC80TTwhV3VfaFctNTJ4"]])),
        #"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

    I wonder if this error is caused by the API... 

    Thank you soooo much for your help! I wish I could upvote all your answers but I don't have enough points to do it :S




    • Edited by devtech1 Friday, September 22, 2017 1:37 PM
    Friday, September 22, 2017 12:50 PM
  • Try doing a search on: 

    "is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination"

    and let us know if any of the suggestions in the search results help to resolve your issue.

    Friday, September 22, 2017 2:08 PM
  • I found this: http://marketing-wiki.ru/wiki/Power_bi_formula_firewall_privacy_settings

    It resolved my problem! I have now successfully ran this query! Thank you sooo much for your help! I really appreciate it! I couldn't have done it without your help. Thank you for your patience too!


    • Edited by devtech1 Friday, September 22, 2017 6:19 PM
    Friday, September 22, 2017 2:25 PM
  • Glad that I was able to help!
    Friday, September 22, 2017 6:37 PM