excel - 分析电源查询中的多个列

Question

我希望从以下数据中生成最常见、最高和最低的值:

我还添加了一个额外的列来处理文本评论。
到目前为止的 M 代码:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type any}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Most Common", each List.Mode(
Record.ToList(
Table.SelectColumns(#"Added Index",
List.RemoveFirstN(
    Table.ColumnNames(#"Changed Type"))){[Index]}))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Highest", each List.Max(
Record.ToList(
Table.SelectColumns(#"Added Index",
List.RemoveFirstN(
    Table.ColumnNames(#"Changed Type"))){[Index]}))),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Min", each List.Min(
Record.ToList(
Table.SelectColumns(#"Added Index",
List.RemoveFirstN(
    Table.ColumnNames(#"Changed Type"))){[Index]}))),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom2",{"Most Common", "Highest", "Min"})
in
    #"Removed Other Columns" 

如图所示，这对于以下错误并不完全正确:

当等分时，最常见的返回 null(预期)

文本作为最大值拉出(非预期)

将致力于此，但任何建议表示赞赏。

Answer 1

假设:

您在第 7 行犯了一个错误，“Not Limited”应该是最常见的值之一；

您还想知道 33 是​​第 12 行中的最小值，而不仅仅是最高值；

您可能有多个要连接的唯一文本值。

let
    Source = Excel.CurrentWorkbook(){[Name="Tabel1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Lists", each Text.Split(Text.Combine({[Column1],[Column2],[Column3],[Column4],[Column5],[Column6]},"|"),"|")),
    #"Added Custom" = Table.AddColumn(#"Added Custom1", "Most Common", each Text.Combine(List.Modes([Lists]),",")),
    #"Added Custom2" = Table.AddColumn(#"Added Custom", "Highest", each List.Max(List.Transform([Lists], each try Number.FromText(_) otherwise null))),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Lowest", each List.Min(List.Transform([Lists], each try Number.FromText(_) otherwise null))),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Text Comments", each Text.Combine(List.Distinct(List.RemoveMatchingItems(List.Transform([Lists], each try if Number.FromText(_) <>"" then "" else "" otherwise (_)),{""})),",")),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Added Custom4", {{"Highest", null}, {"Lowest", null}}),
    #"Removed Columns" = Table.RemoveColumns(#"Replaced Errors",{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Lists"})
in
    #"Removed Columns"

脚步:

“键入文本”的所有列；

在 Text.Combine 之后创建了一个包含值列表的辅助列& Text.Split组合；

二手 List.Modes返回 Text.Combine 中最常见的值返回“最常见”；

二手 List.Max和 List.Min结合 List.Transform返回“最高”和“最低”值；

使用了 Text.Combine 的组合, List.Distinct , List.RemoveMatchingItems和 List.Transform只返回唯一的实际文本值；

删除了 column1-6 和 helper，并将错误替换为“null”。