If you want to import data from a SQL query instead of a table then this article will help you. Sometimes we have a very complex SQL query. So, if we create a table and populate it with this query and then import it in to the power BI it will take more time. So, we call this query directly in Power BI. Follow the below steps.
Step 1
I have three tables in my database and I have created a join query on these tables. In table 1 i.e. Sales.CurrencyRate I have the below data.
And table 2 i.e. Sales.Currency contains the below data
And table 3 i.e. Sales.CountryRegionCurrency has the data taht you can see in the below image.
So, I have created the below query using these table. See the below image for query and data.
Now I want to import the data using this query in Power BI.
Step 2
Go to ‘Home’ tab of Power BI and click on ‘Get Data’ drop down and further click on ‘Sql Server’. A new SQL Server database window will open. Provide server name and database name. Select ‘DirectQuery’ as data connectivity mode.
Expand ‘Advanced Option’ and paste your query in ‘Sql statement’. Click on ‘Ok’ to submit query.
If you are connecting your server and database for the first time then after clicking on ok you need to provide credentials of the database and click on ‘Connect’.
Your database will be connected and a new window will open that shows the data. Click on ‘Load’ to import the data.
Your data has been loaded and shown at the right side of the tool. It’s default name is ‘Query1’.
You can rename it by right clicking on it and further clicking on ‘rename’.
Step 3
If you want to change any thing in this query then right click on it and click on ‘Edit Query’. Now Power query editor window will be open. Click on source setting.
Once you click on ‘Source setting’ you will go back to the SQL server database window where you pasted your query. Change your query and click on ‘Ok’.
To save changes click on ‘Close & Apply’ in ‘File’ tab.
Change will reflect on your main power bi window.
Summary
I hope you understood the above steps. It will help you a lot when you create business logics in query and import it directly in power bi. Thanks for reading. Have a good day.