power查询-M语言-将列转换为行

Question

我有一个电子表格，其中包含列名作为产品名称，数量，成本。

我想将它转换为包含产品名称、数量、成本的数据行。

请看下面关于我想要什么的图片。

​

​

在Power语言中处理这个问题的最佳方法是什么？

我不确定我是否只想把那些有刺激名称、数量和成本的列转起来？

谢谢

Answer 1

有个办法..。

从这个表开始，作为Table1：

​

​

您可以选择Customer列和Un透视其他列以获得以下结果：

​

​

然后，您可以添加一个索引列(保持它的名称为Index)，然后添加一个自定义列(保持它的名称为自定义)，并使用if Text.EndsWith([Attribute],"Cost") then 1 else 0作为它的公式，以获得如下结果：

​

​

然后添加另一个自定义列..。将其命名为“总成本”，并输入#"Unpivoted Other Columns"[Value]{[Index]+(List.Count(#"Added Custom"[Custom])/List.Sum(#"Added Custom"[Custom]))}作为其计算公式：

​

​

上述两个步骤首先是根据成本在“价值”栏中的位置来确定T恤衫的相应成本，然后实际定位成本并将其记录在与各自T恤衫相同的线上。Index列提供行定位信息，而自定义列提供计数信息--包括总列表计数和带成本的行数。我使用计数信息来确定向下移动“值”列的索引位置的数量，以动态获取相关的成本值。

然后对属性列进行筛选，使用文本筛选器>不以.然后键入“成本”一词。所有以“成本”字结尾的属性条目的行都应该消失：

​

​

移除索引列和自定义列，并将属性列和值列分别重命名为Product和Quantity，以获得最终结果：

​

​

这是我的M码：

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Customer"}, "Attribute", "Value"),
#"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index", 0, 1),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if Text.EndsWith([Attribute],"Cost") then 1 else 0),
#"Added Custom2" = Table.AddColumn(#"Added Custom", "Total Cost", each #"Unpivoted Other Columns"[Value]{[Index]+(List.Count(#"Added Custom"[Custom])/List.Sum(#"Added Custom"[Custom]))}),
#"Filtered Rows" = Table.SelectRows(#"Added Custom2", each not Text.EndsWith([Attribute], "Cost")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index", "Custom"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Attribute", "Product Name"}, {"Value", "Quantity"}})
in
#"Renamed Columns"

Answer 2

这里的关键是旋转和不旋转。

从这张桌子开始，

​

​

选择右边的四列，然后单击Transform > Unpivot列以获得以下表：

​

​

现在创建一个自定义列，使用此公式对值进行分类。

if Text.EndsWith([Attribute], "Cost") then "Cost" else "Quantity"

我还在" Cost"列的末尾砍掉了Attribute部分。您可以转换>替换值并将" Cost"替换为空，也可以在分隔符" Cost"之前转换>提取>文本。

​

​

现在转到定制列(选择Value列作为值列选择)，最后将Attribute列重命名为Product Name。

​

​

以下是我所有步骤的M代码：

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcknNK0stUnAuLS7Jz00tUtJRMjIGEoYmIJapqZ6pAYhnZKpnYKAUqxOt5JyRmZyYno+swdAQSJiagtUZgNQBeeYQDbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Customer = _t, #"Product Orange T-shirt" = _t, #"Product Blue T-shirt" = _t, #"Product Orange T-shirt Cost" = _t, #"Product Blue T-shirt Cost" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Product Orange T-shirt", Int64.Type}, {"Product Blue T-shirt", Int64.Type}, {"Product Orange T-shirt Cost", type number}, {"Product Blue T-shirt Cost", type number}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Customer"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Custom", each if Text.EndsWith([Attribute], "Cost") then "Cost" else "Quantity"),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom"," Cost","",Replacer.ReplaceText,{"Attribute"}),
    #"Pivoted Column" = Table.Pivot(#"Replaced Value", List.Distinct(#"Replaced Value"[Custom]), "Custom", "Value", List.Sum),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Attribute", "Product Name"}})
in
    #"Renamed Columns"