Forum Discussion

Sam67548990's avatar
Sam67548990
Regular Visitor
2 months ago
Solved

Fabric virtual table

Can any one help me how to establish a relationship between fabric (source table) and virtual table in data verse, when I used share point i have faced some issues in 1:n relationship. Please help me...
capacities
  • Mahammadaamir's avatar
    Mahammadaamir
    2 months ago

    If you’re using Microsoft Fabric SQL Database or Lakehouse as the source for a Dataverse Virtual Table, you cannot create native Dataverse relationships (1:N, N:1, N:N) in the same way you do with standard Dataverse tables. Virtual tables are read-only metadata representations of external data, and relationship support is limited.

    Option 1: Use Fabric SQL Database with Primary and Foreign Keys (Recommended)

    If your Fabric data is stored in a SQL Database/Warehouse:

    1. Create a Primary Key on the parent table.
    2. Create a Foreign Key in the child table.
    3. Expose both tables through the Dataverse Virtual Table provider.
    4. In Dataverse, create a lookup column that maps to the foreign key.
    5. Refresh the virtual tables.

    This works better than SharePoint because SQL supports relational constraints.

    Option 2: Create Relationships in Fabric First

    Instead of relying on Dataverse:

    • Create SQL views in Fabric that join the parent and child tables.
    • Expose the view as a virtual table.
    • Use the view in your Power Apps or Power BI.

    Common Issues

    If you’re getting errors while creating a 1:N relationship, check that:

    • Both virtual tables use the same data provider.
    • The parent table has a unique primary key.
    • The child table contains the matching foreign key.
    • Data types of the PK and FK are identical.
    • The lookup column is correctly mapped.

    If you’re coming from SharePoint

    SharePoint virtual tables do not fully support relational behavior like SQL/Fabric, which is why 1:N relationships often fail. Fabric SQL or Warehouse is a much better choice for relational scenarios.

Omkar_1712's avatar
Omkar_1712
Solution Specialist
2 months ago

Hello Sam67548990,

Yes, it's possible to create relationships between a Fabric-backed virtual table and Dataverse tables, but there are a few prerequisites.

  • Ensure the Fabric table has a unique column that can be mapped as the Primary Key when creating the virtual table. Without a unique key, Dataverse cannot establish 1:N or N:1 relationships.

  • When creating the virtual table, map both the Primary Key and Primary Name columns correctly.

  • After the virtual table is created, create the relationship from Dataverse → Relationships (typically a Many-to-One lookup from the Dataverse table to the virtual table).

If you experienced issues with SharePoint virtual tables, the same limitation generally applies to Fabric virtual tables - having a stable, unique identifier is essential for relationships to work correctly.

Could you clarify:

  • Is your Fabric source a Lakehouse or a Warehouse?

  • Are you trying to create a 1:N or N:1 relationship?

  • Does your Fabric table already have a unique key column?

Best regards,
Omkar Shinde
Microsoft Fabric Enthusiast | Power BI Consultant

💡 If you found this response helpful, please consider giving it a Kudos.
✅ If this resolves your question, please mark it as the Accepted Solution to help others in the community.

Sam67548990's avatar
Sam67548990
Regular Visitor
2 months ago

Hi Omkar_1712 

 

Thanks for the reply 

 

Using lakehouse here

N to 1 ( subgrid in virtual ) is possible, subgrid in standard is not able to do so, also not able to create a lookup in virtual table from dataverse 

Fabric table has unique key