Do you want to learn how to write SQL queries in Power BI? This guide will show you how to do just that. It will teach you the basics of SQL and walk you through the steps of writing a query. By the end of this tutorial, you will be able to write complex queries that return valuable insights into your data.
1. Connect to SQL Server
The first step is to connect to SQL server so you can import data into Power BI and create a dashboard with it.
Install the SQL Server Management Studio (SSMS)
The SSMS is a free tool from Microsoft that you can use to connect to SQL servers and manage them. It allows you to import, manage, and monitor all of your data, and write queries and scripts.
When you have downloaded the SSMS and opened it, you will be shown a screen asking for specific credentials. For the purposes of this tutorial, we will use a practice database.
Fill in the credentials to gain access to the server
Add the below credentials to log into the server:
- Server Name: ec2-52-14-205-70.us-east-2.compute.amazonaws.com
- Authentication: SQL Server Authentication
- Login: SQL
- Password: SQL
This will give you access to the SuperStoreUS database which contains information about the products, sales, and profits of a fictitious company. You can use this to practice importing queries into Power BI and manipulating the data to gain new insights.
Inputting these credentials gives you access to one instance (ec2-52-14-205-70.us-east-2.compute.amazonaws.com), which contains the SuperStoreUS database. Each database is then broken down into tables containing different pieces of information (sales, orders, customers, etc.)
In this form, you can look at the raw data and gain some insights. However, by importing the SQL data into Power BI, you can create dashboards and generate links in the data to get a much deeper look at the information.
2. Import your SQL data into Power BI
Once you have connected to your SQL Server, the next step is to import the data into Power BI.
In Power BI, go to the Home tab and select Get Data. You can then click the SQL Server option.
This will open a new window where you can enter the details for your connection. Once you put the credentials in, your SQL server is connected. You will see the data from the SQL database organized by table. Use the tickboxes next to each table to view it.
When connecting your SQL server to Power BI, there are two main options you can use; Import or Direct Query.
The Import function will copy the data from your SQL server into Power BI. This means that you will have a local copy of the data on your computer which you can use to create dashboards, reports, and models.
The Direct Query function will not import the data into Power BI. Instead, it will connect to the SQL server and allow you to query the data directly. This means that any changes you make to the source data will automatically be updated on your Power BI dashboard.
Using the Direct Query function does have benefits because it means that you don’t need to upload the data each time you make a change. It automatically updates for you, so it is good for tracking information that is updated regularly. However, there are some limitations on the data manipulation options that you can use in Power BI.
If you want to maximize the options that you have, using the Import function is a better option.
3. Open the Query Editor
Once you have imported your data, it will be displayed in the data view as individual datasets on Power BI. In order to manipulate the data and make simple changes, you need to use the Query Editor.
To open the Query Editor, click on the Edit Queries button on your toolbar.
This will open a new window where you can see all of your datasets and the tables within them. You can then rename them, remove columns, and perform other basic functions. For more complex operations, you need to open the Advanced Editor. This editor shows you the code for the query so you can make more specific modifications.
When you have finished making alterations, click Close and Apply and your changes will be saved.
4. Merge your datasets
Merging datasets can be useful when you want to combine data from multiple tables into a single dataset. This can be done by using the Merge Queries function in the Query Editor.
Start by loading all of the datasets that you want to combine. For example, load the orders, customers, and returns datasets from the SuperStoreUS database. Now that you have the datasets, open the Query Editor and click Combine. You will then get a dropdown menu with different options for combining datasets. Navigate to the Merge option.
You will then see a Merge dialog box where you can select the tables you want to merge and the type of merge that you want. In order for this to work, you need to choose columns that are in all of the tables.
This will create a new query that combines columns from different datasets into a single query. This makes it much easier to work with a single dataset in Power BI, instead of using multiple sets.
5. Create your dashboard
Now that you have organized your datasets and merged them, where necessary, you can create your Power BI dashboard.
To do this, go to the Home tab and select Dashboard. You will then see a list of all of your datasets. Click on the one that you want to use and it will be added to the dashboard.
You can then drag and drop different elements, or tiles, onto the dashboard to create your desired layout. For example, you could add a table, bar chart, and map to your dashboard.
When you have finished creating your dashboard, click on the Save button and give it a name. You can then share it with other people or embed it on a website.
6. Publish your new Power BI dashboard
Your dashboard can be published onto the Power BI service, where it is then easily accessible.
To publish your dashboard, go to the File menu and select Publish. You will then see a dialog box with different options for publishing.
When your dashboard is published, it will be available on the Power BI website and you can access it from any device that has an internet connection.
Conclusion
Combining SQL and Power BI can be a powerful way to analyze data. In this article, we have shown you how to import your data into Power BI, make basic changes to it, and create a dashboard. You can then publish the dashboard and share it with other people.
Now that you know how to write SQL queries in Power BI, you can start manipulating your data and creating better reports and dashboards.