Assistance requiring approach to a project

based on the requirements you you said above :

API Data Retrieval and Incremental Refresh:
If the data fetched via APIs doesn't support query folding, you won't be able to use Power BI's native incremental refresh feature, which relies on query folding.
but you can implement your own incremental refresh logic within your data retrieval process from the API. This involves storing the last refresh timestamp and only fetching new or updated data since the last refresh. You would then append this data to your existing dataset in Power BI.
this requires more manual effort, it's a possible workaround for implementing incremental refresh with non-folding data sources.
Live Streaming Dashboard:
Power BI doesn't offer native support for live streaming dashboards with data updated in real-time. but you can use streaming datasets in Power BI to achieve near real-time updates. Streaming datasets allow you to push data to Power BI using APIs.
Embedding Reports for Client Access:
Power BI Embedded is a suitable option if you want to embed Power BI reports directly into your client's application. This option provides seamless integration with your application and allows you to control user access and permissions.
also, you can use power bi pro or premium per user (PPU) licenses to create reports and dashboards, and then embed them into your client's application using the "Embed report" option. This approach also requires sharing the report with your client and managing user access.
Regarding your query about row limits in incremental refresh:
If the number of rows updated during an incremental refresh exceeds the limit specified in your power bi service plan (i think 150 million rows for Power BI Pro), the refresh will fail.
for this , you may need to optimize your data retrieval process, filter unnecessary data, or consider upgrading to a higher-tier service plan with higher row limits.

If this helped, Follow this blog for more insightful information about data analytics
https://analyticpulse.blogspot.com/
Please Subscribe AnalyticPulse on YouTube for future updates:
https://www.youtube.com/@AnalyticPulse
Please subscribe CogniJourney On Youtube For Daily fun facts:
https://www.youtube.com/@CogniJourney

Hi Anonymous ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

So I tried implementing the manual incremental refresh logic. With some help from ChatGPT I came up with this code,

=let
    // Step 1: Retrieve the previously stored data
    storedData = Table.FromRecords({}),
    // Function to fetch data from the API
    FetchDataFromAPI = () => 
    let
        // Define the URL of the API endpoint
        apiUrl = "https://worldtimeapi.org/api/timezone/Asia/Kolkata",
        
        // Make the HTTP request to fetch data from the API
        apiResponse = Web.Contents(apiUrl),
        
        // Convert the JSON response to a table
        apiDa = Json.Document(apiResponse),
        
         apiData = 
            if Value.Is(apiDa, type list) then
                apiDa // Return the list of records as is
            else
                {apiDa} // Wrap the single record in a list
    in
        apiData,
    // Step 2: Fetch data from the API
    apiDa = FetchDataFromAPI(),
    apiData=Table.FromRecords(apiDa),
    finalData=
        if Table.RowCount(storedData)=0 then
            apiData
        else
            let
                    // Step 3: Identify updates
                    updatedData = Table.Join(storedData, {"unixtime"}, apiData, {"unixtime"}, JoinKind.LeftOuter),
                    updatedDataFiltered = Table.SelectRows(updatedData, each [unixtime] <> null),

                    // Step 4: Merge updates with the stored data
                    mergedData = Table.Combine({Table.RemoveColumns(storedData, {"unixtime"}), updatedDataFiltered}),

                    // Step 5: Append new rows
                    newRows = Table.SelectRows(apiData, each not List.Contains(Table.Column(mergedData, "unixtime"), [unixtime])),
                    finalMergedData = Table.Combine({mergedData, newRows})
                in
                    finalMergedData
in
    finalData

The API is a publicly available API that gives the current time and date. An example is

{
    "abbreviation": "IST",
    "client_ip": "122.176.65.95",
    "datetime": "2024-04-01T15:03:59.624664+05:30",
    "day_of_week": 1,
    "day_of_year": 92,
    "dst": false,
    "dst_from": null,
    "dst_offset": 0,
    "dst_until": null,
    "raw_offset": 19800,
    "timezone": "Asia/Kolkata",
    "unixtime": 1711964039,
    "utc_datetime": "2024-04-01T09:33:59.624664+00:00",
    "utc_offset": "+05:30",
    "week_number": 14
}

My logic behind this code is that whenever I click on the "Refresh Preview" button in the Power Query editor, a new response would come, which would check with the "unixtime" field. Now this field would change with every call, so, every new record will be unique. So, according to my code logic, after every refresh, a new row would be added to the previous table. But that did not happen, instead the same row kept on updating. I don't understand where I am going wrong here. In another post Inserting rows recursively does not yield correct results , I found the point of #"Burndown Query", but didn't really understand it. Any help regarding the code would be much appreciated.

Thanks!