M - "DataFormat.Error: We couldn't convert to Number. Details: undefined"

[charleshale]

I have a plain vanilla table of 2 text columns that I am trying to group (group 2nd column by first, show all rows.

I am getting the dreaded "DataFormat.Error: We couldn't convert to Number. Details: undefined".   In accordance with the posts on this forum about the issue, I've cleaned the text, trimmed it, converted back and forth between numbers and text, removed errors, and, in short, I've tried every cleanse I can think of but still must have some bad data somewhere that is causing the error and that PowerBI can't remove.   Any suggestions?   WWID  (What would @ImkeF do 🙂 ?  )

Thank you

Charlie

[ImkeF · Accepted solution]

Ha, ha @charleshale  😉

I would add a column that tells me if there is more than just numbers in that column like so:

Text.Length(Text.Remove([PID], {"0".."9"})) > 0

Then filter on this new column for "true".
... Re-reading your request:
Are you saying that while you're trying convert to TEXT, you're getting an error complaining that the conversion to NUMBER has failed?
This would be an indication that a conversion in a previous step is causing it.
So you might want to implement that step before that number-conversion step that migh sit somewhere in previous steps.

[charleshale]

Aha!  A helper column essentially.  Genius solution!