Power BI , Dataflow , for transactional and historical data

[Syndicate_Admin]

Data source: Azure

Below query is based on Events which are registered and cleared after a while. 

Heartbeat
// list records for last 30 days:
| where TimeGenerated > ago(30d)
| summarize LastCall = arg_max(TimeGenerated,*) by Computer
// retrieve machines that have not sent a heartbeat in the last 4 hours:
| where LastCall < ago(4h)
| project Computer, LastCall,
timestamp = format_datetime(LastCall, 'MM-dd-yyyy hh:mm'),
startofday = format_datetime(startofday(now()), 'MM-dd-yyyy')
,VMUUID,SourceComputerId,ComputerIP,ResourceId
| sort by LastCall

The above KustoQL query runs every day, so today it may return 10 Computers (10 events), tomorrow 7 computers (7 events), etc.
Want to keep records of all events (today, tomorrow, etc.) for 31 days.

How to use Power BI or Power BI Dataflow to achieve above ?

[Syndicate_Admin]

Here are some resources that should help you.

https://visualbi.com/blogs/microsoft/powerbi/historical-data-preservation-using-power-bi-dataflow/ 

https://radacad.com/what-are-the-use-cases-of-dataflow-for-you-in-power-bi 

https://radacad.com/all-you-need-to-know-about-the-incremental-refresh-in-power-bi-load-changes-only 

https://docs.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview 

[Syndicate_Admin]

Hi @ktaylor ,

1) If your source retains historical data, then you would apply a filter in your dataflow something like this:

Table.SelectRows(
    previousStepName,
    // each Date.IsInPreviousNDays([Date], 31)
OR  // each [Date] >= Date.AddMonths(Date.From(DateTime.LocalNow()), -1)
)

If your source does not retain historical data, then you can't do it with a dataflow.

2) Incremental refresh has nothing to do with dataflows:

https://docs.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview 

Pete

[Syndicate_Admin]

Hi @ktaylor 

Power BI and Power BI Dataflow are not able to keep the historical queried data for you. They are not databases or data warehouses that are usually used to store historical data.

As you are using Kusto query, you can export data to somewhere, for example a SQL database in Azure. Power BI can access this kind of data source. Then if you want to create a report, you can connect Power BI to this database to get data for the report. 

Reference: 

https://docs.microsoft.com/en-us/azure/data-explorer/kusto/management/data-export/ 

https://docs.microsoft.com/en-us/azure/data-explorer/kusto/management/data-export/export-data-to-sql 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Syndicate_Admin]

Hi, thanks for answering my question on 19 May 2022.

Which methods can I use to export data from Log Analytics or Azure Graph explorer (Kusto query) to Azure SQL database ? 

[Syndicate_Admin]

thanks, will do