Add a column with rowwise calculation

[tt211595]

Hello! I'd like to use M code to add a new column to a query. For each row, I'd like the new column to show how many distinct values are in the row (across multiple columns).

For example, if I have this query with three columns:

Column1	Column2	Column3
A	NULL	NULL
B	C	D
E	E	F

I'd like to add NewColumn, which does a distinct count for each row in the query:

ID1	ID2	ID3	NewColumn
A	A	A	1
B	C	D	3
E	E	F	2

Thanks in advance!

[Ashish_Mathur]

Hi,

This M code works as well

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each List.Count(List.Distinct(List.Select(Record.ToList(_), each _<>null))))
in
    #"Added Custom"

Hope this helps.

[kameronyork · Accepted solution]

This can be accomplished by creating a list within a function in Power Query. 

The following image shows the outcome you are looking for:

This is the function I used to acheive this solution:

This can be done in multiple steps or just one step as seen above.  The process is as follows:
1. Create a list using the Record.ToList function.  Inside brackets you will identify what columns will pass their values to the list.
2. Use the List.Distinct function to remove duplicate values from the list.  Nulls will remain, but the following step will ignore them.
3. Use the List.NonNullCount function to return the count of values in the list that are not Null.

I hope this helps!  If it has, please mark this reponse as the solution 🙂

[tt211595]

Thanks very much!

[Ahmedx]

plse try this

List.Count(
 List.Distinct(
List.Select(
Record.FieldValues(_),each _<> "" and _<> null)))

[tt211595]

This worked, too. Thank you!