Log In
Register
Home
New Member
March 5, 2021
Offline
Hi all,
I've read some helpful posts about recreating COUNTIF in Power Query (Numbering grouped data power query) but I'm having trouble when it comes to having multiple conditions e.g. COUNTIFS
In the attached simplified example I have a list of tasks - each Task is in a span, which is in a Region. Because my data is at a task level but my reporting is at the span level I use COUNTIFS to create a Span Count column =(COUNTIFS($A$1:$A2,A2,$B$1:B2,B2)=1)+0
I would really appreciate any help on how I can achieve something similar in Power Query.
Thanks
Trusted Members
Moderators
November 1, 2018
Offline
It sounds to me like using a pivot table with a distinct count would do what you need without any need to create this specific column in the source data. What does your actual output need to look like?
New Member
March 4, 2021
Offline
if I understand your problem correctly, you could group by both columns (Area and Span)
let
Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
#"Gruppierte Zeilen" = Table.Group(Quelle, {"Area", "Span"}, {{"Count", each _, type table [Area=text, Span=number, Task=text]}}),
#"Hinzugefügter Index" = Table.AddIndexColumn(#"Gruppierte Zeilen", "Index", 1, 1, Int64.Type),
#"Erweiterte Count" = Table.ExpandTableColumn(#"Hinzugefügter Index", "Count", {"Task"})
in
#"Erweiterte Count"
Regards
Melanie
1 Guest(s)
