AddIndexColumn based on unique values in columns

[keenanto]

Hi All

I have the following table and I want to add a new column which counts the number of different car models that a salespersons sells in each branch. So Tom sells only Ford in the New York branch so the unique car count is 1. Joan sells Ford so her unique car count is 1 and after she sells Toyota, the unique car count changes to 2 and remains at 2 until another unique car is sold. The count will then revert to 1 when moved on to the next salesperson or the next State. 

State	SalesPerson	Car	DateTime	UniqueCarCount
New York	Tom	Ford	20/07/2022 07:07:36	1
New York	Tom	Ford	20/07/2022 08:58:28	1
New York	Tom	Ford	20/07/2022 09:56:08	1
New York	Tom	Ford	20/07/2022 10:57:56	1
New York	Joan	Ford	20/07/2022 07:07:36	1
New York	Joan	Toyota	20/07/2022 08:30:38	2
New York	Joan	Ford	20/07/2022 08:58:28	2
New York	Joan	Ford	20/07/2022 09:56:08	2
New York	Joan	Toyota	20/07/2022 10:57:56	2
New York	Steve	Toyota	20/07/2022 08:58:28	1
New York	Steve	Ford	20/07/2022 09:56:08	2
New York	Steve	Renault	20/07/2022 10:57:56	3
Florida	Tom	Toyota	20/07/2022 09:56:08	1
Florida	Tom	Toyota	20/07/2022 10:57:56	1

I've tried adding a new custom column with the AddIndexColumn but I cannot get the logic right to get the result I want. If anyone has any ideas, I'd really like to hear them! 

Thanks 

[wdx223_Daniel · Accepted solution]

NewStep= Table.FromRecords(List.Accumulate(Table.ToRecords(PreviousStepName),{{},[]},(x,y)=>let a=Text.Format("#[State]-#[SalesPerson]",y),b=Record.TransformFields(x{1},{a,each List.Distinct((_??{})&{y[Car]})},2) in {x{0}&{y&[UniqueCarCount=List.NonNullCount(Record.Field(b,a))]},b}){0})

[keenanto]

That's it, it works perfectly when i add this step. Thank you very much

[keenanto]

Thank you for the quick reply Artur. 

I pasted this code below into advanced editor. It appears to be fine in Advanced Editor with no Syntax errors detected. But when i click ok and it brings me back out to the Power Query Editor, I get this error...

Expression.Error: The import PromotedHeaders matches no exports. Did you miss a module reference?

let
    Source = Excel.CurrentWorkbook(){[Name="Table14"]}[Content],
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
     BuffZipList1 = List.Buffer(List.Zip({PromotedHeaders[State],PromotedHeaders[SalesPerson],PromotedHeaders[Car]})),
  #"Added Custom" = Table.AddColumn(PromotedHeaders, "UniqueCarsSold", each List.Count(List.Distinct(List.Transform(List.Select(BuffZipList1, (x)=>x{0}=[State] and x{1}=[SalesPerson]), each _{2}))))

in
    #"Added Custom"

[jbwtp]

Hi all,

I think this is down to taste preferences, but I would prefer using GroupBy, theoretically this would be less scans through the dataset, but I did not test it.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8kstV4jML8pW0lEKyc8Fkm75RSlAyshA38Bc38jAyEjBwNwKiIzNgKIKUGCoFKtDnF4LK1MLKyMLsvRaWpmaWRmQpdfQwMrUHKgdn16v/MQ8sj0M1RySX5lfkojhZ2MDK2NkdxsRbzdmgJGgGTPEcGnG5nAsgYahPbgktSwVt8cJRjZMP3mOh+kOSs1LLM0pIeh8Y7ABbjn5RZkpifDkgtXtuBIbMZqxp7ZYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [State = _t, SalesPerson = _t, Car = _t, DateTime = _t, UniqueCarCount = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"State", type text}, {"SalesPerson", type text}, {"Car", type text}, {"DateTime", type datetime}, {"UniqueCarCount", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"State", "SalesPerson"}, {{"Data", each _, Value.Type(#"Changed Type")}, {"Count", each List.Count(List.Distinct(_[Car])), Int64.Type}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Car", "DateTime", "UniqueCarCount"}, {"Car", "DateTime", "UniqueCarCount"})
in
    #"Expanded Data"

Kind regards,

John

[jbwtp]

Hi @keenanto,

in line 2 you have a step called #"Promoted Headers". But in line 3 you refer to it as PromotedHeaders. Change it in either place to match. It should resolve the problem.

Cheers,

John  

[artpil]

Hi,

Here's the code that counts distinct cars by sales person in a state.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZHNCoMwEIRfRXIWTCP+NNeKhx5KqV6KeAi4B6m6ENOWvn33UNtSjaQQNiSZj5khVcUKowwwnxWqg/EIesSBTjulaWb0VLY9sNqv2AHu3hn1he5L7GnmqBvaBA94EgguhMcTSSuMnfWpjFIpUmf9Vkax5M76DZdRQsivfo9q+KvACyjxgUbNOoRchrNMdg9LaTtgab0Syla8MHADe5HFXBPjHmwiTjCoa2dWouUd6rZR7y9czPXt4gJ8HOon", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    BuffZipList1 = List.Buffer(List.Zip({PromotedHeaders[State],PromotedHeaders[SalesPerson],PromotedHeaders[Car]})),
    #"Added Custom" = Table.AddColumn(PromotedHeaders, "UniqueCarsSold", each List.Count(List.Distinct(List.Transform(List.Select(BuffZipList1, (x)=>x{0}=[State] and x{1}=[SalesPerson]), each _{2}))))
in
    #"Added Custom"

Paste the code to advanced editor.

What the code does:

in step BuffZipList1 list of lists based on columns State, SalesPerson and Car is being created,

in step #"Added Custom" first is filtered ( List.Select statement), then list is truncated to unique values and finally items are being counted.

Hope this helps.

Artur