Forum Discussion
Helper IIINeed Help to Auto populate Shipping Delays Message in Power Query
Hi Team of Experts!
I need help in displaying Stock Alert.
My data sample and expected result is as follows:
| ORDER-LINE | ORDER QTY | Demand_Order | ALLOTTED_QTY | QTY_DIFFERENCE | PROMISE DATE | Supply_Date | Stock Alert | Message |
| SO-AB1234/1 | 100 | SO-AB1234/1 | 20 | 80 | April01 2020 | March10 2020 | Early | Partial Quantity (20) Arriving Early on March10 2020 for SO-AB1234/1 |
| SO-AB1234/1 | SO-AB1234/1 | 30 | 50 | April01 2020 | March20 2020 | Early | Partial Quantity (30) Arriving Early on March20 2020 for SO-AB1234/1 | |
| SO-AB1234/1 | SO-AB1234/1 | 40 | 10 | April01 2020 | April20 2020 | Late | Partial Quantity (40) Arriving Late on April20 2020 for SO-AB1234/1 | |
| SO-AB1234/1 | SO-AB1234/1 | 10 | 0 | April01 2020 | May15 2020 | Late | Final Quantity (10) Arriving Late on May15 2020 for SO-AB1234/1 |
My following Query is giving me error.
if[TOTAL_SHIPPED_QTY]>=[ORDER_QTY]
then "FULL QUANTITY SHIPPED" else
if[Supply_Date]<> null then if([QTY_DIFFERENCE]>0
and
[REMAIN_QTY]=[ALLOTTED_QTY]) then
"Full Quantity" & "(" & Number.ToText[ALLOTTED_QTY] & ")" & " Arriving on" & " " & [Supply_Date] & " " & "for" & [#"ORDER-LINE"] else
if([REMAIN_QTY]>[ALLOTTED_QTY]) and [QTY_DIFFERENCE]>0 then
"Partial Quantity" & "(" & Number.ToText[ALLOTTED_QTY] & ")" & " Arriving on" & " " & [Supply_Date] & " " & "for" & [#"ORDER-LINE"] else
if([REMAIN_QTY]>[ALLOTTED_QTY]) and [QTY_DIFFERENCE] = 0 then
"Final Quantity" & "(" & Number.ToText[ALLOTTED_QTY] & ")" & " Arriving on" & " " & [Supply_Date] & " " & "for" & [#"ORDER-LINE"]
else "NO SUPPLY DATE"
else
[Stock Alert]
Will really appreciate kind support of the great community.
Thanks & Best regards,
PG
With You got cal remaining, Changed logic. You need add early and late logic
Message = if(not(ISBLANK('Table'[ORDER QTY])) && [TOTAL_SHIPPED_QTY]>=[ORDER QTY] , "FULL QUANTITY SHIPPED" , if(not(ISBLANK([Supply_Date])), if([QTY_DIFFERENCE]>0 && [Cal remaining]=0 , "Full Quantity" & "(" & [ALLOTTED_QTY] & ")" & " Arriving on" & " " & [Supply_Date] & " " & "for" & [ORDER-LINE] , if([Cal remaining]>0 && [QTY_DIFFERENCE]>0 , "Partial Quantity" & "(" & [ALLOTTED_QTY] & ")" & " Arriving on" & " " & [Supply_Date] & " " & "for" & [ORDER-LINE] , if([Cal remaining]>[ALLOTTED_QTY] && [QTY_DIFFERENCE] = 0 , "Final Quantity" & "(" & [ALLOTTED_QTY] & ")" & " Arriving on" & " " & [Supply_Date] & " " & "for" & [ORDER-LINE] , "NO SUPPLY DATE") ))))
9 Replies
- amitchandak
Super User
Syntax has issues. If is in () and is && ; OR is || . This one still need revision
if([TOTAL_SHIPPED_QTY]>=[ORDER_QTY] , "FULL QUANTITY SHIPPED" , if([Supply_Date]<> null, if([QTY_DIFFERENCE]>0 && [REMAIN_QTY]=[ALLOTTED_QTY]) && "Full Quantity" & "(" & Number.ToText[ALLOTTED_QTY] & ")" & " Arriving on" & " " & [Supply_Date] & " " & "for" & [#"ORDER-LINE"] , if([REMAIN_QTY]>[ALLOTTED_QTY] && [QTY_DIFFERENCE]>0 , "Partial Quantity" & "(" & Number.ToText[ALLOTTED_QTY] & ")" & " Arriving on" & " " & [Supply_Date] & " " & "for" & [#"ORDER-LINE"] , if([REMAIN_QTY]>[ALLOTTED_QTY] && [QTY_DIFFERENCE] = 0 && "Final Quantity" & "(" & Number.ToText[ALLOTTED_QTY] & ")" & " Arriving on" & " " & [Supply_Date] & " " & "for" & [#"ORDER-LINE"] , "NO SUPPLY DATE") else [Stock Alert])))
pankajjThanks a ton Amit.
I really appreciate your swift response and help in correcting my query.
I will try your sollution and update you on the result.
Best regards,
Pankajj
- pankajj
I have tried your script in power bi and power query but was unsuccessfull to run it without errors.
I have uploaded the Power BI file for your kind reference.
https://drive.google.com/file/d/1IkQZlfQnA0VLpli3R6HdVoX4s_cUXdPo/view?usp=sharing
Kindly review the attached file.
Thanks a lot for your support.
Best regards,
PG