Convert a DAX to M Query

[JajatiDev]

Hi,

I need guidance on how to write the code to convert the following DAX into an M query.

IF('Open Orders'[Delivery Type]="Full order consolidation" && SUMX(FILTER('Open Orders ','Open Orders '[Order No] = EARLIER('Open Orders '[Order No])),'Open Orders '[Order Quantity])=SUMX(FILTER('Open Orders ','Open Orders '[Order No]=EARLIER('Open Orders '[Order No])),'Open Orders '[Confirmed Quantity]),"Allocated",

IF('Open Orders '[Delivery Type]="Full order consolidation" && SUMX(FILTER('Open Orders ','Open Orders '[Order No]=EARLIER('Open Orders '[Order No])),'Open Orders '[Confirmed Quantity])>0 ,"Partial Allocation",

"Pending Allocation"))

Regards,

Jajati Dev

[wdx223_Daniel]

NewStep=let a=Table.Buffer(Table.Group(PreviousStepName,"Order No",{"n",each List.Sum([Order Quantity])-List.Sum([Confirmed Quantity])})) in Table.AddColumn(PreviousStepName,"New Column",each if [Delivery Type]="Full order consolidation" and a{[#"Order No"=[Order No]]}?[n]?>0 then "Partial Allocation" else "Allocated")

[v-kkf-msft · Accepted solution]

Hi @JajatiDev ,

Please try the custom column in Power Query.

= let 
  no = [Order No],
  tab = Table.SelectRows(#"Changed Type", each [Order No] = no),
  sumorder = List.Sum(tab[#"Order Quantity"]),
  sumCom = List.Sum(tab[#"Confirmed Quantity"])
in
  if [Delivery Type] = "Full order consolidation" and sumorder = sumCom then "Allocated" else if  [Delivery Type]= "Full order consolidation" and sumCom >0 then "Partial Allocation" else "Pending Allocation"

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.