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})
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"
