Power Query Custom Column HELP

[peterso]

Hi all,

I'd like to create a column that returns a True / False based on the minimum value in a column per each type. I'm not the best at explaining - please take a look at the example:

Thanks for taking a look!

[PC2790 · Accepted solution]

Here is a step by step way to do it in Power Query:

1) Group by the columns to get the max value, somethin like below:

2) Apply a left outer join to merge the main table and the grouped by table:

= Table.NestedJoin(#"Changed Type", {"Value","Type"}, #"Grouped Rows", {"New","Type"}, "Grouped Rows", JoinKind.LeftOuter)

3) Now expand the columns to get final Result column:

= Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"New"}, {"Result"})

4) Now replace the values as per your requirement

= Table.ReplaceValue(#"Expanded Grouped Rows", each [Result],each if [Result] = null then "False" else "True",Replacer.ReplaceValue,{"Result"})

Complete M Code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs5PzlbSUTJUitUBcjIyi0qAPGMUnikKzxLKy08tRlIJ4QFNiQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}, {"Value", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Type"}, {{"New", each List.Min([Value]), type nullable number}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Value","Type"}, #"Grouped Rows", {"New","Type"}, "Grouped Rows", JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"New"}, {"Result"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Grouped Rows", each [Result],each if [Result] = null then "False" else "True",Replacer.ReplaceValue,{"Result"})
in
    #"Replaced Value"

[Anonymous]

Hi @peterso 

Paste it to Advanced Editor:

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "i45WCs5PzlbSUTJUitUBcjIyi0qAPGMUnikKzxLKy08tRlIJ4QFNiQUA",
          BinaryEncoding.Base64
        ),
        Compression.Deflate
      )
    ),
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Type = _t, Value = _t]
  ),
  #"Changed Type" = Table.TransformColumnTypes(Source, {{"Type", type text}, {"Value", Int64.Type}}),
  MinValue = Table.Group(
    #"Changed Type",
    {"Type"},
    {{"Min", each List.Min([Value]), type nullable number}}
  ),
  #"Merged Queries" = Table.NestedJoin(
    #"Changed Type",
    {"Type"},
    MinValue,
    {"Type"},
    "Changed Type",
    JoinKind.LeftOuter
  ),
  #"Expanded Changed Type" = Table.ExpandTableColumn(
    #"Merged Queries",
    "Changed Type",
    {"Min"},
    {"Min"}
  ),
  #"Added Custom" = Table.AddColumn(#"Expanded Changed Type", "Result", each [Value] = [Min]),
  #"Removed Columns" = Table.RemoveColumns(#"Added Custom", {"Min"})
in
  #"Removed Columns"