The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
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
Hi @RTERCERO ,
I am honestly unclear what you want to achieve. Which one is the Master SKU? Which one is the alternative? Which one is the Group? Which among the table is your expected result? Are you trying to combine texts? Etc. Please elaborate.
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