Count values from multiple fields

[Anonymous]

Dear Friends,

I want to calculate counts of values available in multiple fields like below -

for User A , Value is showing only in Acre1 field so count will be 1 and for User B, values are showing in 3 field

User	Acre1	Acre2	Acre3	Acre4	Acre5	Count
A	3					1
B	2	0	1			3
C	1	1	6	2	2	5
D	0					1
E	7	6				2
F	6	8	5			3

[PhilipTreacy]

You're welcome @Anonymous 

You're supposed to mark the actual answer as the solution, not your own reply 🙂

Regards

Phil

[Anonymous · Accepted solution]

Thank you so much @PhilipTreacy for quick help . It works for me. 🙂

[PhilipTreacy]

Hi @Anonymous 

You can add a Custom Column in the Power Query editor like this

= Table.AddColumn(#"PreviousStepName", "Addition", each List.NonNullCount({[Acre1], [Acre2], [Acre3], [Acre4], [Acre5]}), Int64.Type)

Here's a full sample query, copy/paste this into a blank query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIGYgSK1YlWcgIyjIDYAIgNkcSdoXwQNoOqMQLLuEBVo5rjCmSYQ9UiCbtBhSyA2BQmHgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [User = _t, Acre1 = _t, Acre2 = _t, Acre3 = _t, Acre4 = _t, Acre5 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"User", type text}, {"Acre1", Int64.Type}, {"Acre2", Int64.Type}, {"Acre3", Int64.Type}, {"Acre4", Int64.Type}, {"Acre5", Int64.Type}}),
    #"Inserted Sum" = Table.AddColumn(#"Changed Type", "Addition", each List.NonNullCount({[Acre1], [Acre2], [Acre3], [Acre4], [Acre5]}), Int64.Type)
in
    #"Inserted Sum"

Regards

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.