Forum Discussion
Solved
Count distinct on a column
I feel this should be easier than I'm finding it. I have an Excel workbook containing 5 tabs of related data that I want to build some report templates with. I have used Power Query Editor to merg...
Have a look at this sample and see if it will work for you...
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0MgaRBkqxOtFKTnABI1OwgDNcAMIH6TAxNQOSxnANEL45XD2Eb2gO12BuYYmiAcJHWADhA9XHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, StoreCode = _t, Quantity = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"StoreCode", Int64.Type}, {"Quantity", Int64.Type}}), distinctStoreCount = List.Count(List.Distinct(#"Changed Type"[StoreCode])), addStoreCount = Table.AddColumn(#"Changed Type", "StoreCount", each distinctStoreCount) in addStoreCount
Have a look at this sample and see if it will work for you...
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0MgaRBkqxOtFKTnABI1OwgDNcAMIH6TAxNQOSxnANEL45XD2Eb2gO12BuYYmiAcJHWADhA9XHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, StoreCode = _t, Quantity = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"StoreCode", Int64.Type}, {"Quantity", Int64.Type}}),
distinctStoreCount =
List.Count(List.Distinct(#"Changed Type"[StoreCode])),
addStoreCount =
Table.AddColumn(#"Changed Type", "StoreCount", each distinctStoreCount)
in
addStoreCount
Basically you are creating a line in the query the counts the distinct of the StoreCode column and then adding column to the original table that refers to that distinct count.