Unanswered

Querying Dataverse tables - in particular external tables

(0) Share

Report

Posted on by winston_smyth

I am querying dataverse tables in sql server management studio (SSMS).
External tables dont show up here however.

Is there a way to query external tables via SSMS or another method thats better than the power platform site which is very limited in its data querying abilities.

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (6)

Answers (0)

Mira Ghaly 11,413 Moderator on at

Like (0)

Report

@winston_smyth

What do you mean by power platform site?

Do you mean data flows?

If yes you can do whatever query you like and save it as a view on your SQL database and then load this view in dataverse using data flows and power query.

Please check below link about data flows:

https://learn.microsoft.com/en-us/power-apps/maker/data-platform/create-and-use-dataflows?WT.mc_id=DX-MVP-5004221

Another possible way to load data into dataverse is using a tool like kingswaysoft where there exists a CDS destination component which you can use.

Was this reply helpful? Yes No
winston_smyth 90 on at

Like (1)

Report

On make.powerapps.com, i can see all tables, including virtual/external in this case the table aadusers:

But in SQL Server (SSMS) or PowerBI, I cannot see virtual/External tables. aadusers is not there:

same with PowerBI, aadusers is not there:

Was this reply helpful? Yes No
Mira Ghaly 11,413 Moderator on at

Like (1)

Report

@winston_smyth

I got you try using the SQL4CDS on the XRM toolbox looks like it is working with external tables:

https://www.xrmtoolbox.com/plugins/MarkMpn.SQL4CDS/

Was this reply helpful? Yes No
winston_smyth 90 on at

Like (0)

Report

Hi.

yes, i can see & query that virtual table in SQL 4 CDS, thank you.

If i want to use that virtual table in powerBI, is my only option to use a flow to move the data to an actual dataverse table?

Was this reply helpful? Yes No
Mira Ghaly 11,413 Moderator on at

Like (0)

Report

@winston_smyth

Power BI can connect to a SQL datasource so you can build your power BI dataset based on any source not necessarily dataverse.

Was this reply helpful? Yes No
TheRealPobblebonk 21 on at

Like (0)

Report

Thank you for the hint around external tables. Can we query the AD groups a user is member of?

Was this reply helpful? Yes No