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

[Singh_10]

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

[suparnababu8]

Hi @Singh_10 

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.

  
  
  •  

[Ritaf1983]

Hi @Singh_10 

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.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[Singh_10]

Thanks for the Reply,

actually i am taking data from Azure Analytics -- where if i take summarize data via API it doesn't give Any date column