Help rearranging dataset (pivot, lookup, or something else?)

[Matrovsky]

Sorry for the uninformative title; I'm not sure what to call the thing I'm trying to accomplish. I'm importing data from a PDF and hope to rearrange them into a more useful structure.

I have a list of widgets, grouped by the subwidgets they're made of. So the initial import looks something like this (formatting added for readability):
_____________________________________________________________________________________________
#SET#05
Widget1
Widget2
Widget12
Widget39

TotalQty Subwidget Description Details
Qty05.A SubwidgetA DescriptionA DetailsA
Qty05.B SubwidgetB DescriptionB DetailsB
Qty05.C SubwidgetC DescriptionC DetailsC
Qty05.D SubwidgetD DescriptionD DetailsD


#SET#14
Widget4
Widget19
Widget68

TotalQty Subwidget Description Details
Qty14.A SubwidgetA DescriptionA DetailsA
Qty14.B SubwidgetB DescriptionB DetailsB
Qty14.F SubwidgetF DescriptionF DetailsF


#SET#35
Widget41

TotalQty Subwidget Description Details
Qty35.A SubwidgetA DescriptionA DetailsA
Qty35.B SubwidgetC DescriptionC DetailsC
Qty35.F SubwidgetF DescriptionF DetailsF
Qty35.H SubwidgetH DescriptionH DetailsH
_____________________________________________________________________________________________

The end goal is a table of widgets (rows) vs. subwidgets (columns) indicating which are included in which:

Widget	Set	SubA	SubB	SubC	SubD	SubF	SubH
W1	05	TRUE	TRUE	TRUE	TRUE	FALSE	FALSE
W2	05	TRUE	TRUE	TRUE	TRUE	FALSE	FALSE
W12	05	TRUE	TRUE	TRUE	TRUE	FALSE	FALSE
W39	05	TRUE	TRUE	TRUE	TRUE	FALSE	FALSE
W4	14	TRUE	TRUE	FALSE	FALSE	TRUE	FALSE
W9	14	TRUE	TRUE	FALSE	FALSE	TRUE	FALSE
W68	14	TRUE	TRUE	FALSE	FALSE	TRUE	FALSE
W41	35	TRUE	FALSE	TRUE	FALSE	TRUE	TRUE

I've extracted a table showing the widgets & subwidgets associated with each set:

Set	Widgets	QTy	Subwidgets
05	W1	-	-
05	W2	-	-
05	W12	-	-
05	W39	-	-
05	-	Qty05.A	A
05	-	Qty05.B	B
05	-	Qty05.C	C
05	-	Qty05.D	D
14	W4	-	-
14	W9	-	-
14	W68	-	-
14	-	Qty14.A	A
14	-	Qty14.B	B
14	-	Qty14.F	F
35	W41	-	-
35	-	Qty35.A	A
35	-	Qty35.C	C
35	-	Qty35.F	F
35	-	Qty35.H	H

...and I can collapse those into unique lists, records, or tables as needed, and have used them to create the main structure of the desired table:

Widget	Set	SubA	SubB	SubC	SubD	SubF	SubH
W1	05
W2	05
W12	05
W39	05
W4	14
W9	14
W68	14
W41	35

...but then I don't know which lookup and/or pivot operations are needed to fill it with the correct values. Can anybody help?

Thank you in advance for any help you're willing to offer!

BONUS QUESTION: If possible, I would love to have the quantity of each subwidget needed instead of a Boolean, but I didn't want to distract from the main question in case it adds a bunch of complication:

