Power BI audit log is CSV with embedded JSON. How can Power Query parse the JSON and create columns?

[BobHaze]

From the O365 Amin it is possible to produce and export an audit log of all Power BI activities. The file produced is CSV and one of the columns is filled with JSON data.

Example:

2018-02-09T17:40:44.0000000Z,abcdefg@xyz.com,InstallApp,"{""RecordType"":20,""CreationTime"":""2018-02-09T17:40:44"",""Operation"":""InstallApp"",""UserType"":0,""Workload"":""PowerBI"",""UserId"":""abcdefg@xyz.com"",""UserAgent"":""Mozilla\/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit\/537.36 (KHTML"",""Activity"":""InstallApp""}"

How can I use Power Query to parse the JSON within the CSV and create/populate columns from the JSON?

[v-yuezhe-msft · Accepted solution]

@BobHaze,

Add a blank query and paste the following code  to Advanced Editor of the query, then check if you get expected result.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dY9BT8IwGIb/StMTJmN2ow4dF6cXF0SNqVki5VC2T9JY1qVrhGH471KWlGC0hzZ9+7x5+s3nOCbR9ZDEQ3LDonFKSUppSPr1HohlWcHH6nbb7cJSr4O8bq1QKmuagONvjjl+hVKbinUNuFsak8Cd9waElbpmct3nbvvD5OJj4bkBc2x4+qTy0FsLxpt6UaHNp9Ki8rUXvQFzl5918tPzr4HOsGwFtfXkTO+kUoLzy6uQoEEh60pvWvTEUERCMkGHIKETtE3oBTp8UkEBy6m0jh+Nw1GCBtMHNnv0hqy08kva7p8B9xzjxeIH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByEachDelimiter({""""}, QuoteStyle.None, false), {"Column1.1", "Column1.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Column1.2", Splitter.SplitTextByEachDelimiter({""""}, QuoteStyle.None, true), {"Column1.2.1", "Column1.2.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Column1.2.1", type text}, {"Column1.2.2", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type2","""""","""",Replacer.ReplaceText,{"Column1.2.1"}),
    #"Parsed JSON" = Table.TransformColumns(#"Replaced Value",{{"Column1.2.1", Json.Document}}),
    #"Expanded Column1.2.1" = Table.ExpandRecordColumn(#"Parsed JSON", "Column1.2.1", {"RecordType", "CreationTime", "Operation", "UserType", "Workload", "UserId", "UserAgent", "Activity"}, {"Column1.2.1.RecordType", "Column1.2.1.CreationTime", "Column1.2.1.Operation", "Column1.2.1.UserType", "Column1.2.1.Workload", "Column1.2.1.UserId", "Column1.2.1.UserAgent", "Column1.2.1.Activity"})
in
    #"Expanded Column1.2.1"

Regards,
Lydia

[BobHaze]

Lydia,

Thank you soooo much for this! I was pulled off on a different project & I'm just getting back to this topic now.

How would I alter the query to reference the larger CSV file which I opened with Power BI desktop?

Thanks

[v-yuezhe-msft]

@BobHaze,

Replace the Source Line in the above code with your own.

Regards,
Lydia