Dataverse: SQL, and option sets
I've never used Dataverse before, as none of my previous companies had a license for it (or, as it was known back then, the Common Data Service).
Now I'm working with a whole heap of clients who have it, and there's definitely some learnings to be had!
My colleague very kindly showed me these two interactions with Dataverse, from Power Query
SQL queries to Dataverse
You can send SQL queries through to Dataverse and get back responses much quicker than querying the data via Power Query, especially if you need to do a lot of joins. The syntax is below:
let
cds = CommonDataService(url)
sql = "SELECT * FROM ..."
response = Value.NativeQuery(cds, sql)
in
response
Options sets
If you need to look up the labels for options sets in Dataverse, you can fetch the definitions through this API call:
https://company.crm6.dynamics.com/api/data/v9.2/GlobalOptionSetDefinitions
The code in Power Query might look like this:
let
Source = Table.FromColumns({Lines.FromBinary(Web.Contents(url), null, null, 1252)})
in
Source
Azure Data Studio
It's also possible to query the Dataverse on Azure Data Studio. Connect to the url, without the https:// part, and login with Microsoft Entra ID.