计算的列，其中包含一行中许多列的值之和 [英] Calculated column with the sum of values from many columns in a row

问题描述

我需要对每一行的所有值求和，并在计算的列中显示它们。当我处理大量表中的许多列时，添加类似

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]

效率很低。 是否有更短的方法？

推荐答案

是的。您应该先使用查询编辑器取消其他列，然后分组依据 。

Yes, there is. You should "Unpivot other columns" and then "Group By" using the Query Editor.

1. 假设此数据集：

1. 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.

选择取消其他列 ：

您现在应该看到以下内容：

You should now see this:

在功能区的转换选项卡上，选择最左侧的分组依据选项。并像这样填写对话框：

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步骤，让您的可视化处理。

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:\Experiments\PowerBi\denormalized.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"

这篇关于计算的列，其中包含一行中许多列的值之和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！