Expression for column that lists empty columns

[Mbolamu]

I have two tables: Table 1 & Table 2.

Table 2 has 4 columns :

Column: A 

Column: B

Column: C

Column: D

Id like to create an expression in Table 1 that lists that name of the columns in table 2 that are empty within that row.

In the example table below, “XXXX” represents the data. The column in table 1 shows the names of the columns in table 2 with no data in that row. 

 

I am a novice and haven it a good crack with the following expression:

LIST(
IF(
AND(
CONTAINS(
Table2[ROW ID],[_THISROW].[ROW ID]), TRUE,
ISBLANK(
Table1[A]), TRUE), “ A”, “”),

IF( 

AND(
CONTAINS(
Table2[ROW ID],[_THISROW].[ROW ID]), TRUE,
ISBLANK(
Table1[B]), TRUE), “ B”, “”),

IF(
AND(
CONTAINS(
Table2[ROW ID],[_THISROW].[ROW ID]), TRUE,
ISBLANK(
Table1[C]), TRUE), “ C”, “”),

IF(
AND(
CONTAINS(
Table2[ROW ID],[_THISROW].[ROW ID]), TRUE,
ISBLANK(
Table1[D]), TRUE), “ D”, “”))

Any help would be much appreciated!

[dbaum]

It's unclear whether you've tried your expression and, if so, what problem you observed.

Regardless, without having tried to parse your expression, here's an approach to consider:

• Ensure Table 1 references Table 2.
• Depending on the direction of your intertable reference, use an expression like one of the following to evaluate whether a Table 2 column is blank.

ISBLANK([Table 2 ID].[A])
ISBLANK([Related Table 2s][A])

[Mbolamu]

thanks for replying!

the tables are referenced but by a different column. My example was a condensed and redacted version of what I’m trying to achieve but I appreciate how hard it is to give guidance without the full picture so my bad.
The expression works and has a green tick in the expression checker but I know this doesn’t always mean it will have the right results. Like I said I’m a novice so I’m working on common sense and YouTube videos. From what I’ve learnt, my understanding is that the IF( statements need to be connected with a AND or OR like in the picture attached?

I could be completely wrong, I’ve tried to add both and the expression strops working because is says the expression needs to have a true or false value. So I’m a little stuck..

The tables don’t have any data yet and I’m not sure how to check that it would actually return the right results. But I’d like it to display the values like in my example table with commas as separators. With my limited knowledge and lack of data it’s hard to know if the e expression would produce that.

I think this might be one of those things that’s I just wing it for now and come back to once I’m further along. But if you can make sense of my layman’s babble, any help you can give would be much appreciated!

[Marc_Dillon]

No, you don't need and/or between the list items. You just need an appropriate expression for each list item. Like:

IFS( 
  ISBLANK( LOOKUP( [_THISROW].[row id] , table 2 , row id , a) ,
  "A"
)

[Mbolamu]

when I replace “table 2” with the table name , it says “expression cannot be parsed due to unrecognisable token “table 2”

which is strange because I’ve never seen that error before.  So I have no idea what it means or how to fix it.

I tried this expression on another table to see if it would work and it says that “IBLANK” is used incorrectly. I’m out of my depth I think..

 Will the original expression that I posted successfully create a list separated by commas if I don’t add AND( between the ifs?

if so I just might keep it

[Steve]

(
  LIST(
    IFS(ISBLANK(LOOKUP([_THISROW].[Row Number], "Table 2", "Row Number", "A")), "A"),
    IFS(ISBLANK(LOOKUP([_THISROW].[Row Number], "Table 2", "Row Number", "B")), "B"),
    IFS(ISBLANK(LOOKUP([_THISROW].[Row Number], "Table 2", "Row Number", "C")), "C"),
    IFS(ISBLANK(LOOKUP([_THISROW].[Row Number], "Table 2", "Row Number", "D")), "D"),
  )
  - LIST("")
)

[Mbolamu · Accepted solution]

LIST(
IF(
AND(
CONTAINS(
Index[Lecture ID],[_THISROW].[Lecture ID]),
ISBLANK(
Index[Paraphrase Lecture notes])), “ Paraphrase Lecture notes”, “”),

IF(
AND(
CONTAINS(
Index[Lecture ID],[_THISROW].[Lecture ID]),
ISBLANK(
Index[Lecture notes reference source])), “ Lecture notes reference source”, “”),

IF(
AND(
CONTAINS(
Index[Lecture ID],[_THISROW].[Lecture ID]),
ISBLANK(
Index[Lecture notes Condensed point])), “ Lecture notes Condensed point”, “”),

IF(
AND(
CONTAINS(
Index[Lecture ID],[_THISROW].[Lecture ID]), TRUE,
ISNOTBLANK(
Index[My notes]),
ISBLANK(
Index[Personal Notes Reference source])),“ Personal Notes Reference source”, “”),

IF(
AND(
CONTAINS(
Index[Lecture ID],[_THISROW].[Lecture ID]), TRUE,
ISNOTBLANK(
Index[My notes]),
ISBLANK(
Index[Personal Notes Condensed point])),“ Personal Notes Condensed point”, “”),

IF(
AND(
CONTAINS(
Index[Lecture ID],[_THISROW].[Lecture ID]),
ISBLANK(
Index[Research notes])), “ Research notes”, “”),

IF(
AND(
CONTAINS(
Index[Lecture ID],[_THISROW].[Lecture ID]),
ISBLANK(
Index[Paraphrase Research notes])), “ Paraphrase Research notes”, “”),

IF(
AND(
CONTAINS(
Index[Lecture ID],[_THISROW].[Lecture ID]),
ISBLANK(
Index[Research notes Reference source])), “ Research notes Reference source”, “”),

IF(
AND(
CONTAINS(
Index[Lecture ID],[_THISROW].[Lecture ID]),
ISBLANK(
Index[Research notes Condensed point])), “ Research notes Condensed point”, “”),

IF(
AND(
CONTAINS(
Index[Lecture ID],[_THISROW].[Lecture ID]),
ISBLANK(
Index[Statistics notes])), “ Statistics notes”, “”),

IF(
AND(
CONTAINS(
Index[Lecture ID],[_THISROW].[Lecture ID]),
ISBLANK(
Index[Statistics notes])), “ Statistics notes”, “”),

IF(
AND(
CONTAINS(
Index[Lecture ID],[_THISROW].[Lecture ID]),
ISBLANK(
Index[Paraphrase Statistics notes])), “Paraphrase Statistics notes”, “”),

IF(
AND(
CONTAINS(
Index[Lecture ID],[_THISROW].[Lecture ID]),
ISBLANK(
Index[Statistics notes Reference source])), “ Statistics notes Reference source”, “”),

IF(
AND(
CONTAINS(
Index[Lecture ID],[_THISROW].[Lecture ID]),
ISBLANK(
Index[Statistics notes Condensed point])), “ Statistics notes Condensed point”, “”),

IF(
AND(
CONTAINS(
Index[Lecture ID],[_THISROW].[Lecture ID]),
ISBLANK(
Index[Informed opinion])), “ Informed opinion”, “”),

IF(
AND(
CONTAINS(
Index[Lecture ID],[_THISROW].[Lecture ID]),
ISBLANK(
Index[Informed opinion reference source])), “ Informed opinion reference source”, “”),

IF(
AND(
CONTAINS(
Index[Lecture ID],[_THISROW].[Lecture ID]),
ISBLANK(
Index[Informed opinion Condensed point])), “ Informed opinion Condensed point”, “”),

)