topic Re: Can we query Data model in Power BI with complex SQL instead of drag and drop table columns in Developer

Can we query Data model in Power BI with complex SQL instead of drag and drop table columns

nibitbabu — Thu, 07 Dec 2023 20:49:39 GMT

Can we query or build a report from Power BI Data model ( already loaded using Get data ) with SQL statements already.
It seems like sql can be used only to load data into the Power BI model using DirectQuery--> Advanced options.

Re: Can we query Data model in Power BI with complex SQL instead of drag and drop table columns

tackytechtom — Thu, 07 Dec 2023 21:50:24 GMT

Hi @nibitbabu ,

Not sure, whether I fully got your question, but I'll give it a try 🙂

I do not think you can use SQL to directly query the semantic model, but there are ways to shoot queries i.e. by using the ExecuteQueries API (https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/execute-queries) or by using the new semantic link (What is semantic link (preview)? - Microsoft Fabric | Microsoft Learn). With the latter, I guess you could use some kind of sql dialect on top of dataframes.

Hope this helps at least a bit 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

Re: Can we query Data model in Power BI with complex SQL instead of drag and drop table columns

nibitbabu — Thu, 07 Dec 2023 22:07:55 GMT

Thanks @tachytechtom, I believe you got my question exactly i.e. how to use sql queries against semantic model in Power BI to generate reports and there does not seem to be a stright forward solution.

Is ther any way to atleast import mutiple data Queries(Power BI) using one connection. i.e. after connecting to the database once. I want to import all the complex SQL queries/reports at once without doing a connection to the source for individual queries.

Re: Can we query Data model in Power BI with complex SQL instead of drag and drop table columns

Anonymous — Fri, 29 Dec 2023 06:54:27 GMT

Hi @nibitbabu ,

Base on your description, it seems that you want to load multiple queries into Power BI base on one SQL statement. However, currently Power BI doesn't support this feature. You can refer the following official documentation to load one query first:

Import data from a database using native database query - Power Query | Microsoft Learn

Connect to a SQL Server database using Power Query. Select the SQL Server database option in the connector selection.

In the SQL Server database popup window:
1).Specify the Server and Database where you want to import data from using native database query.
2).Under Advanced options, select the SQL statement field and paste or enter your native database query, then select OK.

Then you can repeat the above steps for each SQL statement you want to load in Power Query.

Best Regards