Share via

Excel Power Query CountIf for Positive Values in a Row

Anonymous
2024-05-14T19:57:06+00:00

I am trying to add a custom column in Power Query that counts the number of positive values in each row of data. My data is formatted as below

Item 01/01/2024 01/02/2024 01/03/2024 01/04/2024 01/05/2024
A 10 8 4 -4 -6
B 5 0 -1 -8 -8
C 12 8 5 1 -2

For Row 2 (Product A) I would like to return 3.

For Row 3 (Product B) I would like to return 2.

For Row 4 (Product C) I would like to return 4.

In Excel, this is easily accomplished with a CountIf formula that returns the count of all cells in the specified range that are >= 0. I cannot figure out how to translate that into a Power Query formula. Any help is much appreciated!

Microsoft 365 and Office | Excel | Other | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2024-05-14T23:06:15+00:00

    Hi,

    This M code works

    let 

    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], 

#"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"01-01-2024", Int64.Type}, {"01-02-2024", Int64.Type}, {"01-03-2024", Int64.Type}, {"01-04-2024", Int64.Type}, {"01-05-2024", Int64.Type}}), 

#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Count(List.Select(List.Skip(Record.ToList(\_),1), each \_>0)))

    in 

    #"Added Custom"

    Hope this helps.

    1 person found this answer helpful.
    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2024-05-15T22:59:15+00:00

    You are welcome.

    0 comments
  2. Anonymous
    2024-05-15T12:29:07+00:00

    That works. Thank you for the quick and helpful response!

    0 comments