Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
As a column it is simply:
Column = LEN([Comment])
As a measure:
Measure 5 = LEN(MAX([Comment]))
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?
As a column it is simply:
Column = LEN([Comment])
As a measure:
Measure 5 = LEN(MAX([Comment]))
Clearly I overthought things a little bit and this was a pretty simple solution. Thanks for the help.
Expression.Error: The name 'LEN' wasn't recognized. Make sure it's spelled correctly.
@tangutoori Make sure you are adding"New Column" in Data Pane but not in "Power Query Editor
Proud to be a PBI Community Champion
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 ?
HI THANKS FOR UR REPLY.
I HAVE A DOUBT.. CAN'T WE DO SAM THING IN A NEW COLUMN ?
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])