Count distinct on a column

[ScottF75]

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

[jgeddes]

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.

[jgeddes · Accepted solution]

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]

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. 

[ScottF75]

Hi @jgeddes , thanks for the reply.

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

[jgeddes]

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]

Hi @jgeddes, again, appreciate you taking the time to respond, it may be my limited experience and how I've interpreted and implemented your answer but unfortunately, I'm not getting the result I expected.