Power Bi String Manipulation gives ambiguous results

[cdvipul]

I am new to Power BI, tryin to create new columns using the standard excel LEN and FIND formulae to extract date from a filename string.

The results I get are not inline with what actually the length (barring few) is, nor the location given by FIND matches the actual location within the string.

It kind of results in multiples of the original length when the prefix/format of the filename is the same after a few records as we go down the list.

What am I doing incorrect? Is new column not the method to use string manipulation functions?

1. LEN = LEN(TRIM('GCS TLOG'[filename]))

2. FIND = FIND(".",TRIM('GCS TLOG'[filename]),1)

[cdvipul · Accepted solution]

@v-kkf-msft  Thanks, this was resolved, had to select the 'Dont Summarize' option in the VIsualization - Columns Section. Sum had gotten selected automatically 

[cdvipul]

Thank you for your response @amitchandak , i did what you asked, issue still exists.

That is what i find weird , there seems to be no issue with raw data, its coming from a production BQ table. Also, i have exported the dataset into excel and applied the formulae and they work fine.

Created a new columns 

Column = "----"&'GCS TLOG'[filename] & "----"

Applied CLEAN and TRIM, still the same issue.

Also, the increment has a pattern 35, 35*2 (70) for next set of records, 35*3 (105),  140. 175 .. changing after a few set of records.

[v-kkf-msft]

Hi @cdvipul ,

Please check if there is zero width space like Character.FromNumber(8203) in your text. As shown below, it has no width, but will be counted into the text length.

You can use the Text.ToList function to create custom column to check for the presence of invisible characters, like this.

= Text.ToList( [TextColUnique] )

Then you can create the custom column to remove Character.FromNumber(8203) in Power Query.

= Text.Remove([TextColUnique],Character.FromNumber(8203))

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@cdvipul , the columns seem fine, there can be special character in name, use clean in power query

Power Query Trim and Clean : https://youtu.be/NRYPsCnS0w4

To check if there is something at the end create a new column

TRIM('GCS TLOG'[filename]] & "---"