Replace nulls with zeros in Power BI

This video demonstrates on how we can relace a specific value with another value. One such case explained in this video is on how we can replace nulls with zeros. 

Connect to MySQL DB | This connector requires one or more additional components to be installed

This video describes how to connect to MySQL Database and fix an common issues with the Power BI MySQL connector  which says "This connector requires one or more additional components to be installed". 

Download link to MySQL NET Connector

https://dev.mysql.com/downloads/connector/net/

Hide or Unhide Visuals in Power BI using bookmarks

In this video we create a Power BI report and use bookmarks to Hide or unhide card visuals.

Combine Multiple files and load to Power BI

In this video we discuss on how we can combine multiple files and load into Power BI. For the demo, we have the following 3 scenarios

1. Data Sets with same number of Columns
2. Data Sets with different number of Columns
3. Data Sets with different file types

Power BI Content Sharing - Who can see what

This video explains on who can see what content when Power BI content is created or shared across the license types.

Using Custom fonts in Power BI

Custom Fonts are one of the major factors of Branding and for enterprises branding means a lot. This video demonstrates how to use custom fonts in Power BI which are currently not available by default.

Unzip and extract file contents to Power BI

 

This video describes how to unzip and extract file contents to Power BI. Reading zip files without extracting them. 

Link to Mark's Blog: https://sql10.blogspot.com/2016/06/reading-zip-files-in-powerquery-m.html

Display Alternate text when a image URL is not present in Power BI

 

This video describes how to display alternate text when there is no data present for the image URL in Power BI.

Display default image when there is no data present for the image URL in Power BI

 

This video describes how to display a default image when there is no data present for the image URL in Power BI.

Display URL's as images in Power BI

 

This video describes how to display URL's as images in Power BI.

Connect to Azure Data Lake from Power BI

This video describes how to connect to Azure Data Lake from Power BI.

Download link to Azure Open Datasets: 

https://docs.microsoft.com/en-us/azure/open-datasets/dataset-catalog

T-SQL to find Server and SQL restart date time

This script comes handy when you would like to find out the Server and SQL services restart times.

DECLARE @SQLServiceRestartDateTime AS VARCHAR(50),
		@ServerRestartDateTime AS VARCHAR(50)

SELECT @SQLServiceRestartDateTime = create_date FROM sys.databases WHERE database_id=2
SELECT @ServerRestartDateTime = CAST(DATEADD(SECOND, (ms_ticks/1000) * -1, GETDATE()) AS datetime) FROM sys.dm_os_sys_info

SELECT @SQLServiceRestartDateTime AS [SQL Service Start DateTime], @ServerRestartDateTime AS [Server Start DateTime]

T-SQL to find Backup or Restore Progress

Here is a script that comes handy while performing a huge database Backup or Restore. This script provides the details on the progress of the Backup or Restore operation including the estimated finish time.

SELECT 
   session_id AS SPID, command AS [Command], a.text AS Query, start_time AS [Start Time], 
   percent_complete AS [Percent Complete],
   dateadd(second,estimated_completion_time/1000, getdate()) AS [Estimated Completion Time]
FROM sys.dm_exec_requests r 
   CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a 
WHERE r.command like 'BACKUP%' OR r.command like 'RESTORE%') 

T-SQL to find Navigation path in CMS

In large enterprises there will be huge number of SQL servers which will be registered within a Central Management Server (CMS) and times it may become difficult to find out where the server is registered atleast for new team members in the Huge pile of servers and folder structure. This query give you the path where the specified servers is registered within the CMS.

DECLARE @ServerName nvarchar(50)
SET @ServerName='SQL01';

WITH CMSCTE
AS
(
--Anchor
SELECT server_group_id, name, description, parent_id, 1 AS [Level], CAST((name) AS VARCHAR(MAX)) AS CMSPath
FROM msdb.dbo.sysmanagement_shared_server_groups AS A
WHERE parent_id IS NULL
UNION ALL
--Recursive Member
SELECT B.server_group_id, B.name, B.description, B.parent_id, C.[level] + 1 AS [Level], CAST((C.CMSPath + '->' + B.Name) AS VARCHAR(MAX)) AS CMSPath
FROM msdb.dbo.sysmanagement_shared_server_groups AS B
JOIN CMSCTE AS C 
ON B.parent_id = C.server_group_id  
)

SELECT TOP 1 CMSPath AS 'Path in CMS' , B.name as 'Server Name', B.description AS 'Server Description', A.name AS 'Group Name', A.description AS 'Group Description'
FROM CMSCTE AS A
INNER JOIN msdb.dbo.sysmanagement_shared_registered_servers AS B
ON A.server_group_id=B.server_group_id
WHERE B.name = @ServerName
ORDER BY [Level] DESC

T-SQL to find Remote Server Name of the linked server

At times it happens that you might have named a linked server with a friendly name that the actual server name. As the time flies and people change in the team, it will become difficult to identify how or to where this is linked.  You can find the information using the sp_linkedservers stored procedure but what if the linked server configured uses a entirely different DNS name than the actual server? The below query helps to get the Remote Server Name with few other details when run against a linked server.  

DECLARE @linkedServerName varchar(100), @sql varchar(max)
SET @linkedServerName='Your Linked Server Name'
SET @sql = CONCAT('SELECT * FROM OPENQUERY([',@linkedServerName , '], 
''SELECT ''''',@linkedServerName,''''' AS LinkedServerName, 
@@SERVERNAME AS RemoteServerName, SUSER_SNAME() AS ConnectedWith, DB_NAME() AS DefaultDB'')')
EXEC (@sql)