Need Help to Auto populate Shipping Delays Message in Power Query

[pankajj]

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

[amitchandak]

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])))

[pankajj]

Thanks 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]

@amitchandak 

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

[amitchandak]

I corrected the current logic. Check

Message = if([TOTAL_SHIPPED_QTY]>=[ORDER QTY]
, "FULL QUANTITY SHIPPED" ,
	if(not(ISBLANK([Supply_Date])),
		if([QTY_DIFFERENCE]>0
		&& [REMAIN_QTY]=[ALLOTTED_QTY] ,  "Full Quantity" & "(" & [ALLOTTED_QTY] & ")" & " Arriving on" & " " & [Supply_Date] & " " & "for" & [ORDER-LINE] ,
			if([REMAIN_QTY]>[ALLOTTED_QTY] && [QTY_DIFFERENCE]>0 ,
				"Partial Quantity" & "(" & [ALLOTTED_QTY] & ")" & " Arriving on" & " " & [Supply_Date] & " " & "for" & [ORDER-LINE] ,
					if([REMAIN_QTY]>[ALLOTTED_QTY] && [QTY_DIFFERENCE] = 0 &&
						"Final Quantity" & "(" & [ALLOTTED_QTY] & ")" & " Arriving on" & " " & [Supply_Date] & " " & "for" & [ORDER-LINE]

, "NO SUPPLY DATE",[ALERT])

))))

Also, find attached files

[pankajj]

@amitchandak 

This is amazing.

The query is working however it is giving Partial message only for the first time (when only 20 units received, 80 still pending) and FULL QUANTITY RECEIVED FOR REST OF THE QUANTITY.

Ideally the output message i want to show should look something like:

Partial Quantity (20) Arriving Early on March10 2020 for SO-AB1234/1
Partial Quantity (30) Arriving Early on March20 2020 for SO-AB1234/1
Partial Quantity (40) Arriving Late on April20 2020 for SO-AB1234/1
Final Quantity (10) Arriving Late on May15 2020 for SO-AB1234/1

[amitchandak]

I have done a small modification. But I think data which you have provided me has some issues. As Qty remain is 0 in three-row, I think if you correct that it should work, or can help in making it work

Message = if(not(ISBLANK('Table'[ORDER QTY])) && [TOTAL_SHIPPED_QTY]>=[ORDER QTY]
, "FULL QUANTITY SHIPPED" ,
	if(not(ISBLANK([Supply_Date])),
		if([QTY_DIFFERENCE]>0
		&& [REMAIN_QTY]=[ALLOTTED_QTY] ,  "Full Quantity" & "(" & [ALLOTTED_QTY] & ")" & " Arriving on" & " " & [Supply_Date] & " " & "for" & [ORDER-LINE] ,
			if([REMAIN_QTY]>[ALLOTTED_QTY] && [QTY_DIFFERENCE]>0 ,
				"Partial Quantity" & "(" & [ALLOTTED_QTY] & ")" & " Arriving on" & " " & [Supply_Date] & " " & "for" & [ORDER-LINE] ,
					if([REMAIN_QTY]>[ALLOTTED_QTY] && [QTY_DIFFERENCE] = 0 ,
						"Final Quantity" & "(" & [ALLOTTED_QTY] & ")" & " Arriving on" & " " & [Supply_Date] & " " & "for" & [ORDER-LINE]

, "NO SUPPLY DATE")

))))

[pankajj]

@amitchandak 

I agree with you. I think if REMAIN_QTY can be a caclulated column which is auto populated by reducing the ALLOCATED_QTY from the Actual ORDER_QTY, the query should provide desired result.

However i am not getting the correct REMAIN_QTY since the ORDER_QTY is only in the 1st Row.

any work arounds?

[pankajj]

@amitchandak 

Hi Amit,

Sorry to bother again.

I'm sure being a super user, i will be pretty quick for you.

Can you please suggest the way to calculate REMAIN_QTY.

Thanks a ton for all your patience and help so far.

You guys are doing a excellent job for the community.

Best regards,

Pankajj

[amitchandak · Accepted solution]

@pankajj 

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")

))))