Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
joshua1990
Post Prodigy
Post Prodigy

Count Distinct Values in each Column

Hi all!

I have a table with a lot of columns.

Some of these columns are blank and sometimes there is even just 1 value.

Now, how is it possible in PQ to count for each column the number of distinct values?

I know, we can do it via "Group By", but here I would need to select each individual column. There are like 150 columns.

Any workaround here?

 

 

2 REPLIES 2
Vijay_A_Verma
Super User
Super User

See a sample code here

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIEYmMgNlOK1UEVMQCLJAFZRlARE7iIMRQbKcXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    Custom1 = Table.FromColumns({Table.ColumnNames(Source)} & {List.Transform(Table.ToColumns(Source), (x)=>List.Count(List.Distinct(List.RemoveNulls(x))))}, {"Column Names", "Distinct Count"})
in
    Custom1

 

@Vijay_A_Verma This is awesome! Thanks a lot. Is it possible to convert this into a function? So that I can add this step much more esier into every query?

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.