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".
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])
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
--------------------------------------------------------------------------------------------------------
No comments:
Post a Comment
Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.