finding superset and subset

[rajeshapunu1234]

Hi team
i am having table called "PACKAGE" which is having two coumns as below

now wanted to find superset package and supset package from the table
note: the above is sample data the package is more the 700 and need to find the superset and subset package from all the 700 

can someone please help me out to get it done?

[rajeshapunu1234]

Sir actually my need to to identify the superset and supset packages example

from above table i want to have list of superset pakcage in first column its subset package in second column and if any subset package is coming from the another subset should come in the third column with the same package row as follow

can u please help me to acheive it?

[dufoq3]

Hi @rajeshapunu1234,

for future requests: provide sample data as table so we can copy/paste and don't forget to provide also expected result please.

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkhMzk5MT1UwVNJRCkktLgEyYnWwCBthFzZGETbCbogRdkOMCBhigiJsDBM2xS5shiJsgl21CQHVqO42RTIkFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [p = _t, t = _t]),
    GroupedRows = Table.Group(Source, {"p"}, {{"t1", each [t], type list}}),
    Ad_Subset = Table.AddColumn(GroupedRows, "Subset", each List.RemoveItems(List.RemoveNulls(List.Accumulate(
     { 0..Table.RowCount(GroupedRows) -1 },
     {},
     (s,c)=> s & { if List.ContainsAll([t1], GroupedRows{c}[t1]) then GroupedRows{c}[p] else null }
     )), {[p]}), type list),
    FilterSupersets = Table.SelectRows(Ad_Subset, each not List.Contains(List.Combine(Ad_Subset[Subset]), [p])),
    Ad_FinalTable = Table.AddColumn(FilterSupersets, "FinalTable", each List.Accumulate( 
     List.Zip({{0..List.Count([Subset]) -1}, [Subset]}),
     #table(type table[Superset = text], {{[p]}}),
     (s,c)=> Table.AddColumn(s, "Subset" & Text.From(c{0} +1), each c{1}, type text)), type table),
    FinalTable = Table.Combine(Ad_FinalTable[FinalTable])
in
    FinalTable

[rajeshapunu1234]

Hi it is working for small set of data but not working for the huge data
like i am having 700 packages with above 4000 test

I used same column names but with your code it is taking time but not giving result

and i dont know how to attach excel here can you please guide me to attach so that you can help me out

[dufoq3]

You can upload your file to google drive or one drive and share link with us. Don't for get to grant public permissions.

[rajeshapunu1234]

https://docs.google.com/spreadsheets/d/15I2nioKiyWlaq0jChc5Tc9yy49pJ8RlX/edit?usp=sharing&ouid=10973...

@dufoq3

kindly find the link above
thanks

[dufoq3]

Hi @rajeshapunu1234, I tried my best.

• If you try code below you will see that for first 1000 rows it finishes in a few seconds wit 14 subset columns
• With first 2000 rows, it takes around 30seconds and 47 subset columns.
• With first 3000 rows, it takes 1min 50sec and 77 subset columns
• I tried to run my query with all 4806 rows, it takes 9minutes and 169 subset columns (327 rows).
• You need to understand that the more lines there are, the number of combinations grows exponentially because we have to compare every single row with all other rows. To be honest I'm not sure if there is such way to speed this up. Maybe someone else could help.

You can download the result here.

Old code removed

[dufoq3 · Accepted solution]

Hello again @rajeshapunu1234, with @jgeddes help you can find new code here:

You can decide whether you want to include supersets with 0 subsets or not:

Now this query takes around a minute on my PC with whole dataset (4806 rows) and finishes with 718 rows and 303 subset columns

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkhMzk5MT1UwVNJRCkktLgEyYnWwCBthFzZGETbCbogRdkOMCBhigiJsChM2xS5shl0Y1SUm2A0xJiCMarYZdtVmBFQbYhcG+jIWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [p = _t, t = _t]),
    // use "yes" or "no"
    #"IncludeSupersetsWithoutSubets?" = "yes",
    StepBack = Source
,
    RenamedColumns = Table.RenameColumns(StepBack,{{"p", "Superset"}, {"t", "Sub"}}),
    GroupedRows = Table.Group(RenamedColumns, {"Superset"}, {{"Sub", each [Sub], type list}}),
    Ad_PrevTable = Table.AddColumn(GroupedRows, "PrevTable", each GroupedRows, type table),
    ExpandedGroupedRows = Table.ExpandTableColumn(Ad_PrevTable, "PrevTable", {"Superset", "Sub"}, {"Superset2", "Sub2"}),
    Ad_SubCheck = Table.AddColumn(ExpandedGroupedRows, "Sub Check", each 
       if [Superset] = [Superset2] then true
       //else if not List.Contains([Sub], [Sub2]{0}) then false
       else if List.ContainsAll([Sub], [Sub2]) then true else false
     , type logical),
    FilteredRows = Table.SelectRows(Ad_SubCheck, each ([Sub Check] = true)),
    Ad_Order = Table.AddColumn(FilteredRows, "Order", each List.Count([Sub2]), Int64.Type),
    RemovedOtherColumns = Table.SelectColumns(Ad_Order,{"Superset", "Superset2", "Order"}),
    GroupedRows2 = Table.Group(RemovedOtherColumns, {"Superset"}, {{"Subsets", each 
       Table.Transpose(Table.RemoveFirstN(Table.SelectColumns(Table.Sort(_, {{"Order", Order.Descending}}), {"Superset2"}), 1))
 , type table}}),
    Ad_ColCount = Table.AddColumn(GroupedRows2, "ColCount", each Table.ColumnCount([Subsets]), Int64.Type),
    // Based on "IncludeSupersetsWithoutSubets?" parameter
    FilteredColCountParameter = Table.SelectRows(Ad_ColCount, each if Text.Trim(Text.Lower(#"IncludeSupersetsWithoutSubets?")) = "yes" then [ColCount] <> -1 else [ColCount] <> 0),
    ColNames = [ a = List.Max(FilteredColCountParameter[ColCount]), 
    b = List.Buffer(List.Generate(
            () => 1,
            each _ <= a,
            each _ +1,
            each { "Column" & Text.From(_), "Subset" & Text.From(_) } ))
  ][b],
    StepBack2 = FilteredColCountParameter,
    RemovedColumns = Table.RemoveColumns(StepBack2,{"ColCount"}),
    ExpandedSubsets = Table.ExpandTableColumn(RemovedColumns, "Subsets", List.Transform(ColNames, each _{0}), List.Transform(ColNames, each _{1}))
in
    ExpandedSubsets

[Vijay_A_Verma]

If I understand correctly, a simple group operation should meet your needs. See the code below

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKjBU0lEqMVSK1QGyjUBsIwjbGIlthK7GGMI2QRI3hKqPBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [p = _t, t = _t]),
    #"Grouped Rows" = Table.Group(Source, {"p"}, {{"t", each Text.Combine([t], ", ")}})
in
    #"Grouped Rows"

[rajeshapunu1234]

where can i have another column which mention which package is superset and which package is subest of the superset with identification?

[rajeshapunu1234]

in the mean time want to identify the unique package as well
can you please support here in both need?

[Vijay_A_Verma]

column p is superset which is unique and column t is subset. If you need, you can rename the columns. Use the revised code where I have sorted and prepared unique list of subsets.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKjBU0lEqMVSK1QGyjUBsIwjbGIlthEONCZK4IVQ8FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [p = _t, t = _t]),
    #"Grouped Rows" = Table.Group(Source, {"p"}, {{"Subset", each Text.Combine(List.Sort(List.Distinct([t])), ", ")}})
in
    #"Grouped Rows"