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 merge all of the data into a usable format however there are some additions I'd like to make, one of which I've been struggling with for a few weeks.

 

The data I'm working with contains a Store Code and this store code appears for each product record in the file (76) in this case and 25 stores, giving 1900 rows in the table.  I need to add a column where, for each row, the total number of stores is held (25), I cant 'hard code' it because I need to make it refreshable. I've tried List.count, List.distinct, various group by options, but nothing seems to get me where i need.

 

Any help would be brilliant.

Thanks

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

6 Replies

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

      Hi jgeddes , thanks for the reply.

      What mechanism would this use, Add Column of some other?

      • jgeddes's avatar
        jgeddes
        Super User
        2 years ago

        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.

    • ScottF75's avatar
      ScottF75
      New Member
      2 years ago

      The line 

      List.Count(List.Distinct(#"Changed Type"[StoreCode])

      pointed me in the right direction and i was able to solve my issue, thank you. 

  • 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.