Forum Discussion
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 dist...
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?