Forum Discussion
All items related between 2 columns
Hello,
I have a set of references and intend to define a "PRINCIPAL ITEM" to group related "Alternative SKU's" I will do not have a specific criteria to select the "PRINCIPAL ITEM" among the group so I can be any as long as the group consider all relationships.
The issue am having is finding all related SKU's. so I can group them.
This is a reference from initial table to expected result:
Tried several options but none gave me the expected result.
Attaching sample data: https://we.tl/t-ZyiZIGvRbg
Appreciate the help.
RT
4 Replies
Hello danextian ,
Sorry if I did not expressed well, I made the changes to the first post,
Answering your specific questions:
Which one is the Master SKU?
Can be any of the ones in the related group, I just need one as a reference.
*changed the name to "Principal Item" to make it more comprehensable.
Which one is the alternative?Any related item even if its indirect
Which one is the Group?
All related its even if its indirect, including the "Principal Item", I grouped them by color to show where each set came from
Which among the table is your expected result?It is now labeled "FINAL TABLE"
Are you trying to combine texts?
Yes, all related items from both columns on "INITIAL TABLE"
Thanks for the promt response
RT
Hi RTERCERO
For my solution to work, there should be an indicator which group each row belongs to. Power Query cannot read colors. So I tagged as 1 those in green and the others 2. Please see below M Script used, screenshot fo the result and sample PBIX.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXICYkOlWB0IzxmF5wLngVRFoPAiUXhRcB7IBFcUnhucBzLBF85zB7K8gdgIzAsHstwhvFgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [BASE = _t, ALT = _t, Group = _t]), #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type), #"Changed Type" = Table.TransformColumnTypes(#"Added Index",{{"BASE", type text}, {"ALT", type text}, {"Group", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Group"}, {{"Grouped", each _, type table [BASE=nullable text, ALT=nullable text, Group=nullable number]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Min Index Per Group", each let x = Table.Group([Grouped], {"Group"}, {{"Min", each List.Min([Index]), type number}}) in x[Min]{0}), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Combined", each let base_alt = List.Combine({[Grouped][BASE], [Grouped][ALT]}), unique = List.Distinct(base_alt) in Text.Combine( List.Sort(unique), ","), type text), #"Expanded Grouped" = Table.ExpandTableColumn(#"Added Custom1", "Grouped", {"BASE", "ALT", "Index"}, {"BASE", "ALT","Index"}), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Grouped",{{"Index", Int64.Type}, {"Min Index Per Group", Int64.Type}}), #"Added Custom2" = Table.AddColumn(#"Changed Type1", "SET", each if [Min Index Per Group]=[Index] then [Combined] else null, type text), #"Filtered Rows" = Table.SelectRows(#"Added Custom2", each [SET] <> null and [SET] <> ""), #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"BASE", "SET"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"BASE", "SET PRINCIPAL ITEM"}}) in #"Renamed Columns"PBIX - https://drive.google.com/file/d/1svkpQFJo59tyW2AhdpCbk-nm392ZJizx/view?usp=sharing
danextiann ,Thanks for the alternative, but the purpose of the query Im looking to "Generate" the traceability of relations and create the groups by itself .Im thinking could be something using Iterations. but in my research haven'tt found something that works yet.
Best Regards
RT
