Apr 29 2018 09:52 AM
I am trying to convert JSON data that is provided by the Audit log for Office365 for creation of Teams into separate columns so I can use the data more effectively:
The data looks like this:
{
"CreationTime": "2018-04-26T16:31:29",
"Id": "xxxxxxxxxxxxxxx",
"Operation": "GroupAdded",
"OrganizationId": "xxxxxxxxxxxxx",
"RecordType": 4,
"UserKey": "i:xxxxxxxx@live.com",
"UserType": 0,
"Version": 1,
"Workload": "SharePoint",
"ClientIP": "xx.xxx.xxx.xxx",
"ObjectId": "https:xxxxxx.sharepoint.com",
"UserId": "sxxxx.xxx@xxx.com",
"CorrelationId": "xxxxxxxxxxxx",
"EventSource": "SharePoint",
"ItemType": "Site",
"Site": "xxxxxxxxxxxxxx",
"UserAgent": "Mozilla\/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit\/537.36 (KHTML, like Gecko) Chrome\/66.0.3359.117 Safari\/537.36",
"ModifiedProperties": [{
"Name": "Name",
"NewValue": "SharingLinks.1cxxxxxxxxxx-xxxxxxxxxxxxxxxxxxxxxxx"
}]
}
I want the macro to separate the different information into their own columns.
I have tried this solution - https://www.mrexcel.com/forum/excel-questions/980302-parse-json-values-excel-cells.html#post4704611
But I can't get it working.
Any help much appreciated!
Apr 30 2018 02:03 AM
Not sure about macro, as variant you may do that with Power Query. For this particular sample generated script looks like (and attached)
let Source = Json.Document(File.Contents("C:\Test\sample.json")), ToTable = Record.ToTable(Source), TransposeTable = Table.Transpose(ToTable), PromotHeaders = Table.PromoteHeaders(TransposeTable, [PromoteAllScalars=true]), ChangeType = Table.TransformColumnTypes(PromotHeaders,{{"CreationTime", type datetime}, {"Id", type text}, {"Operation", type text}, {"OrganizationId", type text}, {"RecordType", Int64.Type}, {"UserKey", type text}, {"UserType", Int64.Type}, {"Version", Int64.Type}, {"Workload", type text}, {"ClientIP", type text}, {"ObjectId", type text}, {"UserId", type text}, {"CorrelationId", type text}, {"EventSource", type text}, {"ItemType", type text}, {"Site", type text}, {"UserAgent", type text}, {"ModifiedProperties", type any}}), ExpandModifiedProperties = Table.ExpandListColumn(ChangeType, "ModifiedProperties"), ExpandRecord = Table.ExpandRecordColumn(ExpandModifiedProperties, "ModifiedProperties", {"Name", "NewValue"}, {"Name", "NewValue"}) in ExpandRecord
for real data you shall expand lists for each field