Forum Discussion

cdvipul's avatar
cdvipul
Regular Visitor
4 years ago
Solved

Power Bi String Manipulation gives ambiguous results

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 lengt...
Need Help
  • cdvipul's avatar
    cdvipul
    4 years ago

    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's avatar
cdvipul
Regular Visitor
4 years ago

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's avatar
    v-kkf-msft
    Community Support
    4 years ago

    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.