create traceability based on the events

[zubair242]

Hi team,

I have a datasets which shows the transfer of commodity between different cells. What I want to see how this commodity is moving between different cells. I have data in tabular format and I want to convert it into something like pivot below.

Is this something that can be achieved in power query? Attached is the sample excel file data.

https://docs.google.com/spreadsheets/d/1zFw9TEkOMMfKuO246mX74gp8odALIhdB/edit?usp=sharing&ouid=11200...

[v-stephen-msft]

Hi @zubair242 ,

Pls has your problem been solved? If possible, please accept the helpful replies as solutions. More people will benefit from it. Thanks.

Best Regards,

Stephen Tao

[dufoq3 · Accepted solution]

Hi @zubair242, another solution:

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdCxCoNQDAXQXylvFkzuS7TmF7p2EwcHO3aw/w/VpygGgtPNkMMN6fvU1tAa8ugMz1Sl9zx+f59pXkYhpbzkK8uRnIbqNExGTYx4T70iGHV3SAkecXjeuryleKR60wR4pKYcI91RvqLGgACVhi3ZI4m+VxrWZI9ao+i8snx8b/gD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Action = _t, ID = _t, Source = _t, Destination = _t]),
    RenamedColumns = Table.SelectColumns(Table.RenameColumns(Source,{{"Source", "Parent"}, {"Destination", "Child"}}), {"Parent", "Child"}),
    Buffer = Table.Buffer(RenamedColumns),
    DistinctHighestParent = Table.Distinct(Table.SelectRows(Table.SelectColumns(Buffer, {"Parent"}), each not List.Contains(Buffer[Child], [Parent]))),

    Ad_Hierarchy = Table.AddColumn(DistinctHighestParent, "Hierarchy", each 
        [ lg = List.Generate(
                    ()=> [ i = 0, nextChild = List.Split(Table.SelectRows(Buffer, (x)=> x[Parent] = [Parent])[Child], 1) ],
                    each List.NonNullCount(List.Combine(List.Transform([nextChild], (x)=> List.Skip(x, [i])))) <> 0,
                    each [ i = [i] +1, 
                           nextChild = List.Combine(List.Transform([nextChild], (y)=> List.Transform([ a = Table.SelectRows(Buffer, (x)=> x[Parent] = List.Last(y))[Child], b = if List.IsEmpty(a) then {null} else a ][b], (z)=> y & {z}))) ],
                    each [[nextChild]] ), 
          toTable = Table.FromRows(List.Last(lg)[nextChild])
        ][toTable] ),
    ExpandedHierarchy = Table.ExpandTableColumn(Ad_Hierarchy, "Hierarchy", Table.ColumnNames(Ad_Hierarchy{0}[Hierarchy])),
    ColNames = Table.ColumnNames(ExpandedHierarchy),
    StepBack = ExpandedHierarchy,
    ChangedType = Table.TransformColumnTypes(StepBack, List.Transform(ColNames, (x)=> {x, type text})),
    RenamedColumns1 = Table.RenameColumns(ChangedType,
        [ a = {"Parent"} & List.Transform({1..List.Count(ColNames)-1}, (x)=> "Child " & Text.From(x)),
          b = List.Zip({ColNames, a})
        ][b] ),
    Transformed = Table.FromColumns(List.Transform(Table.ToColumns(RenamedColumns1), List.Distinct), Value.Type(Table.FirstN(RenamedColumns1, 0)))
in
    Transformed

[Omid_Motamedise]

hat is the logic for the levels??

[zubair242]

Logic of levels is simply movements for commodity from one cell to other. As, I have provided the table, commodity is moved through different cells. We want to track the movement like if we have commodity K2204, what is the source/chain?

[AlienSx]

@zubair242 you lost K3502 -> K3504 in your sample

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    pairs = List.Buffer(List.Zip({Source[Source], Source[Destination]})),
    replace = (lst, pair) => 
        [idx = List.PositionOf(lst, pair, Occurrence.First, (c, v) => List.Last(c) = pair{0}),
        repl = if idx <> - 1 
            then List.ReplaceRange(lst, idx, 1, {lst{idx} & {pair{1}}})
            else lst & 
                (
                    (z) => {List.Repeat({null}, z) & {pair{1}}}
                )
                (List.PositionOf(pairs, pair{0}, Occurrence.First, (c, v) => c{0} = v) + 1)
        ][repl],
    gen = List.Generate(
        () => [i = 0, pair = {pairs{0}}, res = pair],
        (x) => x[i] < List.Count(pairs),
        (x) => [i = x[i] + 1, pair = pairs{i}, res = replace(x[res], pair)],
        (x) => x[res]
    ),
    res = Table.FromColumns(List.Zip(List.Last(gen)))
in
    res

[lbendlin]

@AlienSx Not only that but the dual pathways from K3401 to K3502 are lost as well.

I think this should he represented by a Network Graph chart rather than a Sankey.  And definitely not by a table.

[lbendlin]

Have you considered using a Sankey Diagram chart instead?

[zubair242]

Hi @lbendlin , thank you for providing a good idea to represent this in the sankey visual. However, it is an audit requirement, so user will be looking to see this in the tabular format. Thanks!