Count and sum at row level

[Anonymous]

Hi,

Sorry, I am NOT a dax type person and am going crazy with sum, count etc.....

As per excel demo, I just want to count per row and get the result like in excel....... Any help would be welcomed!

[v-jingzhang · Accepted solution]

Hi @Anonymous 

Here is a method with Power Query, not DAX. You can add a custom column with below code. Hope it helps. 

List.NonNullCount(Record.ToList(_))

For sum, you can replace List.NonNullCount with List.Sum.

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Anonymous]

Thanks very much, thats opended my eyes to some new logic..... However, could you outline the purpose of the _ in the brackets? i.e. I can generally follow a query/formula, but this one has got me guessing? The formula worked, but just embedding it further with a slight tweak on this building block (to the data). Can the same logic be used to exlcude the first column master?

i.e. this is essentially a realtional merge, where MASTER is the primany key, with 3 tables linked to it.

See above, I just added a "-1" in brackets and it removes the count of one I want to exlclude (its a constant, so no issues for me)

[v-jingzhang]

Hi @Anonymous 

Use this

List.NonNullCount(Record.ToList(Record.RemoveFields(_,{"master"})))

Best Regards,
Community Support Team _ Jing

[v-jingzhang]

Hi @Anonymous 

Well, "_" is a magic in Power Query I think. I don't know how to explain it clearly. It represents each expression in its environment. So it can be any type of value according to where it is. 

In the current sample, "_" represents every row. You can add a custom column with only "_". Just like below image. "_" is a record which contains all pairs of column name & column value in the current row. 

There is a simple explanation of "_" in Simplified declarations section in this official document  M Language Functions - PowerQuery M. However it is too simple to make it clear enough to new users. You need to test it more in different kinds of functions to learn about it.

For example, when you transform Age column to add 1 to it, you will find "_" appear in the auto-generated step. Here it represents the value in Age column on each row. 

Try more, you will find Power Query interesting!

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.