Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I
Helper I

Dataverse Synapse Link Choice Column Names



I've integrated dataverse into fabric using Synapse link to automatically bring in all the tables into Fabric. However, the choice columns in the dataverse only return the integer ID of the choice. Is there anyway to bring in the name? If I query using powerquery, the names also return. 


I wouldn't want to match all the codes up to the various choice tables as it would be a huge amount of work, but the choice tables dont come over with the tables either anyway, so currently there is no way to make this data usable. Hoping there is some switch I can turn on to bring this over?

Microsoft Employee
Microsoft Employee

SELECT  [Base].accountid customerid
      , 1 AS [CustomerEntityType]
      , [Base].name [Customer Name]
      , [Base].parentaccountidname [Parent Account]
      , [Base].address1_city [City]
      , [Base].address1_stateorprovince [State or Province]
      , [Base].address1_country [Country]
      , [Base].ownerid ownerid
      , [Base].owneridname [Customer Owner]
      , ISNULL(account_industrycode.value,'N/A') [Industry]
      , [T].name AS [Territory]
      , [T].[parentterritoryidname] [Parent Territory]
      , ISNULL([T].[manageridname], 'No Territory Manager') [Territory Manager]
      , [T].territoryid
      , [Base].accountnumber AS [Customer ID]
      , account_businesstypecode.value [Business Type]
    FROM [account]              AS Base
    LEFT OUTER JOIN [territory] AS T
        ON  T.territoryid = Base.territoryid
    LEFT JOIN [stringmap] AS account_industrycode
        ON  account_industrycode.langid = 1033
        AND account_industrycode.objecttypecode = 'account'
        AND account_industrycode.attributename = 'industrycode'
        AND account_industrycode.attributevalue = [Base].industrycode
    LEFT JOIN [stringmap] AS account_businesstypecode
        ON  account_businesstypecode.langid = 1033
        AND account_businesstypecode.objecttypecode = 'account'
        AND account_businesstypecode.attributename = 'businesstypecode'
        AND account_businesstypecode.attributevalue = [Base].businesstypecode
        [Base].IsDelete IS NULL

This is the pattern I use - I only join on the values that I need at runtime since those labels can change independently of the record or I might want a different language represented in the report.

Thanks - whats frustrating about this though, is I can pull all the tables in powerquery with their string straight into fabric. If I use the synapse link, I need to write SQL queries for each table and column (we have 100s of tables), so its going to be a huge amount of work vs using a dataflow.

There is an approach to script the creation of views using this Script to Quickly Create Dataverse Views in Azure Synapse - with the Metadata Labels! | LinkedIn - 
The fabric shortcuts will be a significantly more performant and near-realtime than anything created with dataflows. - but if your actual datasize is small enough that you can manage it with dataflows, use whatever works for you.

Hi @joeparkinson 

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others.
Otherwise, will respond back with the more details and we will try to help.


Hi @joeparkinson 


We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others.
If you have any question relating to the current thread, please do let us know and we will try out best to help you.
In case if you have any other question on a different issue, we request you to open a new thread.


Hi @joeparkinson 


Apologies for the inconvenience that you are facing here.

I would request you to open up a support ticket to get support from our engineering team for your query.

Please go ahead and raise a support ticket to reach our support team: support-ticket 

Please provide the ticket number here as we can keep an eye on it.

Also - I wrote up an example for getting the label values for multi-select fields.
Unpacking a Dataverse Multi-Select field in Fabric. | LinkedIn

New Member

I wrote a table-valued function that translates an integer option set lookup value into its display value.  I am curious if there is an easier way to get at this without joining to the OptionSet table for each option.  

Ah, i missed the optionset table! At least its possible, but also quite frustrating given the 1000s of columns we'd need to match back. Out of curiousity, what was the function you wrote?

Community Support
Community Support

Hi @joeparkinson 


Thanks for using Microsoft Fabric Community.

At this time, we are reaching out to the internal team to get some help on this.
We will update you once we hear back from them.

Appreciate your patience.



Thank you, appreciate the support on this

Helpful resources


Fabric Monthly Update - June 2024

Check out the June 2024 Fabric update to learn about new features.


Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.