Help splitting column in PowerBi DirectAccess

[Blue407]

I am manipulating some data from a Log Analytics Workspace, the data is logs from Intune.

One of the columns that is imported includes lots of information seperated by commars, semicolons etc.

I want to find one particular string and get the value.

For example {"ActivityDate":"6/5/2024 10:39:05 AM","ActivityResultStatus":1,"ActivityType":3,"Actor":{ActorType":1,"Application":"5926fc8e-304e-4f59-8bed-58ca97cc39a1","ApplicationName":"Microsoft Intune Portal extension",TargetObjectIds":["f8cfc42f-2209-4018-8b45-e263a4042e24"],.......

I need to create a column with the TargetObjectIDs f8cfc42f-2209-4018-8b45-e263a4042e24 so I can link that to another table to lookup the DeviceName.

Any suggestions?

[MikeAinOz · Accepted solution]

You'll need to parse the JSON data in the column. This can be done in Power Query. Check this out https://learn.microsoft.com/en-us/power-query/parse-json-xml

I haven't done this for a while, but you should be able to add a column with the parsed json and then expand the column that you want.

[lbendlin]

You are supposed to parse the JSON.

[Blue407]

@lbendlin could you please give a bit more information and context as I am new to doing this and learning as I go.

I tried adding a step in the DirectQuery to split the column by delimiter, but it will not let me do this unless the table is in import mode. 

I also tried adding a new column with a SEARCH string, also not allowed in DirectAccess.