当前位置：文江博客话题详情

我正在尝试从一列创建两个单独的列。我的成本类型列具有实际和预测为类型

发布于 2025-02-05 13:44:09 字数 204 浏览 3 评论 0原文

如上图所示，我试图将实际和预测分为列。是否有一种方法可以像图像中所示？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

贱贱哒 2025-02-12 13:44:09

它的过滤和组合。

使用列顶上的下拉列表来过滤“实际元素”的cost_types。删除cost_types列。将金额列重命名为实际

使用列在列上下拉下拉列表以过滤“预测”的cost_types。删除cost_types列。将金额列的重命名为预测

组合两个数据集

示例代码：

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

#"Filtered Rows" = Table.SelectRows(Source, each ([cost type] = "actual")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"cost type"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"amount", "actuals"}}),

#"Filtered Rows2" = Table.SelectRows(Source, each ([cost type] = "forecast")),
#"Removed Columns2" = Table.RemoveColumns(#"Filtered Rows2",{"cost type"}),
#"Renamed Columns2" = Table.RenameColumns(#"Removed Columns2",{{"amount", "forecast"}}),

combined = Table.Combine({#"Renamed Columns" , #"Renamed Columns2"})
in  combined

替代数据结构，这对我来说更有意义：

代码：

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"project id", type text}, {"title", type text}, {"manager", type text}, {"dept", type text}, {"Subproject id", type text}, {"effective date", type text}, {"cost type", type text}, {"amount", Int64.Type}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[#"cost type"]), "cost type", "amount", List.Sum)
in  #"Pivoted Column"

Its filtering and combining.

Use drop down atop the column to filter cost_types for "actuals". Remove cost_types column. Rename the amount column as actuals

Use drop down atop the column to filter cost_types for "forecast". Remove cost_types column. Rename the amount column as forecast

combine the two data sets

sample code:

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

#"Filtered Rows" = Table.SelectRows(Source, each ([cost type] = "actual")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"cost type"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"amount", "actuals"}}),

#"Filtered Rows2" = Table.SelectRows(Source, each ([cost type] = "forecast")),
#"Removed Columns2" = Table.RemoveColumns(#"Filtered Rows2",{"cost type"}),
#"Renamed Columns2" = Table.RenameColumns(#"Removed Columns2",{{"amount", "forecast"}}),

combined = Table.Combine({#"Renamed Columns" , #"Renamed Columns2"})
in  combined

alternate data structure, which makes more sense to me:

code:

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"project id", type text}, {"title", type text}, {"manager", type text}, {"dept", type text}, {"Subproject id", type text}, {"effective date", type text}, {"cost type", type text}, {"amount", Int64.Type}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[#"cost type"]), "cost type", "amount", List.Sum)
in  #"Pivoted Column"

回复收藏 0 原文

~没有更多了~