Forum Discussion

cottrera's avatar
cottrera
Post Prodigy
4 years ago
Solved

MAX multiple columns per row

Hi 

 

I have a table that has individual property addresses per row. The table contains multiple columns populated with years. I would like to add an additional columns that shows the MAX of all these columns per row.

 

I will also be using this result in a filter.

 

Property ReferenceASBEELECGUTTK&BREDECSROOFCustom Max
363420112013202020182017 2020
606 2019202020102021 2021
1979 20162010 2015 2016
4442 20122017 2020 2020
2137 2013  2021 2021
2530 20172011  20152017
581 2015    2015
48320162013 20122020 2020
1179 2019    2019
4472  20122011  2012
1497 2010    2010
2254 20202017   2020
3303 20132017  20182018
3266   2012  2012

 

thank you

 

Richard

Need Help
  • lbendlin's avatar
    lbendlin
    4 years ago

    Here's one possible option

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZFLDgQhCESv0nHdCynwdxbT97/GzKARdHphSskLVWDvgTNLuAMi0RBWQRyvOqR85QrP3UOOWe9abhs7BLRYaqUZnI2albRIEYHV4U2XwyBBbHUNexlm1kjsjIpNeHn7H5kqnYn80XSVtxH4jOryEfmZ23s/Kdij4AyoraS5UeNrKyDJvia3OMOYowvN/5h+tZLI+XCa+Wa35wM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Property Reference" = _t, ASBE = _t, ELEC = _t, GUTT = _t, #"K&B" = _t, REDECS = _t, ROOF = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Property Reference"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Property Reference"}, {{"Custom Max", each List.Max([Value]), type text}}),
    #"Merged Queries" = Table.NestedJoin(Source, {"Property Reference"}, #"Grouped Rows", {"Property Reference"}, "Grouped Rows", JoinKind.Inner),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Custom Max"}, {"Custom Max"})
in
    #"Expanded Grouped Rows"

    How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    Here's one possible option

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZFLDgQhCESv0nHdCynwdxbT97/GzKARdHphSskLVWDvgTNLuAMi0RBWQRyvOqR85QrP3UOOWe9abhs7BLRYaqUZnI2albRIEYHV4U2XwyBBbHUNexlm1kjsjIpNeHn7H5kqnYn80XSVtxH4jOryEfmZ23s/Kdij4AyoraS5UeNrKyDJvia3OMOYowvN/5h+tZLI+XCa+Wa35wM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Property Reference" = _t, ASBE = _t, ELEC = _t, GUTT = _t, #"K&B" = _t, REDECS = _t, ROOF = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Property Reference"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Property Reference"}, {{"Custom Max", each List.Max([Value]), type text}}),
    #"Merged Queries" = Table.NestedJoin(Source, {"Property Reference"}, #"Grouped Rows", {"Property Reference"}, "Grouped Rows", JoinKind.Inner),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Custom Max"}, {"Custom Max"})
in
    #"Expanded Grouped Rows"

    How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".