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
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