Tracing a series of events in a table of transactions.

[Anonymous]

Hi guys, I need help to see if this is possible to achieve. I have a table that contains last 60 days transactions happends in the shop, about 8 million rows. It contains transactions for every actions happened to every raw materials, intermedia products, and final products on each process (For example, load, unload, cleaning etc.). Except final products, each transaction has a barcode which only meaningful to each single intermedia product, which basiclly some prefix plus a series of hex number form 0 to FFFFFFFF and restart form 0 once hit FFFFFFFF. This gurantees the barcode of unload action from previous step is same as the barcode of load action on next step. So the target is if I was given a barcode of final product, a table that contains every single process that made this product should be generated. In addition, some product required to go through some process multiple times which means the length of the result table a not constant.

Unfortunately, the data is considered as sensitive information, I cannot really share it. But I have some basic idea for this project like tracing the process according to barcode, machine (process) number, actions on that machine, and the time actions happend. From some research, looks like Power Quary M is the only possible way to achieve it? Any help or suggestions would be appreciated! Thanks!

[v-danhe-msft]

Hi @Anonymous ,

Could you please create a simple sample to have a test and post your desired result due to it is hard to reproduce your problem?

Regards,

Daniel He

[Anonymous]

The first picture is how the transactions look like in dataset. In real life, there are more other type of transaction, like maintenance, quality etc. But I can filter them out easily.

This second picture is the result should look like if I choose the barcode "SG010A44FC" in a slicer in the report.

Here is the full sample:

Barcode	Description	Action	Process	Machine#	Time
OR2C1D02A2	Beet Juice	Load	Filtering	5	15/02/2019 6:27
OR2C1D32A2	Beet Juice	Load	Filtering	4	15/02/2019 6:29
OR2C1D02A4	Beet Juice	Load	Filtering	7	15/02/2019 6:32
OR2C1D02A5	Beet Juice	Load	Filtering	1	15/02/2019 6:37
FL03C42E64	Beet Juice	Unload	Filtering	5	15/02/2019 7:40
FL03C42E65	Beet Juice	Unload	Filtering	4	15/02/2019 7:43
FL03C42E66	Beet Juice	Unload	Filtering	7	15/02/2019 7:45
FL03C42E64	Beet Juice	Load	Purification	22	15/02/2019 7:45
FL03C42E65	Beet Juice	Load	Purification	7	15/02/2019 7:47
FL03C42E66	Beet Juice	Load	Purification	13	15/02/2019 7:49
FL03C42E67	Beet Juice	Unload	Filtering	1	15/02/2019 7:50
FL03C42E67	Beet Juice	Load	Purification	15	15/02/2019 7:54
PU6F4C26AA	Beet Juice	Unload	Purification	22	15/02/2019 9:33
PU6F4C26AB	Beet Juice	Unload	Purification	7	15/02/2019 9:35
PU6F4C26AC	Beet Juice	Unload	Purification	13	15/02/2019 9:37
PU6F4C26AD	Beet Juice	Unload	Purification	15	15/02/2019 9:42
PU6F4C26AA	Beet Juice	Load	Evaporation	22	15/02/2019 9:56
PU6F4C26AB	Beet Juice	Load	Evaporation	24	15/02/2019 9:57
PU6F4C26AC	Beet Juice	Load	Evaporation	23	15/02/2019 10:00
PU6F4C26AD	Beet Juice	Load	Evaporation	11	15/02/2019 10:05
EV046A23B1	Syrup	Unload	Evaporation	22	15/02/2019 12:36
EV046A23B2	Syrup	Unload	Evaporation	24	15/02/2019 12:38
EV046A23B3	Syrup	Unload	Evaporation	23	15/02/2019 12:40
EV046A23B1	Syrup	Load	Boiling	17	15/02/2019 12:41
EV046A23B2	Syrup	Load	Boiling	7	15/02/2019 12:43
EV046A23B3	Syrup	Load	Boiling	8	15/02/2019 12:43
EV046A23B4	Syrup	Unload	Evaporation	11	15/02/2019 12:45
EV046A23B4	Syrup	Load	Boiling	15	15/02/2019 12:49
BOB46D3412	Syrup	Unload	Boiling	17	15/02/2019 13:55
BOB46D3413	Syrup	Unload	Boiling	7	15/02/2019 13:57
BOB46D3414	Syrup	Unload	Boiling	8	15/02/2019 13:59
BOB46D3412	Syrup	Load	Centrifuging	8	15/02/2019 14:02
BOB46D3413	Syrup	Load	Centrifuging	10	15/02/2019 14:04
BOB46D3415	Syrup	Unload	Boiling	15	15/02/2019 14:04
BOB46D3414	Syrup	Load	Centrifuging	5	15/02/2019 14:06
BOB46D3415	Syrup	Load	Centrifuging	7	15/02/2019 14:12
CE23BAF566	Syrup	Unload	Centrifuging	8	15/02/2019 14:27
CE23BAF566	Syrup	Load	Drying	2	15/02/2019 14:29
CE23BAF567	Syrup	Unload	Centrifuging	10	15/02/2019 14:29
CE23BAF567	Syrup	Load	Drying	6	15/02/2019 14:31
CE23BAF568	Syrup	Unload	Centrifuging	5	15/02/2019 14:33
CE23BAF568	Syrup	Load	Drying	3	15/02/2019 14:35
CE23BAF569	Syrup	Unload	Centrifuging	7	15/02/2019 14:38
CE23BAF569	Syrup	Load	Drying	4	15/02/2019 14:40
DR42BA34ED	Suger	Unload	Drying	2	15/02/2019 16:12
DR42BA34EE	Suger	Unload	Drying	6	15/02/2019 16:14
DR42BA34EF	Suger	Unload	Drying	3	15/02/2019 16:17
DR42BA34F0	Suger	Unload	Drying	4	15/02/2019 16:22
DR42BA34ED	Suger	Load	Cooling	1	15/02/2019 17:10
DR42BA34EE	Suger	Load	Cooling	1	15/02/2019 17:12
DR42BA34EF	Suger	Load	Cooling	1	15/02/2019 17:22
DR42BA34F0	Suger	Load	Cooling	1	15/02/2019 17:27
SG010A44FC	Suger	Unload	Cooling	1	15/02/2019 17:45
SG010A44FD	Suger	Unload	Cooling	1	15/02/2019 17:47
SG010A44FE	Suger	Unload	Cooling	1	15/02/2019 17:48
SG010A44FF	Suger	Unload	Cooling	1	15/02/2019 17:53

Thank you!