I'm trying to use the following code to get a new added column with information about a max value from a given field.
But what I would want is to have the possiblity to group a table where I filter it by a condition where the Custom column value would be 1.
I need to transform somehow the following parte of the code:
[
filter = [ID] /*should I add here another filter?/,
max2=Table.Group(
Source, {"ID"},
{{"MaxFiltered2", each List.Max([CODE])}}
){[ID=filter]}[MaxFiltered2]
][max2]
)
Here follows the code sequence:
#"Added Custom" = Table.AddColumn(
#"Changed Type",
"maxInt",
each
[
filter = [ID],
max=Table.Group(
Source, {"ID"},
{{"MaxFiltered", each List.Max([TAX])}}
){[ID=filter]}[MaxFiltered]
][max]
),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom", each if [maxInt]=[TAX] then 1 else 0),
#"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom] = 1)),
#"Added Custom2" = Table.AddColumn(
#"Changed Type",
"maxInt2",
each
[
filter = [ID],
max2=Table.Group(
Source, {"ID"},
{{"MaxFiltered2", each List.Max([CODE])}}
){[ID=filter]}[MaxFiltered2]
][max2]
)
in
#"Added Custom2"
Inputput desired:
ID TAX CODE
A 4 921
A 6 500
A 6 200
B 2 700
B 2 500
B 1,5 100
Output desired:
ID TAX CODE
A 6 500
B 2 700
(to get the max for A and B IDs both on TAX and CODE variables)
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIBYksjQ6VYHQjfDIhNDQxQ+EZQvhOIDcTmaHxTJL6hnimIBInEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, TAX = _t, CODE = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"TAX", type number}, {"CODE", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"All", each _, type table [ID=nullable text, TAX=nullable number, CODE=nullable number]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom",
each Table.First(
Table.Sort([All],{{"TAX", Order.Descending},{"CODE", Order.Descending}})
)
),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"ID", "All"}),
#"Expanded Custom" = Table.ExpandRecordColumn(#"Removed Columns", "Custom", {"ID", "TAX", "CODE"}, {"ID", "TAX", "CODE"})
in
#"Expanded Custom"