Forum Discussion
Solved
Add a column with rowwise calculation
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 ha...
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 🙂
