Forum Discussion
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 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!
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 🙂
5 Replies
plse try this
List.Count( List.Distinct( List.Select( Record.FieldValues(_),each _<> "" and _<> null)))This worked, too. Thank you!
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 🙂Thanks very much!
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.
