按组逐列的Power Query Sum作为新列 [英] Power Query Sum of column by group as new column

问题描述

因此，我刚开始使用Power查询，我只是浪费了一个多小时来寻找可以在许多其他程序中轻松完成的事情.

So I am new to power query and I just wasted over an hour looking for something that I can do easily in many other programs.

我只想创建一个新列，总结另一列.对于实例，检查百分比是否正确，然后将其标准化.我不想分组并缩小表.

I just want to create a new column summing up another column. FOr instance, to check if the percentage a correct and if not normalize therafter. I dont want to group by and reduce the table.

我一直在左右搜索并尝试添加一个新列，例如"Group Sum".使用类似的东西= list.sum([Number])=计算(SUM([Number])

I ve been searching left and right and tried to add a new column like "Group Sum" using stuff like = list.sum([Number]) = Calculate(SUM([Number])

只是获得所有条目200的总和.没有成功.

just to get the the total sum of all entries 200. No success.

也许是我，但是我真的看不到逻辑.

Maybe its me, but I really dont see the logic.

我现在尝试了

let
    Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
        #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Group", type text}, {"Gender", type text}, {"Number", Int64.Type}, {"Group Sum", Int64.Type}, {"Spalte1", Int64.Type}})
        #"Added Custom" = Table.AddColumn(#"Geänderter Typ","Group Sum",(i)=>List.Sum(Table.SelectRows(#"Geänderter Typ", each [Group]=i[Group])[Number]), type number )
    in
        #"Geänderter Typ"

这会导致错误，并且

let
    Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Group", type text}, {"Gender", type text}, {"Number", Int64.Type}, {"Group Sum", Int64.Type}}),
    #"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(#"Geänderter Typ", "Benutzerdefiniert", each Table.Group(Quelle, {"Group"}, {{"Group Sum", each List.Sum([Number]), type nullable number}}))
in
    #"Hinzugefügte benutzerdefinierte Spalte"

哪个给我一个新列，其中所有条目都显示"Table"

Which gives me a new column where all entries say "Table"

推荐答案

尝试

let Quelle= Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
#"Promoted Headers" = Table.PromoteHeaders(Quelle, [PromoteAllScalars=true]),
#"Geänderter Typ" = Table.TransformColumnTypes(#"Promoted Headers",{{"Group", type text}, {"Gender", type text}, {"Number", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Geänderter Typ","Group Sum2",(i)=>List.Sum(Table.SelectRows(#"Geänderter Typ", each [Group]=i[Group]) [Number]), type number )
in #"Added Custom"

这篇关于按组逐列的Power Query Sum作为新列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！