ISSUE:
Trim and Clean not working to remove hidden space/row from SharePoint list data set.
IMPACT:
The extracted text doesn't correctly alpha filter. Cells with the hidden space/row will present above intended extract cells.
Example:
| Title | Quantity |
| Example A | 1 |
| Example C | 1 |
| Example A | 1 |
| Example B | 3 |
| Example C | 7 |
NOTE: Data set has been pulled from a SharePoint list. Deleting blanks contents from the applicapble SharePointfield will correct the Extracted data, however i would like Power BI to fulfill via query to save manual corrections.
Example of raw impacted data field. + Example A
<div class="ExternalClassEC0B3A6B0798419A9A35C5646E0D5F15"><html>
<p>Example A<br></p>
</html></div> |
Example of raw workable data field = Example B
<div class="ExternalClassF70CB82314354D9BADA5F784FF923F9D"><html>
<p>Example B<br></p>
</html></div> |
What I tried in Transform data (simplified version)
1. 'Clean' relevant column (Result is that it pulls it into 1 cell row)
2. Extract text before delimiter: <br>
3. Extract Text after delimiter; Starting from end: >
4. 'Trim' relevant column
5. 'Clean' relevant column just for good measure
6. Results Example A, Example B ect. However if Example B has a hidden space/row it will appear first in the filtering.
PLEASE HELP!!!!!!!!!!!!!!!!!!!!!!!!!
