Forum Discussion
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...
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
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
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