Forum Discussion
Solved
Array formula returning #VALUE when using large text-based field
Working on a report for a customer. Basic issue is data coming through on multiple rows and customer wants consolidated on one row. My reporting tool doesn't allow me to consolidate to one row as t...
The formula could be
=TEXTJOIN(CHAR(10),TRUE,LEFT(AdhocTable[Immigration Status Update.Comments],IF([@[Assignment ID]]=AdhocTable[Assignment ID],LEN(AdhocTable[Immigration Status Update.Comments]),0)))
entered as array one (Ctrl+Shift+Enter). I take CHAR(10) (new line) as separator, you may change on any other symbol or text.
Please check attached.
SergeiBaklan Sorry for the slow reply. I haven't quite figured out how to use your advice. I've attached a sample file. I messed with it quite a bit. Not an expert here.
The file has 3 cases. One is working. One doesn't error, but returns 0's and I don't want that. The final case shows the long field and it #ing out.
Would love for you to take a peak and help me along!
The formula could be
=TEXTJOIN(CHAR(10),TRUE,LEFT(AdhocTable[Immigration Status Update.Comments],IF([@[Assignment ID]]=AdhocTable[Assignment ID],LEN(AdhocTable[Immigration Status Update.Comments]),0)))
entered as array one (Ctrl+Shift+Enter). I take CHAR(10) (new line) as separator, you may change on any other symbol or text.
Please check attached.
SergeiBaklan Brilliant!