Creating a custom column

[Timahake8]

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

[Cookistador · Accepted solution]

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)

[jgeddes]

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"

[Cookistador]

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 😉

[Timahake8]

Hi, many thanks. I am trying to achieve the first solution you have explained, but within power query rather using a dax. Thank you

[Cookistador · Accepted solution]

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)

[Timahake8 · Accepted solution]

Thank you!😊 This worked

[MasonMA]

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. 

[Timahake8]

Thanks alot, this is what I am trying to achieve but within power query rather than using a Dax formular

[MasonMA]

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