Forum Discussion
Summarize table
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.
6 Replies
Hi prashantg364
For B why did you consider 11-4-23 not 13-4-23?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
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 TypeHi 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.
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 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.
