Is it possible to track when a row was added to a table from an OData Feed and Import connection?

[nowicj01]

Hello there, 

I've been tasked with creating a progress tracker at work and one of the requirements that I have is to be able to track the progress of a table with a list of devices. I get it by connecting to an Intune Data Warehouse OData feed, and I can only connect to it via Power BI/Microsoft, and I don't have direct access to just call the API programmatically which would solve the problem. 

I was wondering if there was a way of automating the process in Power BI? I've tried having a column like so: 

= Table.AddColumn(#"Filtered Rows1", "Date Added", each DateTime.LocalNow())

 and then having a measure like this: 

RowCountChange = 
VAR CurrentRowCount = [DailyRowCount]
VAR PreviousRowCount = 
    CALCULATE(
        [DailyRowCount],
        FILTER(
            'devices',
            devices[Date Added] < MAX(devices[Date Added])
        )
    )
RETURN CurrentRowCount - PreviousRowCount

but that of course doesn't work because the DateAdded always just changes to the date that the feed was refreshed, and I scheduled a daily refresh on Power BI but no luck. 

I was wondering if this is even possible? Having some kind of a way to track when a specific device_id first appeared in the table? 

Or is there a different solution that I'm not even thinking of? Any help much appreciated! 

[nowicj01 · Accepted solution]

I managed to do it and I'll quickly say how in case it's useful to anyone. 

What I ended up doing is creating a Power Automate flow that refreshes the dataset, runs a DAX query on the dataset to count the rows and then creates a row in a SharePoint list with the date it ran on and the number of rows. 

I can then connect to the list via Power BI and voila, everything's where it should be 🙂 

[v-csrikanth]

Hi @nowicj01 
Welcome to the Microsoft Fabric Community.
Yes, you can track when a row (device) was first added from an OData feed in Power BI, even with an Import connection, but it requires data persistence because Power BI refreshes overwrite existing data.
Since Power BI overwrites the dataset upon refresh, you need incremental refresh to persist historical data.

Steps:

1. Enable Incremental Refresh:

2. Create a "Date Added" Column:

   • Modify your query to check for first appearance.This captures the earliest refresh date where each device_id appeared.

   Date Added =
   CALCULATE(
   MIN('devices'[Refresh Date]),
   ALLEXCEPT('devices', 'devices'[device_id])
   )

3. Compare row count changes over time:

   • Create a measure to track new additions per refresh. This shows how many new devices were added in the latest refresh.
     
     New Devices Count =
     COUNTROWS(
     FILTER(
     'devices',
     'devices'[Date Added] = MAX('devices'[Refresh Date])
     )
     )
     
     

If the above information helps you, please give us a Kudos and marked the reply Accept as a Solution.

Thanks,
Cheri Srikanth

[GilbertQ]

Hi @nowicj01 

From my understanding of your question, the only way for this to work is you need to download the results of an API to some kind of CSV file or database so that you can then query the database or files to load the new data as it is added. I'm not sure that this can be done directly in power BI.