Widget	Set	SubA	SubB	SubC	SubD	SubF	SubH
W1	05	Qty05.A/COUNT(Set05)	Qty05.B/COUNT(Set05)	Qty05.B/COUNT(Set05)	Qty05.B/COUNT(Set05)	0	0
W2	05	Qty05.A/COUNT(Set05)	Qty05.B/COUNT(Set050	Qty05.B/COUNT(Set05)	Qty05.B/COUNT(Set05)	0	0
W12	05	Qty05.A/COUNT(Set05)	Qty05.B/COUNT(Set05)	Qty05.B/COUNT(Set05)	Qty05.B/COUNT(Set05)	0	0
W39	05	Qty05.A/COUNT(Set05)	Qty05.B/COUNT(Set05)	Qty05.B/COUNT(Set05)	Qty05.B/COUNT(Set05)	0	0
W4	14	Qty14.A/COUNT(Set14)	Qty14.B/COUNT(Set14)	0	0	Qty14.B/COUNT(Set14)	0
W9	14	Qty14.A/COUNT(Set14)	Qty14.B/COUNT(Set14)	0	0	Qty14.B/COUNT(Set14)	0
W68	14	Qty14.A/COUNT(Set14)	Qty14.B/COUNT(Set14)	0	0	Qty14.B/COUNT(Set14)	0
W41	35	Qty35.A/COUNT(Set35)	0	Qty35.B/COUNT(Set35)	0	Qty35.B/COUNT(Set35)	Qty35.B/COUNT(Set35)

 

[AlienSx]

@Matrovsky , here is true and false part

let
    wdg = (tbl) => 
        [widgets = List.Select(tbl[Widgets], each _ <> "-"),
        subwidgets = List.Select(tbl[Subwidgets], each _ <> "-"), 
        z = Table.FromList(widgets, Splitter.SplitByNothing(), {"Widgets"} & subwidgets, true)][z],
    // Source is your extracted table
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content], 
    all_subw = List.Sort(List.Distinct(List.Select(Source[Subwidgets], each _ <> "-"))),
    group = Table.Group(Source, "Set", {"x", wdg}), 
    xpand = Table.ExpandTableColumn(group, "x", {"Widgets"} & all_subw), 
    result = Table.TransformColumns(xpand, {}, (x) => x ?? false)
in
    result

 

[alex78]

Hi @Matrovsky,
An attempt, Source is your extracted table.

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    cols = Table.ColumnNames(Source),
    a = Table.FillDown(Table.TransformColumns(Source, {} , (x)=> Replacer.ReplaceValue(x,"-",null)),{cols{1}}),
    b = Table.Group(a, cols{0}, {{"x", each 
        [ 
        x = List.Transform(List.Skip(Table.ToColumns(_)), List.RemoveNulls),
        y = {List.Range(x{0}, 0, List.Count(x{2})), List.Transform(x{1}, each Number.From(Text.Select(_, {"0".."9"}))), List.Transform(x{2}, each "Sub" & Text.From(_))},
        z = Table.FromColumns(y & {List.Repeat({List.Sum(y{1})}, List.Count(y{1}))}, List.Skip(cols) & {"x"})
        ][z]}}),
    c = Table.ExpandTableColumn(b, "x", List.Skip(cols) & {"x"}),
    d = Table.ExpandRecordColumn(Table.Group(c, {cols{1}, cols{0}}, {{"x", each let x = Table.AddColumn(_,"y", each [QTy]/[x]) 
        in Record.FromList(x[y], x[Subwidgets])}}), "x", List.Distinct (c[Subwidgets])),
    Sol = Table.TransformColumns(d, {} , (x)=> Replacer.ReplaceValue(x,null,0))
in
    Sol

Regards

 

[AlienSx]

bonus

let
    wdg = (tbl) => 
        [widgets = List.Select(tbl[Widgets], each _ <> "-"),
        subwidgets = List.Select(tbl[Subwidgets], each _ <> "-"),
        qty = List.Buffer(List.Select(tbl[QTy], each _ <> "-")), 
        z = Table.FromList(widgets, (x) => {x} & List.Transform(qty, (w) => w / List.Count(widgets)), {"Widgets"} & subwidgets, true)][z],
    // Source is your extracted table
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content], 
    all_subw = List.Sort(List.Distinct(List.Select(Source[Subwidgets], each _ <> "-"))),
    group = Table.Group(Source, "Set", {"x", wdg}), 
    xpand = Table.ExpandTableColumn(group, "x", {"Widgets"} & all_subw), 
    result = Table.TransformColumns(xpand, {}, (x) => x ?? false)
in
    result