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

Question

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":"[email protected]","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

anonymous · Accepted Answer

Hi&nbsp;Manichin&nbsp;,
&nbsp;
You need to split twice.
1.Split the column by custom text:&nbsp;"WorkSpaceName":"

&nbsp;
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].

&nbsp;
Best Regards,
Stephen Tao
&nbsp;
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly. &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;