New Member
March 5, 2021
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
New Member
March 4, 2021
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)