Accessing the 'choicelabels' entity via Synapse Link

(0) Share

Report

Posted on by TH-31071512-0

Hi all!

I have ingested my Dataverse environment into a Fabric lakehouse via Synapse Link for Dataverse using Microsoft OneLake. I have noticed that where my entities have choice columns they are coming through only with the numerical value for the choice label rather than the text. I know that this label mapping exists in the metadata entity 'choicelabels' as I have seen this in the past, however I can see also that the Synapse Link ingestion method does not bring through metadata tables. I am also unable to bring 'choicelabels' into the lakehouse via a Gen2 Dataflow in Fabric.

My questions are:

1. Is it at all possible to configure the Synapse Link ingestion to bring through the text of the choice columns as well as their numeric counterpart?

2. If the above is not possible, how can I ingest the 'choicelabels' entity into my Fabric lakehouse so that I can then write a PySpark notebook to complete the mapping for me when I transfer my data from the Synapse Link lakehouse to a silver lakehouse equivalent?

Any help on this is massively appreciated!

Many thanks,
Tom

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (3)

Answers (0)

Suggested answer

venturemavenwill 1,189 Super User 2025 Season 2 on at

Like (0)

Report

There is actually a Microsoft Learn article on how to get the Choices field in Dataverse using Synapse Link. Maybe this answers your question

https://learn.microsoft.com/en-us/power-apps/maker/data-platform/azure-synapse-link-choices-powerbi

Was this reply helpful? Yes No
TH-31071512-0 2 on at

Like (0)

Report

@venturemavenwill - Thanks for your response!

It looks like that method would deal with it table by table rather than being able to pull the choices for all of my entities at once. Is there no way at all to be able to bring in value and text label fields for choice columns using Synapse Link? Or to bring in the entire choice table where I can then create a notebook to handle the mapping?

Thanks!

Was this reply helpful? Yes No
Suggested answer

venturemavenwill 1,189 Super User 2025 Season 2 on at

Like (0)

Report

@TH-31071512-0 Global Choices exist in the /api/data/v9.2/GlobalOptionSetDefinitions Dataverse API path.

So you can theoretically read all the choices into a table, and simply filter out the sets that you like.

The problem with this method is that your dataverse table doesn't come with an unique ID that tells you which option set it is, so you will have to perform the odata query beforehands to try to filter out the data you need before you import the data, and perform transformation for each optionset.

@SteRe found a useful method to narrow down the scope of your query.

1. Get the option sets within a specific solution

https://<org url>.dynamics.com/api/data/v9.2/solutioncomponents?$filter=_solutionid_value eq '<solution id>' and componenttype eq 9 &$select=objectid

=> replace <solution id> with GUID of the solution you would like to check for

=> componenttype eq 9 filters for option sets only

=> $select=objectid reduced the json output to minimum. The objectid is needed next step

2. Query for option set details

https://<org url>.dynamics.com/api/data/v9.2/GlobalOptionSetDefinitions(<objectid>)

=> replace <objectid> by any of the object ids found with first query

Hope this helps.

Was this reply helpful? Yes No