Forum Discussion

ScottF75's avatar
ScottF75
New Member
2 years ago
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...
Need Help
  • jgeddes's avatar
    jgeddes
    2 years ago

    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
jgeddes's avatar
jgeddes
Super User
2 years ago

No problem. I started with a table that looks like...

From there I add a line in Advanced Editor of the query that counts the distinct items in the StoreCode column. 
This is the List.Count(List.Distinct(#"ChangedType"[StoreCode])) line. In this line #"ChangedType is the previous step in the query and [StoreCode] is the column that contains the store codes.
After that I add a column to the original table refering to the #"ChangedType" step which adds the distinct store count to each line.
Table.AddColumn(#"ChangedType", "StoreCount", each distinctStoreCount) where #"ChangedType" is the step before the distinct count step and distinctStoreCount is the name of the step that gets the distinct store count.

 

I hope this clarifies the process I used. Please feel free to send me a private message if you wish.