Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code FABINSIDER for a $400 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi,
I have a table that looks something like this:
Person | Criteria 1 | Criteria 2 | Criteria 3 |
A | 1 | 0 | 1 |
B | 1 | 1 | 0 |
C | 0 | 1 | 0 |
D | 0 | 1 | 0 |
E | 1 | 1 | 0 |
F | 0 | 0 | 1 |
I need to create a transformation that will count the number of times a person has a combination of different criteria:
Category 1 | Category 2 | Count |
Criteria 1 | Criteria 1 | 3 |
Criteria 1 | Criteria 2 | 2 |
Criteria 1 | Criteria 3 | 1 |
Criteria 2 | Criteria 1 | 2 |
Criteria 2 | Criteria 2 | 4 |
Criteria 2 | Criteria 3 | 0 |
Criteria 3 | Criteria 1 | 1 |
Criteria 3 | Criteria 2 | 0 |
Criteria 3 | Criteria 3 | 2 |
Any advice would be helpful.
Thank you!
Solved! Go to Solution.
Hi @Yamabushi
let
Source = YourSource,
Unpivot = Table.UnpivotOtherColumns(Source, {"Person"}, "Criteria1", "Value"),
Join = Table.NestedJoin(Unpivot, {"Person"}, Unpivot, {"Person"}, "Unpivot", JoinKind.LeftOuter),
Expand = Table.ExpandTableColumn(Join, "Unpivot", {"Criteria1", "Value"}, {"Criteria2", "Value2"}),
Product = Table.AddColumn(Expand, "Product", each [Value] * [Value2], type number),
Group = Table.Group(Product, {"Criteria1", "Criteria2"}, {{"Count", each List.Sum([Product]), Int64.Type}})
in
Group
Stéphane
Hi @Yamabushi
let
Source = YourSource,
Unpivot = Table.UnpivotOtherColumns(Source, {"Person"}, "Criteria1", "Value"),
Join = Table.NestedJoin(Unpivot, {"Person"}, Unpivot, {"Person"}, "Unpivot", JoinKind.LeftOuter),
Expand = Table.ExpandTableColumn(Join, "Unpivot", {"Criteria1", "Value"}, {"Criteria2", "Value2"}),
Product = Table.AddColumn(Expand, "Product", each [Value] * [Value2], type number),
Group = Table.Group(Product, {"Criteria1", "Criteria2"}, {{"Count", each List.Sum([Product]), Int64.Type}})
in
Group
Stéphane
Yes! Thank you 🙂
Hi,
Thanks for your answer, but sadly this is not the solution I'm looking for.
I am looking for a way to transform the first table into the structure seen in the second table. It has all the combinations of Criteria listed in 2 separate columns while the third column shows how many times a person was positive in both criteria of a separate row.
If I'm interpreting your question the right way you can just use the group by function in the UI.
let
Source = PUTYOURSOURCEHERE,
#"Grouped Rows" = Table.Group(Source, {"Criteria 1", "Criteria 2", "Criteria 3"}, {{"Count", each Table.RowCount(_), Int64.Type}})
in
#"Grouped Rows"
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the February 2025 Power BI update to learn about new features.
User | Count |
---|---|
27 | |
26 | |
21 | |
12 | |
10 |
User | Count |
---|---|
27 | |
25 | |
22 | |
17 | |
13 |