Scenario:
In the following scenario, we will discuss about how to perform the Calculations like the classic Excel Functions, SUMIFs or COUNTIFs or AVERAGEIFs using Power Query List functions.
Suppose we have sales table “tbl_Sales” as follows. Based on this table we will be calculating the CountIFs, SumIFs, AverageIFs.
Now from the above table, we
will be calculating the CountIFs, SumIFs, AverageIFs, MinIFs and MaxIFs in to the following Summary table “ds_SummaryStatistics”.
The calculations should be done based on the following Conditional criteria:
tbl_Sales[CntryNam]=ds_SummaryStatics[Country] && tbl_Sale[ProdNam]=ds_SummaryStatics[Product]
Now lets calculate the
the CountIFs, SumIFs, AverageIFs, MinIFs and MaxIFs using the List Functions of the Power Query in Custom Columns as follows:The Count of Products, where tbl_Sales[CntryNam]=ds_SummaryStatics[Country] && tbl_Sale[ProdNam]=ds_SummaryStatics[Product] :
CountIFs = List.Count(
Table.SelectRows(tbl_Sales,
(varCalc) => varCalc[CntryNam]=[Country]
and varCalc[ProdNam]=[Product] )
[ProdNam])
The Sum of Units Sold:
SumIFs = List.Sum(
Table.SelectRows(tbl_Sales,
(varCalc) => varCalc[CntryNam]=[Country]
and varCalc[ProdNam]=[Product] )
[Units_Sold])
The Average of Units Sold:
AverageIFs = List.Average(
Table.SelectRows(tbl_Sales,
(varCalc) => varCalc[CntryNam]=[Country]
and varCalc[ProdNam]=[Product] )
[Units_Sold])
The Maximum of the of Units Sold :
MaxIFs = List.Max(
Table.SelectRows(tbl_Sales,
(varCalc) => varCalc[CntryNam]=[Country]
and varCalc[ProdNam]=[Product] )
[Units_Sold])
The Minimum of the of Units Sold :
MinIFs = List.Min(
Table.SelectRows(tbl_Sales,
(varCalc) => varCalc[CntryNam]=[Country]
and varCalc[ProdNam]=[Product] )
[Units_Sold])
#Result:
The Complete Power Query is as follows:
let
Source = Excel.Workbook(File.Contents(DataSource), null, true),
ds_Sample_Sheet = Table.PromoteHeaders( Source{[Item=”ds_Sample”,Kind=”Sheet”]}[Data], [PromoteAllScalars=true]),
ChangeType = Table.TransformColumnTypes(ds_Sample_Sheet,{{“Product”, type text}, {“Country”, type text}}),
ProductsCount = Table.AddColumn(ChangeType, “CountIFs”, each List.Count(
Table.SelectRows(tbl_Sales,
(varCalc) => varCalc[CntryNam]=[Country]
and varCalc[ProdNam]=[Product])[ProdNam])),
SumOfUnitsSold = Table.AddColumn(ProductsCount, “SumIFs”, each List.Sum(
Table.SelectRows(tbl_Sales,
(varCalc) => varCalc[CntryNam]=[Country]
and varCalc[ProdNam]=[Product])[Units_Sold])),
AveragUnitsSold = Table.AddColumn(SumOfUnitsSold, “AverageIFs“, each List.Average(
Table.SelectRows(tbl_Sales,
(varCalc) => varCalc[CntryNam]=[Country]
and varCalc[ProdNam]=[Product])[Units_Sold])),
MaxOfUnitsSold = Table.AddColumn(AveragUnitsSold, “MaxIFs”, each List.Max(
Table.SelectRows(tbl_Sales,
(varCalc) => varCalc[CntryNam]=[Country]
and varCalc[ProdNam]=[Product])[Units_Sold])),
MinOfUnitsSold = Table.AddColumn(MaxOfUnitsSold, “MinIFs“, each List.Min(
Table.SelectRows(tbl_Sales,
(varCalc) => varCalc[CntryNam]=[Country]
and varCalc[ProdNam]=[Product])[Units_Sold])),
#”Changed Type” = Table.TransformColumnTypes(MinOfUnitsSold,{{“Product”, type text}, {“Country”, type text}, {“CountIFs”, Int64.Type},
{“SumIFs”, Int64.Type}, {“AverageIFs”, type number}, {“MaxIFs”, Int64.Type}, {“MinIFs”, Int64.Type}})
in
#”Changed Type”
——————————————————————————————————–
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
——————————————————————————————————–
Be the first to comment