Power BI Kingdom
751 FOLLOWERS
The Power BI Blog for Professional Learning of the Microsoft Power BI, SQL Server, T-SQL, ETL, R Programming, and Excel VBA Macros
Power BI Kingdom
2M ago
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
Power BI Kingdom
2M ago
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
Power BI Kingdom
2M ago
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
Power BI Kingdom
2M ago
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
Power BI Kingdom
2M ago
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
Power BI Kingdom
2M ago
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
Power BI Kingdom
2M ago
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
Power BI Kingdom
3M ago
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
Power BI Kingdom
5M ago
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
Power BI Kingdom
5M ago
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