Forum Discussion

fheller98's avatar
fheller98
Helper I
4 years ago

For-Loop

Hello all,

We want to pull data from an API using an empty query - unfortunately the API only provides the data per employee, but we want to have a large table with all employee data, so we are currently thinking about how to handle this.

Example:

User ID:
sajnsonsa34sesr
woefefmwe5452
psdmcsado2353

Empty query:
let
Source = Json.Document(Web.Contents("https://api.XXXXXX.com/api/v2/company/[USER-ID]" & ....

Is it possible to build a for-loop to pull out all the data for individual employees based on the user IDs one by one? Or do we have to use python or something like this?

 

---------------------------------------------------------

 

v-easonf-msft do you maybe have an idea?

2 Replies

  • fheller98's avatar
    fheller98
    Helper I
    4 years ago

    Hey amitchandak 

    thanks for your fast answer. I think it is not going to help because there is one query which get divided into smaller queries, if i get it right.

    We need a loop for replacing some part of the url, then trigger again, then replacing etc.
    Do you think this would work too?

    let
    Source = Json.Document(Web.Contents("https://api.XXXXX.com/api/v2/employees/" & employeeId & "/timetracking", [Headers=[Authorization = Authentification]])),
    data = Source[data],
    #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "employeeId", "projectId", "requestDate", "startTime", "endTime", "totalTimeInMinutes", "breakTimeInMinutes", "comment", "status", "categories"}, {"Column1.id", "Column1.employeeId", "Column1.projectId", "Column1.requestDate", "Column1.startTime", "Column1.endTime", "Column1.totalTimeInMinutes", "Column1.breakTimeInMinutes", "Column1.comment", "Column1.status", "Column1.categories"})
    in
    #"Expanded Column1"

    See the example and highlighted part. And we want to get the data for all employees in one query by replacing the employeeID again and again...

    Thanks
    Finn