Need to consolidate some data

[PowerBI-ELM]

I have a very large data table in the format ( IDFIELDINT, timestamp, timestamp_UTC, pointID, value). Data ponts come in every second or so.  Hundreds of millions of records.

I want to write a report for a few of the PointIDs, but only include the first record from any 1 minute period or the average of all the records in the 1 minute period.

I have tried various things and while they may work they are very very slow.

I suppose I could create a back end pre processed table to draw from, but then I would have to keep it up to date.

Does anyone have any ideas as how I can quickly load the data so I get what I want?  I would like to use import along with incremental refresh (real time loading for new data).  Maybe when it gets loaded the refreshing will not be so bad.  But the initial load and periodic refreshes seems to take too long and drives up the host servers CPU utilization for an extended period.

Thanks

[v-xiaoyan-msft]

Hi @PowerBI-ELM ,

I think your idea is relatively convenient, but if your data source supports direct query, it will save you the trouble of loading too slowly.
Unlike the import mode, the DQ mode does not load the data locally, but queries it directly at the data source.
If your data can be placed in a database that supports query folding, then DQ + incremental refresh is definitely the most time efficient way.

Hope it helps,

Best Regards,
Community Support Team _ Caitlyn

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