英文:
Power BI: Merge Rows Where Column Values Match
问题
我想在表格中合并具有相匹配列值的行。例如，这是当前的表格：
Commodity | Region | Unit | 2007 | 2008 | 2009
Corn | US | $ | 10 | null | null
Corn | US | $ | null | 20 | null
Corn | US | $ | null | null | 30
我希望它看起来像这样：
Corn | US | $ | 10 | 20 | 30
我在网上找到了一个资源，建议首先选择列 Commodity、Region、Unit 并对其他列进行解构，然后对新创建的属性列进行旋转，选择值列作为我的值。然而，这只会导致与之前相同的表格。
英文:
I want to merge the rows of my table whenever the column values match. For instance, here is the table currently:
Commodity | Region | Unit | 2007 | 2008 | 2009
Corn | US | $ | 10 | null | null
Corn | US | $ | null | 20 | null
Corn | US | $ | null | null | 30
I want it to look like:
Corn | US | $ | 10 | 20 | 30
I have found a resource online saying to first select columns Commodity, Region, Unit and unpivot the others, then to pivot the newly created attribute column and select the value column for my values. However, that just results in the same table as before.
答案1
得分: 1
以下是翻译好的代码部分：
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
base = {"Commodity", "Region", "Unit"},
#"Grouped Rows" = Table.Group(Source, base, {
{"data", each
let #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(_, base, "Attribute", "Value")
in Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Attribute]), "Attribute", "Value")
, type table
}
}),
Columns = List.Difference(Table.ColumnNames(Source), base),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", Columns, Columns)
in #"Expanded data"
希望这有所帮助。
英文:
One way in powerquery. Assumes that the grouping are on Commodity/Region/Unit and that the number of columns after that is dynamic
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
base = {"Commodity", "Region", "Unit"},
#"Grouped Rows" = Table.Group(Source,base, {{"data", each
let #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(_, base, "Attribute", "Value")
in Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Attribute]), "Attribute", "Value")
, type table }}),
Columns=List.Difference(Table.ColumnNames(Source),base),
#"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", Columns,Columns)
in #"Expanded data"
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论