Forum Discussion

HeimoW's avatar
HeimoW
Regular Visitor
8 years ago
Solved

Remove lines depending on a condition while grouping

I want to group an imported SQL-table in the PowerQueryEditor but exclude all rows which have are not "null" in a certain column.

 

CODE:

let
    Source = Sql.Database("SQL-xxx", "xxxx"),
    dbo_personnel = Source{[Schema="dbo",Item="personnel"]}[Data],
    #"Grouped Rows" = Table.Group(dbo_personnel, {"Co_id"}, {{"Count", each Table.RowCount(_), type number}})
in
    #"Grouped Rows"

 

 

Appreciate any help! Thanks

3 Replies

  • MattAllington's avatar
    MattAllington
    Community Champion
    8 years ago

    How about filtering out the null column before you do the groupby?

    • HeimoW's avatar
      HeimoW
      Regular Visitor
      8 years ago

      Thanks but,

       

      I did not find an option for filtering in the PowerQueryEditor (my workaround was to create a view on the SQL-server). And filtering in the PowerPI Desktop is not an option as the grouping does not allow me to get distinct values in the relevant column.

      (I want to count all personnel which do not have an exit-date) - all active personnel.