Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
CJ3k
New Member

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.

1 ACCEPTED SOLUTION

@CJ3k 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)
)

View solution in original post

7 REPLIES 7
CJ3k
New Member

Here is an example I want of my data/what I want it to do if this helps anyone.

BatchDateRepetition of TestsTest1Test2Test3Test4Test5Test6True/False

100

8/20/23

150.555.855.800.50.88True
1008/20/232 55.8    True
1008/20/233      False
1008/20/234      False
1008/20/235      False
1019/05/23150.455.855.800.40.86True
1019/05/232      False
1019/05/233      False
1019/05/234      False
1019/05/235      False

 

@CJ3k 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 

@CJ3k 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. 

Nevermind. I just deleted the Column and it kept the formatting. 
Thanks for the help!

CJ3k
New Member

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.

AlienSx
Super User
Super User

Hello, @CJ3k 

Table.AddColumn(
  #"Renamed Columns",
  "TF",
  (x) => List.MatchesAll(Record.FieldValues(x), (y) => y = null)
)

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors