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 
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"
Thanks for the link, but I have gone down that path, sorry I should have mentioned it. Basically there is two solutions there which both don't work for me. The adapter (as far as I could tell) was only picking up certain data from the resource but it's not picking up customEvents which is what I'm looking for.
The other solution ( going to AI and getting the Power BI query ), it works great but the whole point of this endeavor is that I want to be able to query historical data since AI only retains it for 90 days (which also is a point why the adapter solution doesn't work out either) so this query would effectively be doing the same thing as me querying it from AI Analytics (unless I'm missing something and this query is saving it to my desktop or something but there is nothing clear stating that).
The last solution that got me the closest was doing the Continuous Export (which just stores it into a Blob Storage therefore retaining it for longer than the 90 day limit) and then telling Power BI to query it from there. What I get back is a table of filenames which pertain to the blob files that CE pushes out to storage similar to this person : https://community.powerbi.com/t5/Desktop/azure-blob/td-p/92058. The big difference is this person's data is CSV filetype while mine is BLOB files (which are really just JSON files). So Power BI is trying to parse the data from these files and doesn't know how to handle them and tries to parse it using a delimiter but obviously with JSON it's more than just one delimiter to parse that. In my case if the files were JSON, I think the Power BI is smart enough to handle them (I seen it somewhere) but like I said they are ".blob" files. So I tried without a delimiter and I get back the contents of the files as per row but only one column being the JSON represented in string.
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
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"
Helpful resources
Join our Fabric User Panel
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Power BI Monthly Update - June 2025
Check out the June 2025 Power BI update to learn about new features.
Fabric Community Update - June 2025
Find out what's new and trending in the Fabric community.
