Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
RTERCERO
Helper I
Helper I

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:

RTERCERO_1-1654648068438.png

Tried several options but none gave me the expected result.

 

Attaching sample data: https://we.tl/t-ZyiZIGvRbg 

 

Appreciate the help.

 

RT

4 REPLIES 4
danextian
Super User
Super User

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"

 

danextian_0-1654651285781.png

 

PBIX - https://drive.google.com/file/d/1svkpQFJo59tyW2AhdpCbk-nm392ZJizx/view?usp=sharing 

 





Dane Belarmino | Microsoft MVP | Proud to be a Super User!

Did I answer your question? Mark my post as a solution!


"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

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 

danextian
Super User
Super User

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.





Dane Belarmino | Microsoft MVP | Proud to be a Super User!

Did I answer your question? Mark my post as a solution!


"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

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

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors