1

I have a PowerBI Dashboard which, when refreshed, pulls data from Sentinel, and refreshes the data in "Table". When it does this, it removes the existing data, and imports the snapshot (which is limited to a 90 day rolling window - not controlled by me).

Is it possible to convert this query to either merge the new data with the existing table, or append it and then remove duplicates once appended - so that I don't lose "old" data over 90 days old?

This is my query:

= let Source = Json.Document(Web.Contents("https://api.loganalytics.io/v1/workspaces/xxxxxxxx-xxxx-xxxx-xxxxxxxxxxx/query", 
[Query=[#"query"="SecurityIncident
| where isnotempty(ClassificationComment)
| project Incident, CreatedTime, CommentTime, AlertName, Alerts, Severity, Status, ClosedTime, Classification, ClassificationReason, ClassificationComment
",#"x-ms-app"="OmsAnalyticsPBI",#"prefer"="ai.response-thinning=true"],Timeout=#duration(0,0,4,0)])),
TypeMap = #table(
{ "AnalyticsTypes", "Type" }, 
{ 
{ "string",   Text.Type },
{ "int",      Int32.Type },
{ "long",     Int64.Type },
{ "real",     Double.Type },
{ "timespan", Duration.Type },
{ "datetime", DateTimeZone.Type },
{ "bool",     Logical.Type },
{ "guid",     Text.Type },
{ "dynamic",  Text.Type }
}),
DataTable = Source[tables]{0},
Columns = Table.FromRecords(DataTable[columns]),
ColumnsWithType = Table.Join(Columns, {"type"}, TypeMap , {"AnalyticsTypes"}),
Rows = Table.FromRows(DataTable[rows], Columns[name]), 
Table = Table.TransformColumnTypes(Rows, Table.ToList(ColumnsWithType, (c) => { c{0}, c{3}}))
in
Table

Thanks, Mark

Erik Oppedijk
  • 3,496
  • 4
  • 31
  • 42
Mark Tait
  • 545
  • 3
  • 12
  • 22
  • No, you need a database to store historic data. Power Query will refresh from source each time. There are hacky workarounds but they're just brittle hacks. – Davide Bacci May 04 '23 at 14:46

1 Answers1

0

In Excel, assuming you load you "Table" query's results to a worksheet, import your "Table" query's output from the worksheet back into PowerQuery, and call that new query "Table - Existing".

Duplicate your current "Table" query and rename the copy to "Table - Calculated"

Then, change your original "Table" query to instead merge "Table - Existing" and "Table - Calculated" together however you like!

JSmart523
  • 2,069
  • 1
  • 7
  • 17