Downloads
Power Query MErrors and typesIntermediate

Value.Is

Tests whether a value is of a given type, such as number or text, and returns true or false.

Syntax
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

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

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.

Related functions

All Power Query M functions · View as Markdown · M basics