Count the number of characters in a string without duplication

[dphillips]

I have a table which stores the LPResult for a number of LPHeadings for a student. The data also comes in with a comment on the student. Due to the way the table was created, the comment for each student is duplicated on each of the LPHeading row.

Student	LPHeading	LPResult	Comment	Number of Characters
John	A	5	Test	4
John	B	5	Test	4
John	C	4	Test	4
Sue	A	4	Good Student	12
Sue	B	4	Good Student	12
Sue	C	3	Good Student	12
Sue	D	4	Good Student	12
Sue	E	3	Good Student	12
Peter	A	5	Works hard	10
Peter	B	4	Works hard	10
Peter	C	5	Works hard	10
Peter	D	4	Works hard	10
Sam	A	5		0
Sam	B	4		0

Basically I want a simple count of the number of characters in the comment for each student. Ultimately, I want to check how many characters the comment is, with no comment returning 0. Above I have an example of a calculated column which counts the number of characters. Note due to the comment being duplicated, the count of characters is the same for each of the students' rows. How can I create a calculated column like this? Is a calculated column the best? Maybe a measure would be better? Could someone also tell me if this is better done using a measure where I can just have one row for each student with a measure which gives me a count of the number of  characters in their comment. Not sure how to do this due to the duplication of the comment over the rows for each student. Any help would be much appreciated. 

[Anonymous]

Hi everyone,

When I add the data, while it is normal on the SQL side, I encounter the character problem in BI Data and Report as below. How can we solve this situation?

[Greg_Deckler · Accepted solution]

As a column it is simply:

Column = LEN([Comment])

As a measure:

Measure 5 = LEN(MAX([Comment]))

[dphillips]

Clearly I overthought things a little bit and this was a pretty simple solution. Thanks for the help.

[tangutoori]

Expression.Error: The name 'LEN' wasn't recognized. Make sure it's spelled correctly.

[PattemManohar]

@tangutoori Make sure you are adding"New Column" in Data Pane but not in "Power Query Editor

[tangutoori]

yes... i noted it. Am a new user .Dont mind asking simple things. can u explain what is the difference between addig a colum in query and data pane ?

[tangutoori]

HI THANKS FOR UR REPLY.

I HAVE A DOUBT.. CAN'T WE DO SAM THING IN A NEW COLUMN ?

[Greg_Deckler]

The original formula I supplied was a DAX calculated column. If you want to do this in the Query Editor, then the formula for the column would be:

=Text.Length([Comment])