Forum Discussion
Tracking Logistic Movements with Power BI
Good Morning All,
I have a table that report all the movements of materials from goods receipt to consumption.
Material are organized in handling units and they can moves from one handling unit to another one. Each movement is stored in a table and for each movement the table reports the source handling unit and the destination handling unit.
During the transformation of the table, in order to manage data with power bi, i need to add a 'Reference' column that reports the original source handling unit.
For example, for the case below (Source Handling Unit/Destination Handling Unit):
I would like to insert a dynamic column that for each row reports 1002913303 that is the original handling unit which has been moved into 1002922587.
Can you help me please??
Thanks and regards,
Mattia
This could be a good application of the PATH function on the DAX side; however, I suspect you'd have to do some additional data prep to make it work (it can be finicky). The PATH function is handy for reporting relationships and genealogy applications like this. There is also an M path version of PATH found here (but it doesn't scale well in my experience) -
https://docs.microsoft.com/en-us/dax/path-function-dax
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
8 Replies
You should be able to do that in Power Query. What have you tried so far?
Hi mattiadiena ,
I am not clear about your requirement and your logic , if possible could you please explain it in details? Then I will try to test this in my environment.
Please do mask sensitive data before uploading.
Thanks for your understanding and support.
Best Regards,
Zoe ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi dax ,
Thanks for your answer.
Here below you can find an example of the table i would like to create (The fourth column is what i would like to obtain).
Movement ID Source Handling Unit Destination HU Original Source HU 1 2005826606 2005826606 2005826606 2 2005826606 2005826606 2005826606 3 2005826606 2005826606 2005826606 4 2005826606 2005845174 2005826606 5 2005845174 2005845174 2005826606 6 2005845174 2005845174 2005826606 7 2005845174 0 2005826606 8 2005845174 0 2005826606 9 2005845174 0 2005826606 11 2005845174 0 2005826606 13 2005826608 2005826608 2005826608 14 2005826608 2005826608 2005826608 15 2005826608 2005826608 2005826608 16 2005826608 2005826608 2005826608 17 2005826608 2005826608 2005826608 18 2005826608 0 2005826608 19 2005826608 0 2005826608 20 2005826608 0 2005826608 26 2005826610 2005826610 2005826610 27 2005826610 2005826610 2005826610 28 2005826610 2005826610 2005826610 29 2005826610 2005853372 2005826610 30 2005853372 2005853372 2005826610 31 2005853372 2005853372 2005826610 32 2005853372 0 2005826610 33 2005853372 0 2005826610 34 2005853372 0 2005826610 35 2005853372 0 2005826610 36 2005853372 0 2005826610 37 2005853372 0 2005826610 38 2005853372 0 2005826610 39 2005853372 0 2005826610 40 2005853372 0 2005826610 41 2005853372 0 2005826610 42 2005853372 0 2005826610 43 2005853372 0 2005826610 Each row refers to a specific movement, which is characterized by a source Handling Unit and a destination Handling Unit.
There is always an unique original source handling unit and the movement can be beetween the same handling unit or to new handling units. Through i would like to insert a calculated column that, as the last column proposed above, reports for each row the original source handling unit number of that specific movement.
For example in the table above you can notice that the first three rows refers to movements beetween the handling unit 2005826606 and itself, for all rows the original source is 2005826606.
The fourth row reports a movement from 2005826606 to 2005845174 (New Hansdling Unit), the original source remains 2005826606.
From the fourth row the table report movents from handling unit 2005845174 to itself or to other destination, also for these cases the original source remains 2005826606.
I need to insert the a column that, by analizing the movements contained in the table, automatically defines for each row the original source handling unit.
In this way i could better analyze data and delete all the rows that are consequence of other handling unit.
I hope you can help me.
Thank you very much,
Mattia
The question makes sense. Something moves from A to B to C to D, and all along the way, you want each 'movement' to be able to report that A was the starting point of this journey.
Questions: Is each Movement related to a Material ID or some other identifier? Also, is there something in the Movement table to indicate the ORDER in which they happened? Maybe a Date/Time stamp, or Movement ID?
Assuming both answers are YES, then you should be able to do the following:
* Duplicate your Movement table.
* Do a Group By and get the FIRST Movement for each unique Material ID
* Join that to the original Movement table (MERGE) on Material ID and Movement Date (or Sequence or whatever) to get the Source Handling Unit.
* Back on your original query, MERGE it with the result from above on only Material ID, forget about the Date or Sequence, and pick up the Source Unit.
Hope this helps.
