Forum Discussion

rajeshapunu1234's avatar
rajeshapunu1234
Icon for Helper I rankHelper I
2 years ago
Solved

finding superset and subset

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 packag...
Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    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

     

rajeshapunu1234's avatar
rajeshapunu1234
Icon for Helper I rankHelper I
2 years ago

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's avatar
dufoq3
Icon for Community Champion rankCommunity Champion
2 years ago

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