在查询表中排列数据 [英] Arranging Data in a Query Table

问题描述

我试图找出一种简单的方法(使用 PowerQuery)来转换:

I'm trying to figure out a simple way (using PowerQuery) to convert this:

进入这个:

我花了几天时间试图找出一种简单的方法来做到这一点.

I've spent days trying to figure out a simple way to do it.

我尝试过的一切都失败了.

Everything I've tried has failed.

推荐答案

您可以使用转换"选项卡上的分组依据"、按项目分组并为每个细分列(例如总和)定义聚合.然后将创建的代码从 List.Sum 调整为 List.RemoveNulls.然后使用 Table.FromColumns 从段列中添加一个带有嵌套表的列.删除原始段列并展开嵌套表.

You can use Group By on the Transform tab, group by project and define a aggregation for each of the segment columns (e.g. Sum). Then adjust the created code from List.Sum to List.RemoveNulls. Then add a column with nested tables from the segment columns, using Table.FromColumns. Remove the original segment columns and expand the nested tables.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project", type text}, {"Segment1", type text}, {"Segment2", type text}, {"Segment3", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Project"}, {{"Segment1", each List.RemoveNulls([Segment1]), type text}, {"Segment2", each List.RemoveNulls([Segment2]), type text}, {"Segment3", each List.RemoveNulls([Segment3]), type text}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Tabled", each Table.FromColumns({[Segment1],[Segment2],[Segment3]},{"Segment1","Segment2","Segment3"})),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Segment1", "Segment2", "Segment3"}),
    #"Expanded Tabled" = Table.ExpandTableColumn(#"Removed Columns", "Tabled", {"Segment1", "Segment2", "Segment3"}, {"Segment1", "Segment2", "Segment3"})
in
    #"Expanded Tabled"

这篇关于在查询表中排列数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！