Countifs alternative in PowerQuery for MS PowerBI

Question

I have a bit complicated PowerQuery query which has many steps. Within these steps I have a date, team and conditions as below (not actual data)

So the challenge is that I want to count the Pass number for each team for each day and then create another count for Pass and Fail and then it will be used in so many calculations which I can handle later.

I have tried many options, like for example grouping, but it was so confusing because as I mentioned before, the query has so many columns and calculations now. I could successfully solve the issue by creating DAX measure, but the issue here that I need to calculate the average outcome which is not possible to because I couldn't also average the measure of the outcome. So I have no other option but to make the countif though PowerQuery.

Appreciate your help and ideas.

Raw data as text is here in google sheets

Answer 1

Just add column, custom column

= 1

then click select Pass/Fail column, transform .. pivot .. and choose the new column as values column

let  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Custom", each 1),
#"Pivoted Column" = Table.Pivot(#"Added Custom", List.Distinct(#"Added Custom"[PassFail]), "PassFail", "Custom", List.Sum)
in  #"Pivoted Column"

Answer 2

I am assuming that your dates showing the year 0203 is a typo and should be 2023

Not sure exactly what you want for output, but you should be able to adapt the below.

The solution seems to be a simple grouping with a count of the number of passes and/or fails.

The below code generates a separate column for passes and fails per team and date.

It is not sorted in the original order, but that could be added if necessary.

    #"Grouped Rows" = Table.Group(#"Previous Step", {"Date", "Team"}, {
        {"Pass", (t)=>List.Count(List.Select(t[#"PASS/FAIL"], each _ = "Pass")), Int64.Type},
        {"Fail", (t)=>List.Count(List.Select(t[#"PASS/FAIL"], each _ = "Fail")), Int64.Type}
    })

Using your data table from the Google sheet (after correcting the year):

let
    Source = Excel.CurrentWorkbook(){[Name="Table12"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Team", type text}, {"PASS/FAIL", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Date", "Team"}, {
        {"Pass", (t)=>List.Count(List.Select(t[#"PASS/FAIL"], each _ = "Pass")), Int64.Type},
        {"Fail", (t)=>List.Count(List.Select(t[#"PASS/FAIL"], each _ = "Fail")), Int64.Type}
    })
in
    #"Grouped Rows"

Note If you require that all teams show on all dates even if they didn't play, one merely creates a new table containing all dates and teams; and then Joins that to the original table, as in the code below:

let
    Source = Excel.CurrentWorkbook(){[Name="Table12"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Team", type text}, {"PASS/FAIL", type text}}),

//If you need to show all teams on all dates, even if they didn't play on a date
//we merely create a blank table (dates and teams), and execute an outer join.
//then remove the original date/team columns before the grouping.
    #"Date List" = List.Distinct(#"Changed Type"[Date]),
    #"All Teams" =  List.Distinct(#"Changed Type"[Team]),
    Blank = Table.FromColumns(
                {List.Combine(List.Transform(#"Date List", each List.Repeat({_}, List.Count(#"All Teams")))),
                List.Repeat(#"All Teams", List.Count(#"Date List"))},
                type table[dates=date, teams=text]),
    join = Table.Join(Blank,{"dates","teams"}, #"Changed Type",{"Date","Team"}, JoinKind.LeftOuter),
    #"Removed Columns" = Table.RemoveColumns(join,{"Date", "Team"}),

    
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"dates", "teams"}, {
        {"Pass", (t)=>List.Count(List.Select(t[#"PASS/FAIL"], each _ = "Pass")), Int64.Type},
        {"Fail", (t)=>List.Count(List.Select(t[#"PASS/FAIL"], each _ = "Fail")), Int64.Type}
    }),
    #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"dates", Order.Ascending}, {"teams", Order.Ascending}})
in
    #"Sorted Rows"

Answer 3

If you need the zeroes in your final output, you'll need to do a cross join to bring in combinations not present in the original.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDJW0lFyDAISAYnFxUqxOigSrn44JFxcgYRbYmYOuoSfD7KEkb4RilEkSeA0Cr8E3LlIEmDnEpYw1jfWNzAywDSKIgmcduCUQI0PJAnU+CDGKNSwotwOiFGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Team = _t, #"PASS/FAIL" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}, {"Team", type text}, {"PASS/FAIL", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Date", "Team"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    Custom1 = List.Distinct( #"Grouped Rows"[Date]),
    #"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Team", each List.Distinct( #"Grouped Rows"[Team])),
    #"Expanded Team" = Table.ExpandListColumn(#"Added Custom", "Team"),
    #"Merged Queries" = Table.NestedJoin(#"Expanded Team", {"Date", "Team"}, #"Grouped Rows", {"Date", "Team"}, "Expanded Team", JoinKind.LeftOuter),
    #"Expanded Expanded Team" = Table.ExpandTableColumn(#"Merged Queries", "Expanded Team", {"Count"}, {"Count"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Expanded Team",null,0,Replacer.ReplaceValue,{"Count"})
in
    #"Replaced Value"

Stepping through the code:

1. Group by date and team and create a count:

2. Get a distinct list of dates
3. Convert to table
4. Add column for a cross join with all teams (to get zero values later)
5. Expand
6. Merge back to the grouped step to pull in the previous grouped values.
7. Replace nulls with 0

You can amend this for your other question by simply filtering for pass before you do the grouping.