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 can do it in regular excel for two Columns with the formula: = (A1&B1)="" but I want to see if I can do this all in Power Query.
This is one of the MANY codes I've tried to run to allow a column to show True/False if all of the columns have nulls... Only Problem is it shows as a List and I'm not sure how to change that without making another Query? This only shows 2 columns but I want it to grab from a range (Something else I am struggling to figure out)
= Table.AddColumn(#"Renamed Columns", "TF", each {if (([#"TEST1"]&[#"TEST5"])=null) then true else null=false})
Essentially my data is for tests to ensure my products are up to par. It's rare but there are times we have to test products/tweak them more than once in order to ensure we get the right results. I figured if I could make a T/F column I could filter out the Falses (all rows containing only nulls) and use True if there is even one test in the multiple columns that may need to be ran more than once in order to prevent any loss of data. Only other issue is for this data set, the first 3 columns are for the batch number, date is was completed, and how many times a test needed to be ran to get desired results.
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) )
7 Replies
Hello, Anonymous
Table.AddColumn( #"Renamed Columns", "TF", (x) => List.MatchesAll(Record.FieldValues(x), (y) => y = null) )Hello AlienSx
I entered in the formula exactly how you had it and also tried to add the first column of the range in for (y) and the last for the y but it is showing Falses even for rows that have data in every column unfortunately.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?
AlienSx