Analyzing multiple columns in power query-CodePudding

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 a Text.Combine to return 'Most Common';
Used List.Max and List.Min in combination with List.Transform to return 'Highest' and 'Lowest' value;
Used a combination of Text.Combine, List.Distinct, List.RemoveMatchingItems and List.Transform to return only unique actual text values;
Removed columns1-6 and helper and replaced errors with 'null'.