Forum Discussion
Identify if string is made if of numeric values
Hi
I am trying to identify on my report, using PowerQuery, if a purchadse order number; the column is declared as a text value, had/is all nuermic values. A number of Purchase orders numbers are prefixed with a letter(s). I wanted to flag these as they are a different type of order.
I am using a couple of snippets taken from the web and came up with this:
=if
List.AllTrue({
Text.Length([PurchaseOrder])=7,
Value.Is(
Number.From([PurchaseOrder])
)
})
then "YES"
else "NO"
So I am checking if a purchase order text lenght = 7 chatacters, then I want to check if they are all nuermic. Its seems to partially work, ie its understands if it is all numeric, its just when it encounters a text character if fails and throughs up an error for thoses rows.
I asusmed adding this to an IF statement the outcomes would be false and I get my answer.
Chris.
One way to do it is to add a custom column with this formula, which will return True for number rows and False for ones with letters.
=Value.Is(Value.FromText([Column1]), type number)
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
2 Replies
One way to do it is to add a custom column with this formula, which will return True for number rows and False for ones with letters.
=Value.Is(Value.FromText([Column1]), type number)
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat