试试这个

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Grouped Rows" = Table.Group(Source, {"letters"}, {{"data", each Table.AddIndexColumn(_, "Index", 0, 1, Int64.Type), type table }}),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"group a", "Index"}, {"group a", "Index"}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded data", {{"Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Expanded data", {{"Index", type text}}, "en-US")[Index]), "Index", "group a", List.Sum)
in  #"Pivoted Column"

上面的技巧是右键单击字母列，按以下方式分组，使用操作所有行
然后在编辑栏中替换

each _, type table [letters=text, group a=number]}})

与

each Table.AddIndexColumn(_, "Index", 0, 1, Int64.Type), type table }})

使用列顶部的箭头展开列，选择字母列，然后透视