excel 使用Power Query合并不同表中的行

ercv8c1e  于 2023-03-24  发布在  其他
关注(0)|答案(1)|浏览(254)

我有2张单独的table来存放产品,虽然是2张不同的table,但它们的结构是完全一样的。
在这些表中,每一行都有一个项目,并说明应向哪些部门提供该项目。
但是,由于有两个表,因此同一项目可以出现在表中,并且在每个表中标记不同的部门。
我想做一个表,统一2个以前的,并确保出现在表中重复的项目不出来在2行,但它的行合并,在新表中指向2个表的部门。
显然,这个部门的概念是不真实的,我只是用它来试图更好地解释我的情况。
请问你能帮我解决这个问题吗?

+---------------------------------------+
| Table 01                              |
+---------+--------------+--------------+
| Product | Department A | Department B |
+---------+--------------+--------------+
| Item A  | X            |              |
+---------+--------------+--------------+
| Item B  |              | X            |
+---------+--------------+--------------+
| Item C  |              | X            |
+---------+--------------+--------------+

+---------------------------------------+
| Table 02                              |
+---------+--------------+--------------+
| Product | Department A | Department B |
+---------+--------------+--------------+
| Item A  |              | X            |
+---------+--------------+--------------+
| Item C  | X            |              |
+---------+--------------+--------------+
| Item D  | X            |              |
+---------+--------------+--------------+

+---------------------------------------+
| Merged tables                         |
+---------+--------------+--------------+
| Product | Department A | Department B |
+---------+--------------+--------------+
| Item A  | X            | X            |
+---------+--------------+--------------+
| Item B  |              | X            |
+---------+--------------+--------------+
| Item C  | X            | X            |
+---------+--------------+--------------+
| Item D  | X            |              |
+---------+--------------+--------------+
3bygqnnd

3bygqnnd1#

powwerquery中的一种方法,它的优点是可以在添加更多的Department列时处理任意数量的Department列

let 
T1Source =  Table.UnpivotOtherColumns(Table1, {"Product"}, "Attribute", "Value"),
T2Source =  Table.UnpivotOtherColumns(Table2, {"Product"}, "Attribute", "Value"),
combined = Table.Combine({T1Source & T2Source}),
#"Grouped Rows" = Table.Group(combined, {"Product", "Attribute"}, {{"data", each Text.Combine(List.Transform([Value], Text.From), ","), type text}}),
#"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Attribute]), "Attribute", "data")
in #"Pivoted Column"

备选

如果在表之间的同一行/列交叉点中从来没有文本,则可以使用

let 
T1Source =  Table.UnpivotOtherColumns(Table1, {"Product"}, "Attribute", "Value"),
T2Source =  Table.UnpivotOtherColumns(Table2, {"Product"}, "Attribute", "Value"),
combined = Table.Combine({T1Source & T2Source}),
#"Pivoted Column" = Table.Pivot(combined, List.Distinct(combined[Attribute]), "Attribute", "Value")
in #"Pivoted Column"

相关问题