Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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?
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?
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!