Forum Discussion

Dicken's avatar
Dicken
Icon for Post Prodigy rankPost Prodigy
1 year ago
Solved

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

  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 ...
Show and Tell
  • lbendlin's avatar
    lbendlin
    1 year ago
    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"
lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
1 year ago
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"