Summarize table

[prashantg364]

I need some help summarizing table 
I have the below table as my input, for one cluster there will be either 2 or 3 or 4 or more entries, so based on Type & date I wish to summarize my table

Circle	Cluster	Type	Band	Date
A	A_001	POST	7	23-5-23
A	A_001	POST	3	4-6-23
A	A_001	PRE	7	13-4- 23
A	A_001	PRE	3	11-4-23
B	B_001	POST	7	23-5-23
B	B_001	PRE	7	13-4-23
B	B_001	PRE	3	11-4-23
C	C_001	POST	3	23-5-23
C	C_001	POST	7	4-6-23
C	C_001	PRE	3	13-4-23
C	C_001	PRE	7	11-4-23

The output which I am looking for is

Circle	Cluster	Type	Band	Date
A	A_001	POST	7	23-5-23
A	A_001	POST	3	4-6-23
B	B_001	POST	7	23-5-23
B	B_001	PRE	3	11-4-23
C	C_001	POST	3	23-5-23
C	C_001	POST	7	4-6-23

Can any one help.

[v-jianboli-msft]

Hi @prashantg364 ,

Please try:

Table 2 = SUMMARIZE('Table','Table'[Circle],'Table'[Cluster],'Table'[Band],"Type",CALCULATE(MAX('Table'[Type]),FILTER('Table',[Date]=MAX('Table'[Date]))),"Date",MAX('Table'[Date]))

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[prashantg364]

Hi all,

I have a data set as shown in the input table,

Against each product name & product band, I have 2 KPI categories, 1. 3 KPI and 2. 5 KPI. In columns 3 & 4 I have total pass KPIs in each category. In the following few columns I have the details for failed KPIs in terms of 1 & 0. 1 means KPI is pass & 0 means KPI is fail.

Now I want to summarize my table where I can have only one row for each Product name & Product band combination, followed by pass KPI count in the next 2 columns for 2 different categories, and in the next 2 columns I want the failed KPI names separated by commas, as shown in output table.

Pls help, how I can do this either through power Query editor or by using some DAX.

Input table

Product name	Product band	KPI Category - 3 KPI(KPI1,KPI2,KPI3)	KPI Category - 5 KPI(KPI1,KPI2,KPI3,KPI4,KPI5)	KPI1	KPI2	KPI3	KPI4	KPI5
Car	7	3	5	1	1	1	1	1
Car	3	1	2	1	0	0	0	1
Bike	7	2	2	1	1	0	0	0
Bike	3	3	3	1	1	1	0	0
Cycle	7	3	4	1	1	1	1	0
Cycle	3	0	0	0	0	0	0	0

Ouput table

Product name	Product band	Total pass in 3 KPI	Total pass in 5 KPI	Failed KPI -KPI Category - 3 KPI(KPI1,KPI2,KPI3)	Failed KPI - KPI Category - 5 KPI(KPI1,KPI2,KPI3,KPI4,KPI5)
Car	7	3	5
Car	3	1	2	KPI2,KPI3	KPI2, KPI3, KPI4
Bike	7	2	2	KPI3	KPI3, KPI4, KPI5
Bike	3	3	3		KPI4, KPI5
Cycle	7	3	4		KPI5
Cycle	3	0	0	KPI1,KPI2,KPI3	KPI1,KPI2,KPI3,KPI4,KPI5

[v-jianboli-msft]

Hi @prashantg364 ,

Please try:

First Unpivot columns:

Then add two new columns:

Failed KPI -KPI Category - 3 =
Text.Combine(
Table.SelectRows(#"Unpivoted Columns", (x)=>x[Product name]=[Product name] and x[Product band] = [Product band] and (x[Attribute] ="KPI1" or x[Attribute] ="KPI2" or x[Attribute] ="KPI3") and x[Value]=0)[Attribute],",")

Failed KPI - KPI Category - 5 = 
Text.Combine(
Table.SelectRows(#"Unpivoted Columns", (x)=>x[Product name]=[Product name] and x[Product band] = [Product band] and x[Value]=0)[Attribute],",")

Then Remove duplicate rows

Final output:

Here is the M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wck4sUtJRMgdiYyA2BWJDDByrA1NnDBUzgtIGSBiizikzOxVqoBGSQlTFSAqNkbAhhmKwzZXJOalIbjTB4kZUlcZoLkPFsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Product name" = _t, #"Product band" = _t, #"KPI Category - 3 KPI(KPI1,KPI2,KPI3)" = _t, #"KPI Category - 5 KPI(KPI1,KPI2,KPI3,KPI4,KPI5)" = _t, KPI1 = _t, KPI2 = _t, KPI3 = _t, KPI4 = _t, KPI5 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product name", type text}, {"Product band", Int64.Type}, {"KPI Category - 3 KPI(KPI1,KPI2,KPI3)", Int64.Type}, {"KPI Category - 5 KPI(KPI1,KPI2,KPI3,KPI4,KPI5)", Int64.Type}, {"KPI1", Int64.Type}, {"KPI2", Int64.Type}, {"KPI3", Int64.Type}, {"KPI4", Int64.Type}, {"KPI5", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Product name", "Product band", "KPI Category - 3 KPI(KPI1,KPI2,KPI3)", "KPI Category - 5 KPI(KPI1,KPI2,KPI3,KPI4,KPI5)"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Failed KPI -KPI Category - 3", each Text.Combine(
Table.SelectRows(#"Unpivoted Columns", (x)=>x[Product name]=[Product name] and x[Product band] = [Product band] and (x[Attribute] ="KPI1" or x[Attribute] ="KPI2" or x[Attribute] ="KPI3") and x[Value]=0)[Attribute],",")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Failed KPI - KPI Category - 5", each Text.Combine(
Table.SelectRows(#"Unpivoted Columns", (x)=>x[Product name]=[Product name] and x[Product band] = [Product band] and x[Value]=0)[Attribute],",")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Attribute", "Value"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns", {"Product name", "Product band", "KPI Category - 3 KPI(KPI1,KPI2,KPI3)"})
in
    #"Removed Duplicates"

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @prashantg364 .

What you want to do is to summarize the table.

It also seems that you try to implement some sql-window logic to select POST if that is available.

I suggest u look at summarize table:

Data summarized = SUMMARIZE(
Data,
Data[Circle],
Data[Cluster],
Data[Type],
"Dummy", DISTINCT(Data[Circle])
)

, and try to solve the problem using COALSCE and IF to select correct date values based on Type

[tamerj1]

Hi @prashantg364 
For B why did you consider 11-4-23 not 13-4-23?

[prashantg364]

For B & band 7 , i have both PRE & POST - so I want only POST

For B & band 3, I have only PRE -  so I want only PRE