分析电源查询中的多列

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行上犯了一个错误，“不限”应该是最常见的值之一；
• 您还想知道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＆amp; text.split combo;
• 使用的list.modes返回text.com.bine返回“最常见”的最常见值；
• 使用list.max和list.min结合list.transform返回“最高”和“最低”值；
• 使用text.com的组合，list.distinct，list.removematchingiTems和list> list.list.transform仅返回唯一的实际文本值；
• 删除了列1-6和助手，并用“ null”替换了错误。