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.

Connect your Azure App Service to Custom Domain

In this article, we will learn how we can add a custom domain to our website. I hope you are already aware of how to publish the website to Azure. Once you publish the website on azure you will get a URL like xxx.azurewebsites.net. Now you want your domain name let’s say www.abc.com to open the website xxx.azurewebsites.net that is hosted on Azure.

Step 1: Ensure your website is published on a minimum B1 Plan because the Custom Domain feature is not available on Free Plan (F1)

Step 2: Go to App Services -> Custom Domains, Click on it.

Step 3: Click on Add Custom Domain Button

Step 4: Enter your custom domain e.g. (www.abc.com) and click validate button.              

Step 5: It will ask you to select either A record or CNAME. You can choose any one option. You need to prove the domain ownership. In this article, we will use A record.

Step 6: Select A record and scroll down, it will display Type txt and A value. We need to enter these values into our domain provider DNS Settings.

Step 7: Open the website of your domain provider. Let’s say you have purchased the domain from GoDaddy or BigRock. Open their website and go to My Products.

Step 8: Click on your domain and go to the DNS section.

Step 9: Update A Type and Txt Value here.

Wait for 10-15 mins and you can see your website is up and running.

Now when you will open abc.com it will open the same website that is deployed to Azure. This is all about this article. I hope you like it.