How to extract specific key value pair from a comma separated key values

[Manichin]

I have a usecase where i need to split only workspacename from a key value pairs that i got thrugh power shell scripts. The input source for the power bi file is Excel where we have a column for this keyvaluepair. sample value as follows,
{"Id":"cd23f0f1-9d51ac05","RecordType":10,"CreationTime":"2023-03-30T17:33:04","Operation":"AnalyzedByExternalApplication","OrganizationId":"a29a75-4a46-9395-6fa91e6e7751","UserType":0,"UserKey":"10032001371E1CB1","Workload":"PowerBI","UserId":"Landon.Jordan@deepwater.com","ClientIP":"174.9.9.58","UserAgent":"MSOLAP 16.0 Client","Activity":"AnalyzedByExternalApplication","ItemName":"Power BI Desktopee42fb16-91e2-494c-8bc2-ab6bbef00ab6","WorkSpaceName":"Sample - Dev","DatasetName":"SampleModel","ObjectId":"Power BI Desktopee42fb16-91e2-494c-8bc2-ab6bbef00ab6","DatasetId":"7c457a4f-f-71e9beefb333","IsSuccess":true,"RequestId":"7a4-4239-9ad1-5c18476a1506","ActivityId":"DFFBD35F-576E-4F2F-9F56-5F533CA71411"}.

These key value pairs or not standard. each records has different count of key-value pairs and the order is different. i need to get only workspace name from this.

Anyone please suggest how to get the workspace name.
Thank you

[v-stephen-msft · Accepted solution]

Hi @Manichin ,

You need to split twice.

1.Split the column by custom text: "WorkSpaceName":"

2.Remove [Column1.1]. Split the [Column1.2] by """ and split at left-most delimiter.

3.[Column1.2.1] is the wrokspace name, remove [Column1.2.2] and rename [Column1.2.1].

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.