Forum Discussion
Creating a custom column
Hello, sorry for the missmatch
The easiest way to achieve that in PQ, would be to create a summarized table and join your two tables
To achieve that, make a right click on the table and select reference (it will create a shortcut of the source table)
Then, in our new table, go in home and click on group by and in advanced, add a max column and a min column
Now, you have the desired table, in this table, add a calculated column to make the difference between max and min
Then go back in your main table, and click on merge queries (create a new query) and select dpt for both table
Now, extend the desired column and you have your desired table
Just for the two tables you use at the begining, make a right click on it and untick enable load (just to avoid too see these tables in your semantic model)
Thank you!😊 This worked
Super UserYou can also do this with grouping without merging tables.
let
Source =
Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc9JCsAgDAXQuwS6E4xx3LY9hnj/a9R87YBdmIg8fmKtFKxnKyyRDO39OGaHpkUfU96omUpipSxQeosKPYNP+Y08IJGWGRwy3PLNPIfUTFGTIP0/ckKPuWMRXJ81l9n6nQBYtIp02C4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Department = _t, #"Staff Member" = _t, Pen = _t, #"Total Stationary" = _t, #"%Pen" = _t]),
#"Changed Type" =
Table.TransformColumnTypes(
Source,
{
{"Date", type date}, {"Department", type text}, {"Staff Member", Int64.Type}, {"Pen", Int64.Type}, {"Total Stationary", Int64.Type}, {"%Pen", Percentage.Type}
}
),
#"Grouped Rows" =
Table.Group(
#"Changed Type",
{"Department"},
{
{"AllRows", each _, type table [Date=nullable date, Department=nullable text, Staff Member=nullable number, Pen=nullable number, Total Stationary=nullable number, #"%Pen"=nullable number]},
{"Department_Max", each List.Max([#"%Pen"]), Percentage.Type},
{"Department_Min", each List.Min([#"%Pen"]), Percentage.Type}
}
),
#"Expanded AllRows" =
Table.ExpandTableColumn(
#"Grouped Rows",
"AllRows",
{"Date", "Staff Member", "Pen", "Total Stationary", "%Pen"},
{"Date", "Staff Member", "Pen", "Total Stationary", "%Pen"}
)
in
#"Expanded AllRows"