Maximum value in a column using Power Query

[Anonymous]

Hi There,

Hope you are doing well.

I am trying to create one custom column in Power Query where i need to take the Maximum value in each row with respect to other column.

My sample data has two columns i.e., "VBELN" and "VBELV" and my requirement is to create a custom column where i want Maximum values (VBELV) in each row with respect to "VBELN" column. and it should be using M query not DAX.

In Excel we can use Maxifs function which i want to replicate in M query . Please see below data set and solution in Excel which i am looking to replicate in M Query .

Request you to please help .

Thanks,

Ashish

[slorin]

Hi

Just group with max and [VBLEV] then expand

let
Source = YourSource,
Group = Table.Group(Source, {"VBELN"}, 
   {{"VBELV", each [VBELV]}, 
    {"Max", each List.Max([VBELV]), type number}}),
Expand = Table.ExpandListColumn(Group, "VBELV")
in
Expand

Stéphane

[PhilipTreacy]

Hi @Anonymous 

Download example PBIX file

You can do this a couple of ways but both require doing a join of tables, or joining the query with itself.  In my example file I've duplicated the query and joined the 2 queries (tables).

The approach is to group the VBELN column on the max value in the VBELV column.  Then join the result with the original table.  This is the result

Regards

Phil

[Anonymous]

Hi @PhilipTreacy

Thank you for your response.

I have tried the above step but i am not getting Max  value from "VBELN" . Also when i am getting Max value these  records are appearing 4 times .

I  need maximum value of "VBELN" with Respect to "VBELN" and in the output it should appear only one record /row with rest other columns

Please see below Excel what i am expecting now :

Request you to please help .

Thanks ,

Ashish

[slorin]

Hi,

Group by VBELN , use Table.Max function on VBELV column

then expand Record

let
Source = YourSource,
Group = Table.Group(Source, {"VBELN"}, {{"Data", each Table.Max(_, {"VBELV"}), type record}}),
Expand = Table.ExpandRecordColumn(Group, "Data", List.Difference(Table.ColumnNames(Source),{"VBELN"}), List.Difference(Table.ColumnNames(Source),{"VBELN"}))
in
Expand

Stéphane

[PhilipTreacy]

Hi @Anonymous 

I'm confused.  My code does give you the max value of VBELV with respect to VBELN.

If you just want 1 row for each value of VBELN, just remove duplicates on the VBELN column.

If I'm not understanding please try explaining again perhaps with an image showing the final result you want.

Regards

Phil

[Anonymous]

Hi Phil,

I got the solution :

I was doing one thing wrong in Group by I was taking that column aslo for which i need Max . SO i removed that column and took others and did the MAX of VBELN and go the result.

Thank you so much for your quick response though .

Regards,

Ashish