Forum Discussion

keenanto's avatar
keenanto
Regular Visitor
4 years ago
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...
Need Help
  • wdx223_Daniel's avatar
    wdx223_Daniel
    4 years ago

    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's avatar
keenanto
Regular Visitor
4 years ago

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's avatar
jbwtp
Memorable Member
4 years ago

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