Forum Discussion
Creating a custom column
Hi, I need help to create two customs, one returning the max value of an existing row and the second custom column returning the minimum of the existing row so I can get the diff between the max and min
Max %Pen by staff per department
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
8 Replies
If the grouping is by Department, you can use ALLEXCEPT() in Calculated Column to keep only Department context, so that you’re finding the max/min per Department.
Max %Pen =CALCULATE (MAX ( 'table1'[% Pen] ),ALLEXCEPT ( table1, table1[Department] ))Min %Pen =CALCULATE (MIN ( table1[% Pen] ),ALLEXCEPT ( table1, table1[Department] ))and Diff %Pen in below picture.Thanks alot, this is what I am trying to achieve but within power query rather than using a Dax formular
DAX would be much easier in this situation, but if in Power Query, you would need to Group by 'Department' and apply one NestedJoin.
You can also use this UI generated M code,
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc9LCsAgDATQuwS6E4yJv23bY4j3v0bNKC0tXZiIPCaxNVL2HL2wJHK0jxOYA5oVe8xlo+4aSfUsHymjJZPK8Iu+Qg9Q5BWGB41P6k3PSS1VDGVQ/QldUjF67oLrz6prvn0qglarIoP2Cw==", 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 Stationery" = _t, #"% Pen" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}, {"Department", type text}, {"Staff Member", Int64.Type}, {"Pen", Int64.Type}, {"Total Stationery", Int64.Type}, {"% Pen", Percentage.Type}}), Grouped = Table.Group(#"Changed Type", {"Department"}, {{"Max %Pen", each List.Max([#"% Pen"]), type nullable number}, {"Min %Pen", each List.Min([#"% Pen"]), type nullable number}}), AddedDiff = Table.AddColumn(Grouped, "Diff %Pen", each [#"Max %Pen"] - [#"Min %Pen"], type number), Merged = Table.NestedJoin( #"Changed Type", {"Department"}, AddedDiff, {"Department"}, "DeptAgg", JoinKind.LeftOuter ), Expanded = Table.ExpandTableColumn(Merged, "DeptAgg", {"Max %Pen", "Min %Pen", "Diff %Pen"}) in Expanded
Hi Timahake8
Your request is not very clear, you need the max for? A departement? A staff member ?
If you need to achieve this kind of measure for dpt, this is the result
For this result, you have to create 3 measure:
Max %Pen =
CALCULATE(
MAX('YourTable'[% Pen]),
ALLEXCEPT('YourTable', 'YourTable'[Department])
)Min %Pen =
CALCULATE(
MIN('YourTable'[% Pen]),
ALLEXCEPT('YourTable', 'YourTable'[Department])
)Diff %Pen (Max - Min) = [Max %Pen] - [Min %Pen]Or do you just need to get 67% everywhere in Max% pen, 3% in Min% pen and the difference (64) in the last column?
Now, if you need these values, you just have to create one column for max:
Max %Pen =
MAX('YourTable'[% Pen])Min %Pen =
MIN('YourTable'[% Pen])Diff %Pen = Max %Pen - Min %Pen
If you try to achieve something else, do not hesitate to ask us 😉
Hi, many thanks. I am trying to achieve the first solution you have explained, but within power query rather using a dax. Thank you
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)
You 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"
