Fill up columns with conditions in power query

[ngocnguyen]

Hi all

I have below table. Now I wanna create a "Max rate" columns with below logic:

Fill up the Max rate of max distance for 1 shipment in column" Max rate"

Ex: Shipment B have 2 rates with max distance ( 12) : 1719 and 1741  => fill up 1741 into " Max rate" column

So, is there any way to do it with POWER QUERY?

Thanks in advance for your support!

[v-yalanwu-msft · Accepted solution]

Hi @ngocnguyen  , 

According to you description, You could copy the two tables first, and then group and merge .The steps are as follows：

Step1: Copy two tables

Step2: Group by

Step3: merge two tables and expanded tables

Step4: add conditional column and remove empty rows:

Step5: Group by

Step6 : merge two tables and expanded tables

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[amitchandak]

@ngocnguyen , You have this video deal with category max - if that can help

https://www.youtube.com/watch?v=8KaMj2DFpWg

In DAX you can a new column

new column =
var _1 = maxx(filter(Table, [shipment no] =earlier([shipment no])),[distance])
return
maxx(filter(Table, [shipment no] =earlier([shipment no]) && [distance] =_1),[Rate])

[ngocnguyen]

@amitchandak 

thanks for your rep. However, I wanna create formulas in Power query. So is there any way to do it?