Exporting Global OptionSet Labels and Values from Power Platform using PSDataverse and PowerShell

Microsoft Power Platform provides a rich set of capabilities for creating robust business solutions, among which is the option to use Global OptionSets. These are predefined sets of options that can be used across multiple entities. As developers and analysts, there are often requirements to extract this data for various purposes like data analysis in Datalake, Data Flows, Power BI, or data migrations.

In this article, we'll walk through a PowerShell script that utilizes the PSDataverse module to easily extract all the labels and values from a Global OptionSet.

Prerequisites

You just need to install the PSDataverse module. If you haven't done this already, you can do so using the following command:

Install-Module -Name PSDataverse

The Script

Before diving into the details, here is the full script:

Connect-Dataverse $ConnectionString -InformationAction Continue

Send-DataverseOperation "GlobalOptionSetDefinitions(Name='my_choices')" `
    | Select-Object -ExpandProperty Content `
    | ConvertFrom-Json `
    | Select-Object -ExpandProperty Options
    | Select-Object -ExpandProperty Label -Property Value
    | Select-Object -ExpandProperty LocalizedLabels -Property Value
    | Where-Object { $_.LanguageCode -eq 1033 }
    | Select-Object Value, Label

The Script Explained

1. Connect-Dataverse: Connects to the Power Platform Dataverse.
   • $ConnectionString: Holds the connection string for connecting to the Power Platform environment. For the values suitable for this variable, refer to the GitHub repository of PSDataverse.
   • InformationAction Continue: Allows the script to report to you any messages sent back from AAD during authentication. You will need this parameter if you are going to use "device" authentication flow.
2. Send-DataverseOperation: Requests the definition of the my_choices Global OptionSet.

The subsequent commands focus on data extraction and filtering:

3. Select-Object: Select commands properties of an object, or expands nested properties. The first one, just extracts the Content of the response from Power Platform pipes it to the next command to convert the JSON string inside, into an Object. The subsequent Select commands just drill down to the Label and Value columns that we are interested in.
4. ConvertFrom-Json: Transforms a JSON formatted string to a custom object. This allows us to explore the data in the subsequent commands.
5. Where-Object: Filters objects based on conditions. $_ represent each item in the list what will be filtered.

Here's a breakdown of what happens:

• Extracts the options from the Global OptionSet.
• Expands the Label property and retains the Value property.
• Expands the LocalizedLabels property while retaining the Value.
• Filters labels with a LanguageCode of 1033 (US English). You can change this value if your labels are translated in any other language, and you would like to retrieve those instead.
• Selects the Value and Label properties for the output.

Conclusion

Using PowerShell with PSDataverse allows for efficient extraction of data like labels and values from Global OptionSets. This script is valuable for data migration, analytics, or reporting tasks. Always refer to the official documentation or repository PSDataverse GitHub for updates or enhancements.

Bonus

If you just want to see how your export looks you can add another pipe and show it in a beautiful grid view:

| Out-GridView

You can also export the result into a CSV file using another pipe at the end.

| Export-Csv ".\globaloptionset-my_choices.csv"