topic Re: Powe Query - How do get the ttl count of the column is >0? in DAX Commands and Tips

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

iekiekiek — Tue, 06 Aug 2024 02:50:19 GMT

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

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

Jihwan_Kim — Tue, 06 Aug 2024 03:14:27 GMT

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"

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

Anonymous — Tue, 06 Aug 2024 09:34:05 GMT

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.

topic Re: Powe Query - How do get the ttl count of the column is &gt;0? in DAX Commands and Tips

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

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

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

topic Re: Powe Query - How do get the ttl count of the column is >0? in DAX Commands and Tips