我希望從以下資料中生成最常見、最高和最低的值：
我還添加了一個額外的列來處理文本評論。
到目前為止的 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（預期）
- 文本作為最高值通過（非預期）
將致力于此，但任何建議表示贊賞。
uj5u.com熱心網友回復：
假設：
- 您在第 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回傳 a 中最常見的值Text.Combine以回傳 'Most Common'； - 使用
List.Max并List.Min結合List.Transform回傳“最高”和“最低”值； - 使用
Text.Combine,List.Distinct,List.RemoveMatchingItems和的組合List.Transform只回傳唯一的實際文本值； - 洗掉了 column1-6 和 helper，并將錯誤替換為“null”。
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/474375.html