Table.Group and map a given header or column name to a given value;

[Dicken]

 
Hi, I was ttryin to map give record headers to certain values,     a sort of  if  cat then "a", dog "b"   etc.    
to do that I have come up with two methods, and even thought happy with these I have tried using 
list.zip and accumulate,   so these are the two I havve but if anyone would like to suggest other functions or approaches I would 
be interested ; 

[
  tab = #table(
    type table [Item = Text.Type, Unit = Text.Type], 
    {{"A", "cat"}, {"A", "dog"}, {"A", "horse"}}
  ), 
   header = {"One", "Two", "Three", "Four", "Five"}, 
  avalue = {"cat", "horse", "elephant", "dog", "cow"}, 
  inverse = Record.FromList( header, avalue), 
  result = Table.Group(
    tab, 
    {"Item"}, 
    {{"N", each Record.FromList([Unit], List.Transform([Unit], (x) => Record.Field(inverse, x)))}}
  )
]

[
    tab = #table(
      type table [Item = Text.Type, Unit = Text.Type],
      {{"A", "cat"}, {"A", "dog"}, {"A", "horse"}}
    ),
    recs = {"One", "Two", "Three", "Four", "Five"},
    avalue = {"cat", "horse", "elephant", "dog", "cow"},
    lookuptab = Table.FromColumns({recs} & {avalue}, {"rec", "val"}),
    result = Table.Group(
      Table.AddColumn(tab, "N", each lookuptab{[val = [Unit]]}[rec]),
      {"Item"},
      {{"t", each Record.FromList([Unit], [N])}}
    )
  ]

     Richard 

[Dicken]

thanks , have not had a chance to go through it  yet but will. 

Richard. 

[lbendlin · Accepted solution]

let
  
    header = {"One", "Two", "Three", "Four", "Five"},
    avalue = {"cat", "horse", "elephant", "dog", "cow"},
    tab = #table(
    type table [Item = Text.Type, Unit = Text.Type], 
    {{"A", "cat"}, {"A", "dog"}, {"A", "horse"}}
  ),
    #"Added Custom" = Table.AddColumn(tab, "Name", each header{List.PositionOf(avalue,[Unit])}),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Item"}, {{"R", each Record.FromTable(Table.RenameColumns(_,{"Unit","Value"})) }})
in
    #"Grouped Rows"