Power BI – Get data from SQL Server Custom Query and display in Table, Matrix and Decomposition Tree

In this article, we will learn how to get data from SQL Server and display it in various visualizations 
Let's create some tables in our SQL database and insert some records. Here I'm going to create 2 new tables in SQL Server i.e. Country and State.

CREATE TABLE [dbo].[Country](

[CountryId] [int] NOT NULL,

[CountryName] [varchar](100) NOT NULL

) 

CREATE TABLE [dbo].[State](

[StateId] [int] NOT NULL,

[CountryId] [int] NOT NULL,

[StateName] [varchar](50) NOT NULL,

)

INSERT [dbo].[Country] ([CountryId], [CountryName]) VALUES (1, N'India')

INSERT [dbo].[Country] ([CountryId], [CountryName]) VALUES (2, N'Srilanka')

INSERT [dbo].[State] ([StateId], [CountryId], [StateName]) VALUES (1, 1, N'Punjab')

INSERT [dbo].[State] ([StateId], [CountryId], [StateName]) VALUES (2, 1, N'Himachal')

INSERT [dbo].[State] ([StateId], [CountryId], [StateName]) VALUES (3, 2, N'Colombo')

Now our tables are ready and we have records. Let's write SQL Query to fetch the records.

SELECT c.CountryId,c.CountryName,st.StateId,st.StateName FROM country c 

INNER JOIN state st on c.CountryId=st.CountryId

Let's Open Power BI and fetch the records through a custom query.

Step 1: Click on Get data --> Go to SQL Server

Step 2: Select your Server, Database and Go to Advance and write Custom Query

Step 3: Enter your credentials. If you are using windows authentication for the database then click "Use my current credentials"

Step 4: Once you press connect it will display all the records. You can press the Transform data button if you want to Add/Edit rows/columns. Otherwise, press the Load button.

You can go to Visualizations and can add Charts, Table, Matrix, etc. Here in this example, we will add a table. Double click on the Table icon.

Table Visualization

Step 1: Go to Visualizations and select Table.

Step 2: Drag the Fields in Values in Visualizations. You can see the records on the left side.

Step 3: You can format the data, By clicking on the format and change the format.

Decomposition Tree

Step 1: Go to Visualizations and select Decomposition Tree.

Step 2: You can display the data in Tree. Drag the column in Analyze and Explain By

You can see the Country and State in Tree mode as mentioned above.

Matrix

Step 1: Go to Visualizations and select Matrix.
Step 2: Drag the Fields based on your requirement. In this example, I have displayed countries name and state count.

You can't format the result as we did in the first example. You can change the font, color, background, Header, etc.

Also, you can publish the report to the Web then you can click the publish button and connect with your credentials and Publish to Power BI.

This is all about this article. thank you.