NULL

In the Ultra Transcenders books

DP-900DP-750DP-600DP-700PL-300DP-800

Each book explains NULL in context, with comparison tables and the common traps.

Related terms

• ANSI mode

  A stricter Spark SQL behaviour, enabled by default since Apache Spark 4.0 (so also in Fabric Runtime 2.0), where a bad cast or an integer overflow fails the query rather than quietly producing null. Functions such as try_cast, try_add and try_divide keep the old null result, while switching ANSI mode off should be treated as a temporary workaround.

• Append queries

  A Power Query step that combines two or more tables by placing their rows one after another, lining columns up by name and putting null where a column is absent. The plain option modifies the current query, whereas the 'as new' option produces a separate query and leaves the originals alone.

• BLANK

  DAX uses BLANK to mean no value at all. It resembles NULL in SQL but behaves more like an empty cell in Excel, so adding 20 to it returns 20; the BLANK function creates one and ISBLANK detects it.

• COALESCE

  Picks the first argument that is not NULL. In T-SQL loads this is often used to replace a missing lookup result with an Unknown member key such as -1.

• Data skipping

  Avoiding reads of files that cannot hold matching rows, using column statistics such as min, max and null counts gathered per file at write time. Liquid clustering and Z-ordering improve how much gets skipped.

• EVENTDATA

  Inside a logon or DDL trigger, this T-SQL function hands back XML about what fired it, including the command text, login, time and type of event. Anywhere else it simply gives NULL.

• Fill down

  In Power Query, copies the last non-null value above into each null cell below it (Fill up goes the other way). Empty strings aren't nulls, so convert them first or they stay as they are.

• ISBLANK

  Returns TRUE in DAX if a value is BLANK, which is roughly what NULL means in SQL.