Forum Discussion
Help splitting column in PowerBi DirectAccess
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?
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.
3 Replies
You are supposed to parse the JSON.
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.
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.
