How to return Max Sale Date and Product with correct Totals using DAX in Power BISuppose we have simple Data Modal as follows.The sample Data from ds_Sample is as follows:----------------------------------------------------------------Scenario 1:Returning the Max Sale Date, and the Product that contributing the Max SalesWe can achieve this Scenario using the following DAX Functions.Net ..read more

How to Calculate the Average of Main Category based on Averages of Sub Category using DAX in Power BIScenario:We have sample Dataset as follows, with various Dimension levels like:Region > Country > Product_Segment > ProductThis sample dataset has the Quantity of Products sold value (Qnty_Sold) either by Online or Offline (Sale_Mode).Based on this Dataset, first we need to Calculate the ..read more

How to calculate the ROLLUP Sub Totals and Grand Total Count, Sum and Average values for each Dimension Level in SQL ServerScenario:Suppose we have a sample table with Price values for different Hierarchy Levels like Region, Country, ProdGroup, ProdName, as shown below./* Drop the table if it arleady existsDROP TABLE IF EXISTS [dbo].tbl_Sample;*//* Create a Sample table */CREATE TABLE [dbo ..read more

How to return Minimum and Maximum value across multiple Columns in SQL ServerScenario:Suppose we have a sample Products table with Price values across multiple Years as shown below./* Create a sample table */CREATE TABLE [dbo].[tbl_Products]  (    [ProdID] [int] NOT NULL,    [ProdName] [varchar](255) NOT NULL,    [YR2010] [int] NULL,    [2011] [int ..read more

How to Aggregate or Concatenate the Numbers and Strings using STRING_AGG Function in SQL ServerScenario:Suppose we a sample Categories table as follows.. /* Create sample Categories table */CREATE TABLE [dbo].[tbl_Categories]    (        [CatID] [int] NULL,        [MainCat] [varchar](5) NULL,        [SubCat] [varchar ..read more

How to use PATINDEX Function to Remove the Special Characters from a Column in SQL ServerScenario:Suppose we have a sample data with Special Charaters as shown below:1!2@3#4$5%6^7&8*9(0)_+,<.€25>/?\|ABC$123.45abc|€987.45$123.45Gold@$916.75We need to remove the Special Characters like !@#$%^&*()_+,<€>/?\| , and clean the data as per below ..read more

How to create Root Node and Leaf Node Hierarchy using Recursive CTE in SQL ServerScenario:Suppose we have a sample data with ParentNode and ChildNode columns as follows.Lets say:A1 is the Parent Node for the Child Node B1. This Child Node can be RootNode or a Parent Node for other Child Nodes as per below.B1 is the Parent Node for {C1, D1}.C1 is the Parent Node for {E1, F1}.Please note that, the ..read more

How to create Root Node and Leaf Node Hierarchy using M-Query in Power BIScenario:Suppose we have a sample data with ParentNode and ChildNode columns as follows.Lets say:A1 is the Parent Node for the Child Node B1. This Child Node can be RootNode or a Parent Node for other Child Nodes as per below.B1 is the Parent Node for {C1, D1, E1,F1}.C1 is the Parent Node for {G1, H1}.Please note that, the ..read more

How to view the Schema of a Table or Dataset in Power QueryScenario:Suppose we have a sample table/dataset "tbl_Sample" in Power BI as follows:Now we can view the Schema ( Columns definition) of a Table or Dataset using M-Query is as follows:= Table.SelectColumns(Table.Schema(ChangeType), {"Name", "Position", "TypeName", "Kind", "IsNullable"})Result:let    Source = Table.FromRows(Json.Document ..read more

How to Transpose Rows to Columns using M-Query in Power BIScenario:Suppose we have a Sample dataset as follows:Now we can transpose the Rows to Columns using the following M-Query:let    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Ncq5DQAgDMDAXVLTkGcCNqBF2X8NCFYqSyefI3uuKaOixCTHY4UVrnw22GDr22GHvTnggCuZFw==", BinaryEncoding.Base64), Compression.Deflate)), let _t ..read more