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 an...
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
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