Forum Discussion
Solved
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 bran...
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})
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