I wish to generate the Most common, Highest and Lowest values from the following data:
I have also added an additional column to handle text comments.
M Code so far:
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"
As shown in the image this isn't quite right for the following errors:
- When there is an equal split the most common returns null (expected)
- Text pulls through as the highest value (not expected)
Will be working on this but any suggestions are appreciated.
CodePudding user response:
Assuming:
- You made a mistake on row 7 and 'Not Limited' should be amongst the most frequent values;
- You also want to know that 33 is the lowest value in row 12, not just the highest;
- You can possibly have multiple unique text values you'd like to concatenate.
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"
Steps:
- All columns to "Type Text";
- Created an helper column with lists of values after
Text.Combine
&Text.Split
combo; - Used
List.Modes
to return most common values inside aText.Combine
to return 'Most Common'; - Used
List.Max
andList.Min
in combination withList.Transform
to return 'Highest' and 'Lowest' value; - Used a combination of
Text.Combine
,List.Distinct
,List.RemoveMatchingItems
andList.Transform
to return only unique actual text values; - Removed columns1-6 and helper and replaced errors with 'null'.