Forum Discussion

joshua1990's avatar
joshua1990
Post Prodigy
3 years ago

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?

 

 

Need Help

2 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    3 years ago

    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

     

    • joshua1990's avatar
      joshua1990
      Post Prodigy
      3 years ago

      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?