英文:
Create summary table from many tables Power BI
问题
Table 1- True | 7 | 5 |
Table 1 -False | 8 | 9 |
Table 2- True | 4 | 15 |
Table 2 -False | 11 | 9 |
英文:
I have many tables and from that, I need to create a summary table.
Table 1 -
| IDs | PSC id | PSC | ACV id | ACV |
|---|---|---|---|---|
| A3 | True | 2 | False | 4 |
| A4 | True | 3 | True | 1 |
| A5 | False | 4 | False | 4 |
| A6 | False | 5 | True | 6 |
Table 2 -
| IDs | PSC id | PSC | ACV id | ACV |
|---|---|---|---|---|
| A5 | True | 12 | False | 7 |
| A6 | True | 3 | True | 3 |
| A7 | False | 4 | False | 4 |
| A9 | False | 5 | True | 1 |
The summary table should be like this (summation of all True and Summation of all False )
| Table | ACV | PSC |
|---|---|---|
| Table 1- True | 7 | 5 |
| Table 1 -False | 8 | 9 |
| Table 2- True | 4 | 15 |
| Table 2 -False | 11 | 9 |
答案1
得分: 1
这不太简单 - 也许Ron或Horseyride有更好的想法。
向每个表格添加一个自定义列，列名为表格名称。例如：
表格 1：
表格 2：
追加查询如下：
追加的表格：
按照以下方式分组：
添加一个自定义列：
List.Sum( Table.SelectRows(Source, (x)=> x[Custom] = [Custom] and x[ACV id] = [PSC id])[ACV])
最终结果：
英文:
This isn't straightforward - maybe Ron or Horseyride have better ideas.
Add a custom column to each table with the table name. e.g.
Table 1:
Table 2:
Append queries as new:
Appended table:
Group exactly as follows:
Add a custom column:
List.Sum( Table.SelectRows(Source, (x)=> x[Custom] = [Custom] and x[ACV id] = [PSC id])[ACV])
Final result:
答案2
得分: 0
以下是已翻译的代码部分：
// 假定要合并的所有表格都以 "Table" 开头，并且是唯一的查询
let Source = Table.SelectRows(Record.ToTable(#sections[Section1]), each Text.StartsWith([Name], "Table")),
List = List.Union(List.Transform(Source[Value], each Table.ColumnNames(_))),
#"Expanded Data" = Table.ExpandTableColumn(Source, "Value", List, List),
Combined = Table.AddColumn(Table.RenameColumns(Table.SelectColumns(#"Expanded Data",{"Name", "PSC id", "PSC"}),{{"PSC id", "id"}, {"PSC", "value"}}), "Type", each "PSC")
& Table.AddColumn(Table.RenameColumns(Table.SelectColumns(#"Expanded Data",{"Name", "ACV id", "ACV"}),{{"ACV id", "id"}, {"ACV", "value"}}), "Type", each "ACV"),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(Combined, {{"id", type text}}, "en-US"),{"Name", "id"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Merged"),
#"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Type]), "Type", "value", List.Sum)
in #"Pivoted Column"
请注意，我已将HTML实体编码 " 替换为双引号 ", 以使代码更易阅读。
英文:
Alternate version that does NOT require adjusting the initial tables at all
// assumes all table to combine start with Table and are the only queries to do so
let Source = Table.SelectRows(Record.ToTable(#sections[Section1]), each Text.StartsWith([Name], "Table")),
List = List.Union(List.Transform(Source[Value], each Table.ColumnNames(_))),
#"Expanded Data" = Table.ExpandTableColumn(Source, "Value", List,List),
Combined = Table.AddColumn(Table.RenameColumns(Table.SelectColumns(#"Expanded Data",{"Name", "PSC id", "PSC"}),{{"PSC id", "id"}, {"PSC", "value"}}), "Type", each "PSC")
& Table.AddColumn(Table.RenameColumns(Table.SelectColumns(#"Expanded Data",{"Name", "ACV id", "ACV"}),{{"ACV id", "id"}, {"ACV", "value"}}), "Type", each "ACV"),
#"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(Combined, {{"id", type text}}, "en-US"),{"Name", "id"},Combiner.CombineTextByDelimiter("-", QuoteStyle.None),"Merged"),
#"Pivoted Column" = Table.Pivot(#"Merged Columns", List.Distinct(#"Merged Columns"[Type]), "Type", "value", List.Sum)
in #"Pivoted Column"
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论