Will Unique include the null's count in column profiling in Power Query Editor?

[Gomathivalli]

Just have a question on column profiling in Power Query editor

Number(Whole num) Text(Text Format)

1 B

3 C

0 B

4

null A

2 C

5 A

This is my table and column profiling shows as follows
Number

Count7 Count7 Error0 Empty1 Distinct7 Unique7 Empty string1 Min Max5
Text

Count7 Count7 Error0 Empty1 Distinct4 Unique1 Empty string1 Min Max C

The nulls i used in my table has 0 as length so i could confirm i used nulls in the table.

As a result nulls are included in both distinct and unique when i checked but the documentation and sources online state that unique doesnt include nulls. Any clarifications would be appreciated.

[Olufemi7 · Accepted solution]

Hello @Gomathivalli,

In Power Query Editor (both in Power BI Desktop and Microsoft Fabric Dataflows), column profiling does include nulls in the Distinct and Unique counts. Nulls are treated as valid values during profiling, even though they are also reported separately under Empty.

How profiling metrics work:

• Count → Total rows, including nulls.
• Distinct → Number of different values, including null.
• Unique → Number of values that occur only once, including null if it appears once.
• Empty → Number of nulls.
• Empty String → Number of “” values (different from null).

Example:
For a numeric column with values 1, 3, 0, 4, null, 2, 5 → Distinct = 7, Unique = 7, Empty = 1.
For a text column with values A, B, C, null, B, C, A → Distinct = 4, Unique = 1, Empty = 1.

Power BI Desktop: Nulls are included in Distinct and Unique counts. Profiling pane shows Empty separately to distinguish nulls.
Microsoft Fabric: Uses the same Power Query engine as Power BI. Profiling results are identical: nulls are included in Distinct and Unique counts.

Microsoft documentation:

• Data profiling tools in Power Query: `https://learn.microsoft.com/en-us/power-query/data-profiling-tools` 
• Table.Profile (Power Query M): `https://learn.microsoft.com/en-us/powerquery-m/table-profile` 

Summary: Nulls are always included in Distinct and Unique counts in Power Query Editor for both Power BI Desktop and Microsoft Fabric. If you want profiling without nulls, you must filter them out first (for example, using Remove Empty).

Power Query M example to exclude nulls before profiling:

let
Source = YourTable,
NoNulls = Table.SelectRows(Source, each [ColumnName] <> null),
Profile = Table.Profile(NoNulls)
in
Profile

This way, the profiling results will exclude nulls from Distinct and Unique counts in both Power BI and Fabric.

[v-aatheeque]

Hi @Gomathivalli 

Following up to confirm if the earlier responses addressed your query. If not, please share your questions and we’ll assist further.

[v-aatheeque]

Hi @Gomathivalli 

We wanted to follow up to check if you’ve had an opportunity to review the previous responses. If you require further assistance, please don’t hesitate to let us know.

[cengizhanarslan]

Power Query column profiling uses these rules:

1) Count

• Counts all rows, including nulls

2) Empty

• Counts null values (not empty strings)

3) Distinct

• Counts distinct values, and null is treated as a value

• Multiple nulls count as one distinct value

So in your data:

1, 3, 0, 4, null, 2, 5

→ Distinct = 7 (including null)

4) Unique (this is the key point)

Unique = values that appear exactly once in the column

That means:

• If a value appears once, it is counted as unique

• If null appears once, it is also considered unique

• If null appears more than once, it is not unique

Power Query does not exclude nulls from uniqueness checks.