Power BI Challenge 4 - Delivery App Review

Challenge 4- Delivery App Data

So here it is all the 4th instalment of the EDNA challenge.

Remember its open to everyone so don’t be afraid to get stuck in.

To learn more about the challenge and how it works be sure to check out the forum below!

If you’re not sure of anything just reach out to me.

Remember: The weekly winner will receive a complimentary membership to the platform that they can share with anyone and the opportunity for your work to be showcased across our channels.

THE BRIEF

You are working at a consultancy that implemented an app for a client to help them improve their delivery process and fulfilment from warehouses to store.

The app was the created so that drivers could scan the label as opposed to entering it manually while also recording the time of arrival, the time they left the store and some other variables that the management were interested in improving.

The management is now looking to evaluate how the business has reacted and how effective the app is.

The senior consultant has extracted all data from the app and placed it into the file as below.

He now wants you to use this data and create a report or dashboard to help management visualise what is happening so that we can decide what the next stage of the transformation is.

The management are particularly interested in any warehouse store combination that it’s not working well for.

The time being spent at a store.

The number of times manual entry was still used.

The number of damaged labels

If returns are being collected

A 1 in the data represents true

A 0 represents false

The ball is now in your court and you need to find a way presenting this data back to Janet and the team.

SUBMISSION DUE DATE - Sunday, 26th July 2020 (PST)

Please can you all submit your PBIX files to powerbichallenge@enterprisedna.co

Objective: Produce a report which can help review the effectiveness of the app.

The data set can be downloaded below.

Challenge 4.xlsx (456.2 KB)

Best of luck!

Any issues or questions please reach out.

Haroon

Enterprise DNA

5 Likes

Hi @haroonali1000,

You might want to change the date to submit as July 12. :slight_smile:

thanks
Keith

@GuyJohnson,

If I’m looking at the calendar right, should be Sunday July 26th is submission date. I’ll let @haroonali1000 verify though.

Thanks
Jarrett

I was hoping the 12 was correct, and July was the typo. Or alternatively, July 12 was correct and 2020 was the typo (in that case, I’m confident I could put together a really nice report on this one… :smiley:)

  • Brian
1 Like

Sorry for the confusion all.

The submission date is the 26th July 2020.

i have corrected the post.

Thanks

Haroon

1 Like

Nice one, looking forward to diving into this.

1 Like

@haroonali1000,

Just need to clarify, which happens first, Arrived or Left? Multiple errors throughout if Arrived is before Left.

Thanks
Jarrett

@GuyJohnson,

I get that they are different that way, just trying to clarify which of the two is supposed to happen first. Have to know this before I can subtract one from the other to see how much time it took.

Thanks
Jarrett

Maybe someone can help me. I am not 100% sure what Label Damage/ Damage and Returns collected stands for exactly. thank you in advance.

2 Likes

@ojones looking at the data, I’m going with the assumption that 'Date Time Left" is departure from the Store they were delivering TO, not departure from the Warehouse.

So arrived is first, followed by left (my company has a similar process in tracking deliveries to customers, and we have the departure from the warehouse on a completely separate table since it doesn’t make sense to return to the warehouse after each delivery (sort of like the UPS or mail carrier doesn’t go back to the main hub)

@alexbadiu - I’m not expert on this, but my guess is that

Label Damage would mean that you can’t scan the label (have to enter data manually and so the App can’t be used)

Damage would be package damage (any delivery app would need to track this)

and Returns Collected would be packages going back to the Warehouse for some reason (probably after sitting at the store for a time before it was decided it wouldn’t be able to be sold)

2 Likes

@Heather,
I agree with your logic, just looking for confirmation from @haroonali1000. Column should probably be labeled differently so that it makes more sense.

Thanks
Jarrett

Agreed on renaming the column - in fact I would probably not use the word “LEFT” at all (to me that could mean remaining time :slight_smile: )

I’m going with the logic that the person who labeled the columns isn’t very data savvy, and I just have to do my best with what I’ve got (sadly, a norm in my PowerBI world)

1 Like

@haroonali1000
Looks like data error, the ones with negative and positive higher numbers.


Also Null values when tried to convert the column as date/time

@sunip,
The error is there on purpose. That’s part of the challenge. Figure out how to switch the Arrived to match the format of the other column, or vice versa.

Thanks
Jarrett

Thank you Heather. I was supposing the same thing for damage label and damage. But for returns collected I was considering the same package being returned, probably because of the damage and the fact that the store refuses the package. Your supposition seems more accurate though. Thanks

Hi @ojones, @sunip,

Not sure if the error is intentional because depending on your local region settings, the results for DateTime Left store change because it’s not formatted as text like the DateTime arrived at store column.

Would be helpful if @haroonali1000 could confirm if it should be interpreted in US format or not, I guess

Hi All,

Great to see you all getting stuck into the challenge i will hopefully clarify some of the questions you have all been asking.

Match Method is either- Manual or scanned. If the app was used correctly then they would have scanned.

Label damage-One of the main reasons match methods are manual is because there is normally damage to the label for scanning however this is not always the case we can sometimes have a damaged label that we still managed to scan and even a perfect label that they still used manual entry.

Damage- represents if there was any damage to the actual parcels being delivered.

Returns Collected- Stores are allowed to send customer returns, old stock etc and this field indicates if the store did or not.

DateTimeArrived DateTimeLeft- This is the date and time that the delivery arrives at the store. I can see that depending on your region/ local setting it is causing some issues. The format should be as follows see the picture below: DateTimeArrived should be DAY=2nd MONTH=01 YEAR=2020 TIME=18:06
DateTimeLeft should be DAY=2nd MONTH=01 YEAR=2020 TIME=18:50

You NEED to format this if this is not the case and you are seeing the same result as the picture.

DD/MM/YYYY HH:MM should be the format for both columns.

I hope this helps clarify the scenario.

Regards,
Haroon

4 Likes

Based on the Parameter
“You are working at a consultancy that implemented an app for a client to help them improve their delivery process and fulfilment from warehouses to store.”
Is it correct to infer that the phrase “DateTimeArrived” means DateTime item received from warehouse?
and “DateTime Left” means item was out for delivery to customer? in other words Warehouse and store hied third party delivery service of which this third party utilizing the App for this particular client.
Kindly confirm if my assumption is valid. Thank you

Hi @hamidmilani

Your current assumptions are not correct.

DateTimeArrived is the time the delivery truck arrived at a particular store.

DateTimeLeft is when that same delivery truck departed from that particular store.

Regards,
Haroon