Forum Discussion
Azure Application Insights Continuous Export to Power BI
I took another crack at it and I think I figured it out. It first required tweaking some transform queries that PowerBI tries to do to the blob binary data which strips out some important characters in the json serialized strings. Then after that, I had to parse the strings as JSON and expanding the data from there.
I'm surprised considering Continuous Export from AAI is a Microsoft product/feature along with PowerBI, there isn't already some blog post about making this integration work seamlessly (or is this reserved for the BI Pros?) so if anybody needs it, here is the advanced query where you just replace the "<blob_storage_container_url>" with your own url path to your blob container with the blobs stored from AAI. From there it's just expanding the custom.dimensions column to grab all the properties you track and these are unique to whatever your custom event data contains.
let Source = AzureStorage.Blobs("<blob_storage_container_url>"), #"Invoke Custom Function1" = Table.AddColumn(Source, "JsonTransform", each Lines.FromBinary([Content],null,null,1252)), #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}), #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "JsonTransform"}), #"ExpandedJsonTransform" = Table.ExpandListColumn(#"Removed Other Columns1", "JsonTransform"), #"Parsed JSON" = Table.TransformColumns(#"ExpandedJsonTransform",{{"JsonTransform", Json.Document}}), #"Expanded Transform File from Query1" = Table.ExpandRecordColumn(#"Parsed JSON", "JsonTransform", {"event", "internal", "context"}, {"event", "internal", "context"}), #"Expanded event" = Table.ExpandListColumn(#"Expanded Transform File from Query1", "event"), #"Expanded event1" = Table.ExpandRecordColumn(#"Expanded event", "event", {"name", "count"}, {"event.name", "event.count"}), #"Expanded context" = Table.ExpandRecordColumn(#"Expanded event1", "context", {"application", "data", "device", "user", "session", "operation", "location", "custom"}, {"application", "data", "device", "user", "session", "operation", "location", "custom"}), #"Expanded custom" = Table.ExpandRecordColumn(#"Expanded context", "custom", {"dimensions"}, {"custom.dimensions"}) in #"Expanded custom"
Hi ochavez,
As this issue requires sufficient Azure experience, I would suggest you create a support ticket to get dedicated help from Microsoft engineer.
Best Regards,
Qiuyun Yu
I took another crack at it and I think I figured it out. It first required tweaking some transform queries that PowerBI tries to do to the blob binary data which strips out some important characters in the json serialized strings. Then after that, I had to parse the strings as JSON and expanding the data from there.
I'm surprised considering Continuous Export from AAI is a Microsoft product/feature along with PowerBI, there isn't already some blog post about making this integration work seamlessly (or is this reserved for the BI Pros?) so if anybody needs it, here is the advanced query where you just replace the "<blob_storage_container_url>" with your own url path to your blob container with the blobs stored from AAI. From there it's just expanding the custom.dimensions column to grab all the properties you track and these are unique to whatever your custom event data contains.
let
Source = AzureStorage.Blobs("<blob_storage_container_url>"),
#"Invoke Custom Function1" = Table.AddColumn(Source, "JsonTransform", each Lines.FromBinary([Content],null,null,1252)),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "JsonTransform"}),
#"ExpandedJsonTransform" = Table.ExpandListColumn(#"Removed Other Columns1", "JsonTransform"),
#"Parsed JSON" = Table.TransformColumns(#"ExpandedJsonTransform",{{"JsonTransform", Json.Document}}),
#"Expanded Transform File from Query1" = Table.ExpandRecordColumn(#"Parsed JSON", "JsonTransform", {"event", "internal", "context"}, {"event", "internal", "context"}),
#"Expanded event" = Table.ExpandListColumn(#"Expanded Transform File from Query1", "event"),
#"Expanded event1" = Table.ExpandRecordColumn(#"Expanded event", "event", {"name", "count"}, {"event.name", "event.count"}),
#"Expanded context" = Table.ExpandRecordColumn(#"Expanded event1", "context", {"application", "data", "device", "user", "session", "operation", "location", "custom"}, {"application", "data", "device", "user", "session", "operation", "location", "custom"}),
#"Expanded custom" = Table.ExpandRecordColumn(#"Expanded context", "custom", {"dimensions"}, {"custom.dimensions"})
in
#"Expanded custom"
Hi,
I am experiencing the exact same problem after importing Azure Storage Blob data (that is continuously exported from Application Insights) into powerbi. I followed your recommendation by replacing my storage account details with the above query you listed in Advanced Editor this query didn't work.
When i tried to combine all blob files and transform based on comm
let
Source = AzureStorage.Blobs("XXX"),
XXX1 = Source{[Name="XXXX"]}[Data],
#"Removed Other Columns" = Table.SelectColumns(XXX1,{"Content"}),
#"Invoke Custom Function1" = Table.AddColumn(#"Removed Other Columns", "Transform File from XXX (3)", each #"Transform File from XXX (3)"([Content])),
#"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", {"Transform File from XXX (3)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from XXX (3)", Table.ColumnNames(#"Transform File from XXX (3)"(#"Sample File (3)"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}, {"Column22", type text}, {"Column23", type text}, {"Column24", type text}, {"Column25", type text}, {"Column26", type text}, {"Column27", type text}, {"Column28", type text}, {"Column29", type text}, {"Column30", type text}, {"Column31", type text}, {"Column32", type text}, {"Column33", type text}, {"Column34", type text}, {"Column35", type text}, {"Column36", type text}, {"Column37", type text}, {"Column38", type text}, {"Column39", type text}, {"Column40", type text}, {"Column41", type text}, {"Column42", type text}, {"Column43", type text}, {"Column44", type text}, {"Column45", type text}, {"Column46", type text}, {"Column47", type text}, {"Column48", type text}, {"Column49", type text}, {"Column50", type text}, {"Column51", type text}, {"Column52", type text}, {"Column53", type text}, {"Column54", type text}, {"Column55", type text}, {"Column56", type text}, {"Column57", type text}, {"Column58", type text}, {"Column59", type text}, {"Column60", type text}, {"Column61", type text}, {"Column62", type text}, {"Column63", type text}, {"Column64", type text}, {"Column65", type text}, {"Column66", type text}, {"Column67", type text}, {"Column68", type text}, {"Column69", type text}})
in
#"Changed Type"Please help!
Hi
I am experiencing the same problem as listed above when i try to import Application Insights data that is continuously exported to Azure Storage Account in blobs.
When i import blob data into PowerBI, and used your query that is referred below, invoke Custom Function step is failing.
Below is my query
let
Source = AzureStorage.Blobs("XXX"),
XXX1= Source{[Name="XXX"]}[Data],
#"Invoke Custom Function1" = Table.AddColumn(Source, "JsonTransform", each Lines.FromBinary([Content],null,null,1252)),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "JsonTransform"}),
#"ExpandedJsonTransform" = Table.ExpandListColumn(#"Removed Other Columns1", "JsonTransform"),
#"Parsed JSON" = Table.TransformColumns(#"ExpandedJsonTransform",{{"JsonTransform", Json.Document}}),
#"Expanded Transform File from Query1" = Table.ExpandRecordColumn(#"Parsed JSON", "JsonTransform", {"event", "internal", "context"}, {"event", "internal", "context"}),
#"Expanded event" = Table.ExpandListColumn(#"Expanded Transform File from Query1", "event"),
#"Expanded event1" = Table.ExpandRecordColumn(#"Expanded event", "event", {"name", "count"}, {"event.name", "event.count"}),
#"Expanded context" = Table.ExpandRecordColumn(#"Expanded event1", "context", {"application", "data", "device", "user", "session", "operation", "location", "custom"}, {"application", "data", "device", "user", "session", "operation", "location", "custom"}),
#"Expanded custom" = Table.ExpandRecordColumn(#"Expanded context", "custom", {"dimensions"}, {"custom.dimensions"})
in
#"Expanded custom"Please help!
Best Regards,
sherin
