Want to get incremental load from API-- but the Data from API is not having any date Column

Hi Anonymous 

Without a date column, it's not possible to perform an incremental refresh in Power BI. Incremental refresh requires a date or timestamp column to filter and identify the data that has been added or updated since the last refresh. Without such a column, Power BI cannot define the range of data to load during each refresh.

Hi Anonymous 

To handle incremental loads from an API without a date column in Power BI, you can use a different approach, such as leveraging an identifier column that increases over time (like an auto-incrementing ID) or using a combination of other columns to determine new records. Here’s a step-by-step guide:

Step-by-Step Guide

1. Identify a Unique Identifier:

   • Ensure your API data has a unique identifier (e.g., ID column) that can help you identify new records.

2. Store the Last Loaded ID:

   • Create a table in Power BI to store the last loaded ID. This can be done using a parameter or a separate table.

3. Modify the API Query:

   • Adjust your API query to fetch only records with IDs greater than the last loaded ID.

Example Implementation

1. Create a Parameter for Last Loaded ID:

   • Go to Home > Manage Parameters > New Parameter.
   • Name it LastLoadedID and set its initial value to 0.

2. Modify the API Query:

   • Edit your API query to include a filter for IDs greater than LastLoadedID.

let
    Source = Json.Document(Web.Contents("https://api.example.com/data?filter=id gt " & Number.ToText(LastLoadedID))),
    Data = Source[Data]
in
    Data
​

• Load Data and Update Last Loaded ID:

MaxID = MAX('YourTable'[ID])
​

• Use this MaxID to update the LastLoadedID parameter for the next load.

• Automate the Process

  1. Power Query:

     • Use Power Query to automate the process of fetching new data based on the last loaded ID.

  2. Scheduled Refresh:

     • Set up a scheduled refresh in Power BI Service to periodically fetch new data.

  Example Power Query Script

let
    LastLoadedID = 0, // Replace with the actual last loaded ID
    Source = Json.Document(Web.Contents("https://api.example.com/data?filter=id gt " & Number.ToText(LastLoadedID))),
    Data = Source[Data],
    MaxID = List.Max(Data[ID])
in
    Data

• Updating the Last Loaded ID

  • After each refresh, update the LastLoadedID parameter with the new MaxID.

  This approach ensures that you only load new records from the API, making the process efficient and scalable.

  
  
  •