Powe Query - How do get the ttl count of the column is >0?

[iekiekiek]

Hi All, i have a table as follow and would like to add a column custom column (TTL Item as follow) in power query that counts all the rows that are >= 1, i have found multiple formulas but they didn't calculate correctly. Can you please help?

Name	Apple	Banana	Grapes	TTL Item
John	1		2	2
Mary		2		1
David	1	3	4	3
Tom	1	1		2

[Rakesh1705 · Accepted solution]

Source Data

Replacing null with 0

Finding the Apple Count

Same way Banana and Grapes Count is found

Total Item=Apple Count+Banana Count+Grapes Count

If this solves your issue then please accept the same as your solution.

[Jihwan_Kim]

Hi,

I am not sure if I understood your quetion correctly, but please check the below picture and the attached pbix file.

let
    Source = Data_source,
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Name"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each [Value] >= 1),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Name"}, {{"TTL Item", each Table.RowCount(_), Int64.Type}}),
    #"bring back Source" = Source,
    #"Merged Queries" = Table.NestedJoin(#"bring back Source", {"Name"}, #"Grouped Rows", {"Name"}, "Grouped Rows", JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"TTL Item"}, {"TTL Item"})
in
    #"Expanded Grouped Rows"