Forum Discussion
Solved
create traceability based on the events
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.
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
8 Replies
Have you considered using a Sankey Diagram chart instead?
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 Transformedhat is the logic for the levels??
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?
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
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
