Forum Discussion

E_K_'s avatar
E_K_
Icon for Helper III rankHelper III
2 years ago
Solved

Help with errors in Group by List.sum in power query?

I'm trying to create a group by table per date and client. All the other columns are fine other than the "On Time Packages" column 
This is for column On Time, with shows for each package a 0 if it was on time and 1 if it was late. The column is number type and there are no null values. 

I've tried numerous different things to try to get this to work: even created another column to try the same thing by creating a Y for on time and N for late and trying to count the Ys and that still doesn't work. Please help!!

Table.Group(
#"Added Custom",
{"package_date"},
{
{"Overall Delay", each List.Sum([Delay in mins]), type number},
{"Average Delay", each List.Median([Delay in mins]), type number},
{"On Time Packages", each List.Sum(List.Select([On Time ], each _ =0)), type nullable number},
{"Total Packages", each Table.RowCount(_), type number}
}
)

Need Help
  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    2 years ago

    Hello E_K_,

     

    It looks like you're on the right track with your approach to grouping in Power Query. 

     

    Here's a slightly revised version of your query:

    Table.Group(
    #"Added Custom",
    {"package_date"},
    {
        {"Overall Delay", each List.Sum([Delay in mins]), type number},
        {"Average Delay", each List.Average([Delay in mins]), type number},
        {"On Time Packages", each List.Count(List.Select([On Time], each _ = 0)), type number},
        {"Total Packages", each Table.RowCount(_), type number}
    }
)

     

1 Reply

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Icon for Super User rankSuper User
    2 years ago

    Hello E_K_,

     

    It looks like you're on the right track with your approach to grouping in Power Query. 

     

    Here's a slightly revised version of your query:

    Table.Group(
    #"Added Custom",
    {"package_date"},
    {
        {"Overall Delay", each List.Sum([Delay in mins]), type number},
        {"Average Delay", each List.Average([Delay in mins]), type number},
        {"On Time Packages", each List.Count(List.Select([On Time], each _ = 0)), type number},
        {"Total Packages", each Table.RowCount(_), type number}
    }
)