question

NguyenThiThanhNgoc-0923 avatar image
0 Votes"
NguyenThiThanhNgoc-0923 asked DanielZhang-MSFT commented

max with conditions in power query

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!


94722-capture.png94683-capture.png


dotnet-csharp
capture.png (10.9 KiB)
capture.png (10.9 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @NguyenThiThanhNgoc-0923,
May I know whether your issue has been solved or not? If not, please share it in here. We can work together to figure it out.
Best Regards,
Daniel Zhang

0 Votes 0 ·

1 Answer

DanielZhang-MSFT avatar image
0 Votes"
DanielZhang-MSFT answered

Hi NguyenThiThanhNgoc-0923,
First, you can use group by statement to group.
And then use the Max function to obtain the maximum value of the Distance field, and use this as a condition to obtain the maximum rate.
Here is a sql statement you can refer to.

  SELECT MAX(Rate) FROM rateTest  where Distance in (select max(Distance) from rateTest)  group by  ShipmentNo

Best Regards,
Daniel Zhang


If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.