Part 2 - Dynamically Query Ruddr using Custom Function in Power Query

Welcome to our 2nd post about integrating Ruddr with the Power Platform. In the 1st video, we focused on setting up the basics of the integration, creating an API key in Ruddr that would allow us to call the API and begin pulling in data.

One of the nuances that has to be considered, is the fact that the API call can only return up to 100 records at a time. In order to pull back all the data we need, we have to design the dataflow in a way to be able to call the API multiple times dynamically using the data returned to determine the parameters of the next query.

Key Components - Custom Functions & List.Generate

To achieve this functionality, we need to utilize two different capabilities;

Custom Functions provide us the ability to encapsulate the API call into an easy to call expression. Using custom functions in Power Query – Power Query | Microsoft Learn
We can then utilize the List.Generate function to call the custom function. This function is highly flexible (though a little tricky to get the hang of) and can utilize the data being returned by the function to determine how to call the API the next time. List.Generate – PowerQuery M | Microsoft Learn

If you are not used to M code, it may take a little time to feel comfortable with understanding the logic that is needed. I’d highly recommend reading the post that Rick De Groot did over on BI Gorilla to better understand it List.Generate in Power Query: Tutorial with Easy Examples – BI Gorilla.

Guide Along Video

The video will show you step by step how to implement the following code into the dataflow.

Step #1 – Create Custom Function

The following M code is what is created in the video representing how one can query the data from the Ruddr API:

(startDateFilter as text, lastRecord as text) =>let
    url = "https://www.ruddr.io/api/workspace",
    headers = [#"Accept" = "application/json",#"Authorization" = "Bearer TxgR5Rla..."],
    response = Web.Contents(

       url,
       [
            RelativePath="/time-entries?limit=100&dateAfter="&startDateFilter&"&startingAfter="&lastRecord,
            Headers = headers
       ]
   ),
    jsonResponse = Json.Document(response)
in
    jsonResponse

This creates the function that we can then call in a separate query.

Step #2 – Create Blank Query and Use the List.Generate Function

The following M code breaks down the 4 parameters of the List.Generate function. There are two ‘variables’ or properties that are created called Data and lastRecord. To summarize how this will work:

We’ll set the default value of Data to the output of our custom function without providing a lastRecord value to filter by.
Next we’ll set the inital value of the property lastRecord, to the last id that is returned from calling Query(). We do this using another function, List.Last (List.Last – PowerQuery M | Microsoft Learn)
By setting these initial values, the list can then call the Query(…) again by passing in the [lastRecord] value from the prior run. You can think of it running in the following steps, calling Query() and then updating the values of the properties:
1. 1st call: Query(startDateFilter,””)
  1. Data = [1 … 100] , lastRecord = 100
2. 2nd call: Query(startDateFilter, 100)
  1. Data = [1 – 200] , lastRecord = 200
3. 3rd call: Query(startDateFilter, 200)
  1. Data = [1 – 300] , lastRecord = 300

Code Snippet:

let
    startDate = Date.ToText(Date.StartOfMonth(DateTime.Date(DateTime.LocalNow())),“yyyy-MM-dd”),
    dataList = List.Generate(
      // parameter 1 – set the initial values to track
      //   – ‘Data’ property:         Call the Query() with no lastRecord value to get the initial 100 records
      //   – ‘lastRecord’ property:   Call the Query() and use List.Last() to get the last [id] in the [results] list
      () => [Data = try Query(startDate,“”) otherwise null, lastRecord=List.Last(Query(startDate,“”)[results])[id]],
      // parameter 2 – the condition to check on each iteration
      each [Data]<>null,
      // parameter 3 – the criteria to use for the subsequent iteration
      //   – ‘Data’ property:         Call the Query() while passing the [lastRecord] as the second parameter
      //   – ‘lastRecord’ property:   Call the Query() using the [lastRecord] using the same approach to get the last [id] in the [results] list.
      each [Data = try Query(startDate,[lastRecord]) otherwise null, lastRecord=List.Last(Query(startDate,[lastRecord])[results])[id]],
      // parameter 4 – After iterating, only return the [Data] list
      each [Data]
    ),

Test and Begin Clean Up

Once you finish copying the code into the blank query, you should be able to see results begin to get returned. Woohoo!

Now begins the sometimes (usually) arduous task of cleaning up the data so that it is optimized to only what is needed. The video goes further into detail, but you will need to convert the list of records into a table so that you can begin to expand and navigate your way to the data.

Common Challenges

Duplicate records being returned– If you are seeing duplicate records being returned, most likely you did not setup the List.Generate() query correctly. Make sure that you are passing the lastRecord value correctly and also check that you are using the List.Last() function within the Query() calls to grab the last id.

No data being returned – If no data is being returned, you can navigate to the query directly and call it once to test that the function returned data. If it does, then there may be an issue with the way you are calling it in the List.Generate() function. Make sure that you are passing a startDate value / mimic the same parameters you provided directly to ensure it is calling the custom function.

Conclusion & Next Steps

You should now be able to understand how to utilize Custom Functions and the List.Generate() function in Power Query to call APIs dynamically. Would be great to take feedback and suggestions for what else you’d like to see!

Part 2 – Dynamically Query Ruddr using Custom Function in Power Query

Published by Storm Anderson on May 28, 2024May 28, 2024