我需要对每一行中的所有值求和并将它们显示在计算列中.当我处理大量表格中的大量列时，添加类似
I need to sum all values in each row and display them in a calculated column. As I deal with lots of columns in lots of tables, adding something like
CalculatedColumn = 'public table_name'[column1] + 'public table_name'[column2] + ... + 'public table_name'[column528]确实效率低下.有更短的方法吗?
推荐答案是的，有.您应该使用查询编辑器Unpivot other columns"，然后Group By".
Yes, there is. You should "Unpivot other columns" and then "Group By" using the Query Editor.
假设这个数据集:
Suppose this dataset: item;col1;col2;col3;col4;col5 apple;1;2;3;4;5 orange;1;2;3;5;8 banana;1;2;4;6;8
加载它，然后打开查询编辑器.
Load it up, and open the query editor.
选择Unpivot Other Columns":
Choose "Unpivot Other Columns":
你现在应该看到这个:
在功能区的转换"选项卡上，选择最左侧的分组依据"选项.然后像这样填写对话框:
On the "Transform" tab in the ribbon, choose the leftmost "Group By" option. And fill out the dialog like so:
您现在应该得到想要的最终结果:
You should now have the wanted end result:
您也可以跳过 Group By step 并让您的可视化来处理.
You could also skip the Group By step and let your visualization handle that.
PS.如果您也需要一些非求和列，我建议您创建一个具有相同源的重复数据集，并将其链接到具有关系的原始表，或者合并它所以你会得到一个包含所有想要的列的决赛桌.
PS. Should you need a few non-summed columns too I recommend either creating a duplicate dataset with the same source and either linking it to the original table with a relationship, or merging it so you get a final table with all wanted columns.
脚注，这是为您生成的 Power Query:
Footnote, this is the Power Query that is generated for you:
let Source = Csv.Document(File.Contents("D:ExperimentsPowerBidenormalized.csv"),[Delimiter=";", Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None]), #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"item", type text}, {"col1", Int64.Type}, {"col2", Int64.Type}, {"col3", Int64.Type}, {"col4", Int64.Type}, {"col5", Int64.Type}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"item"}, "Attribute", "Value"), #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"item"}, {{"SumCol", each List.Sum([Value]), type number}}) in #"Grouped Rows"更多推荐
计算列,其中包含一行中许多列的值的总和
发布评论