Count Distinct Values in each Column

[joshua1990]

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?

[Vijay_A_Verma]

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]

@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?