Analyzing multiple columns in power query

user_13562186 0
Viewed 137

I wish to generate the Most common, Highest and Lowest values from the following data:

enter image description here

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"

enter image description here

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.

powerquery multiple-columns excel
1 Answers

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.

enter image description here

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'.
user_97581940
Related