Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
prashantg364
Helper II
Helper II

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

CircleClusterTypeBandDate
AA_001POST723-5-23
AA_001POST34-6-23
AA_001PRE7

13-4-

23

AA_001PRE311-4-23
BB_001POST723-5-23
BB_001PRE713-4-23
BB_001PRE311-4-23
CC_001POST323-5-23
CC_001POST74-6-23
CC_001PRE313-4-23
CC_001PRE711-4-23

 

 

The output which I am looking for is

CircleClusterTypeBandDate
AA_001POST723-5-23
AA_001POST34-6-23
BB_001POST723-5-23
BB_001PRE311-4-23
CC_001POST323-5-23
CC_001POST74-6-23

 

Can any one help.

6 REPLIES 6
v-jianboli-msft
Community Support
Community Support

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:

vjianbolimsft_0-1688354390837.png

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 nameProduct bandKPI Category - 3 KPI(KPI1,KPI2,KPI3)KPI Category - 5 KPI(KPI1,KPI2,KPI3,KPI4,KPI5)KPI1KPI2KPI3KPI4KPI5
Car73511111
Car31210001
Bike72211000
Bike33311100
Cycle73411110
Cycle30000000

 

Ouput table

 

Product nameProduct bandTotal pass in 3 KPITotal pass in 5 KPIFailed KPI -KPI Category - 3 KPI(KPI1,KPI2,KPI3)Failed KPI - KPI Category - 5 KPI(KPI1,KPI2,KPI3,KPI4,KPI5)
Car735  
Car312KPI2,KPI3KPI2, KPI3, KPI4
Bike722KPI3KPI3, KPI4, KPI5
Bike333 KPI4, KPI5
Cycle734 KPI5
Cycle300KPI1,KPI2,KPI3KPI1,KPI2,KPI3,KPI4,KPI5

Hi @prashantg364 ,

 

Please try:

First Unpivot columns:

vjianbolimsft_0-1689313927034.png

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:

vjianbolimsft_1-1689314042282.png

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
Not applicable

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
Super User
Super User

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.