Forum Discussion
Help with Formula Regarding 7 Columns Null/True/False
Hello! I have been trying to write a code in Power Query that allows me to have a custom column that comes back with either true or false If all of the columns selected in a row are nulls. I know I ...
Anonymous do you really have nulls? I've dropped first 3 columns:
Table.AddColumn( #"Renamed Columns", "TF", (x) => List.MatchesAll(List.Skip(Record.FieldValues(x), 3), (y) => y = null) )
Here is an example I want of my data/what I want it to do if this helps anyone.
| Batch | Date | Repetition of Tests | Test1 | Test2 | Test3 | Test4 | Test5 | Test6 | True/False |
100 | 8/20/23 | 1 | 50.5 | 55.8 | 55.8 | 0 | 0.5 | 0.88 | True |
| 100 | 8/20/23 | 2 | 55.8 | True | |||||
| 100 | 8/20/23 | 3 | False | ||||||
| 100 | 8/20/23 | 4 | False | ||||||
| 100 | 8/20/23 | 5 | False | ||||||
| 101 | 9/05/23 | 1 | 50.4 | 55.8 | 55.8 | 0 | 0.4 | 0.86 | True |
| 101 | 9/05/23 | 2 | False | ||||||
| 101 | 9/05/23 | 3 | False | ||||||
| 101 | 9/05/23 | 4 | False | ||||||
| 101 | 9/05/23 | 5 | False |
Anonymous do you really have nulls? I've dropped first 3 columns:
Table.AddColumn( #"Renamed Columns", "TF", (x) => List.MatchesAll(List.Skip(Record.FieldValues(x), 3), (y) => y = null) )I do have nulls. Entered this in and it worked.
Do you know if there is a way to hide this True/False column now that I can filter out the rows with all nulls?
AlienSxAnonymous now I don't think I am following you. You asked for this (TF) column to filter out rows with nulls while now you are asking to hide it to filter out nulls again? If your final goal was to remove rows with all Test_n = null then you don't really need TF column. Apply the same logic but use Table.SelectRows instead.