Forum Discussion
AddIndexColumn based on unique values in columns
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
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})
6 Replies
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})
That's it, it works perfectly when i add this step. Thank you very much
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
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"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
