Forum Discussion
Will Unique include the null's count in column profiling in Power Query Editor?
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.
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.
4 Replies
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.
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.Hi Gomathivalli
Following up to confirm if the earlier responses addressed your query. If not, please share your questions and we’ll assist further.
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.
