Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
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.
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.
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
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
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
27 | |
13 | |
11 | |
9 | |
6 |