Value.Is
Tests whether a value is of a given type, such as number or text, and returns true or false.
Value.Is(value, type)Returns: A logical value, true or false.
What it does
Value.Is answers "is this value a number, text, date or some other type?" Use it to decide what to do with a value before you convert or total it. It does not convert anything. It only tests the type, which makes it a good guard in a column that mixes text and numbers.
Syntax and arguments
value: the value to test.type: the type to test against, written with the type keyword, such astype number,type text,type dateortype nullable number.
Example
An export column should hold amounts, but some cells are text such as "N/A". The query keeps the numbers and sets the text to null for review.
let
Cells = {1250, "N/A", 480.5, "75"},
Cleaned = List.Transform(Cells, each if Value.Is(_, type number) then _ else null),
Count = List.Count(List.RemoveNulls(Cleaned))
in
Cleaned
Cleaned is {1250, null, 480.5, null}. The text "75" is not a number, so the test is false and it becomes null. If you want to convert numeric text rather than discard it, use Number.FromText inside a try.
Example: a second, different use
Checking that a field holds a date before a date calculation: Value.Is(Row[DueDate], type date).
Things to watch
- A null is not a number.
Value.Is(null, type number)is false, whiletype nullable numberalso accepts null. - Text that looks like a number is still text. Value.Is will say false, so convert first if you want to keep it.
- Use it as a guard, not as a fix. Decide what happens to the values that fail.
- Count the failures. A large number of non-number cells usually means the export layout changed.
In business terms
Mixed-type exports are common in ERP and bank files, and a guard that finds the bad cells is much easier to review than a conversion error. Record how many cells failed the test.