topic For-Loop in DAX Commands and Tips

For-Loop

fheller98 — Mon, 11 Oct 2021 09:30:53 GMT

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?

Re: For-Loop

amitchandak — Mon, 11 Oct 2021 13:04:01 GMT

fheller98 , refer if this similar blog can help

https://medium.com/@marktiedemann/how-to-do-pagination-in-power-query-430460c17c78

Re: For-Loop

fheller98 — Mon, 11 Oct 2021 14:05:59 GMT

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