PQ-Transposing Data and Splitting Cells

Quote:

---

I'm currently working with a table of student scores for different tests (denoted in columns A2 through E2) that I need to transpose and assign certain values to certain numbers.
Essentially, I have one column in a table that reads 0-7 (Cell A3) and another value in column F3 that indicates the number to be assigned to that range.
I would need a way to transpose cell A3 so it splits into single digits from 0 through 7 (as opposed to being a range in one cell). I would then need to assign a 1 to that spread of numbers 0 through 7.

---

Type			Score 2		Type	Score1	Score2
AA	BB				AA	0	1
0-7	0-5		1		AA	1	1
8-10	6-7		2		AA	2	1
11-14	8-9		3		AA	3	1
15-19	10-11		4		AA	4	1
20-24	12-13		5		AA	5	1
25-29	14-15		6		AA	6	1
30-35	16-18		7		AA	7	1
36-40	19-20		8		AA	8	2
41-44	21-22		9		AA	9	2
45-48	23-23		10		AA	10	2
49-53	24-25		11		AA	11	3
54-56	26-26		12		AA	12	3
57-58	27-28		13		AA	13	3
59-60	29-29		14		AA	14	3
61-62	30-30		15		AA	15	4
63-63	31-31		16		AA	16	4
64-64	32-32		17		AA	17	4
65-65	33-33		18		AA	18	4
66-66	34-36		19		AA	19	4
					AA	20	5
					AA	21	5
					AA	22	5
					AA	23	5
					AA	24	5
					AA	25	6
					AA	26	6
					AA	27	6
					AA	28	6
					AA	29	6
					AA	30	7
					AA	31	7
					AA	32	7
					AA	33	7
					AA	34	7
					AA	35	7
					AA	36	8
					AA	37	8
					AA	38	8
					AA	39	8
					AA	40	8

and so on... green table is much longer so you'll need to test it yourself

Code:

---

// Table1AA
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Split = Table.SplitColumn(Table.RemoveColumns(Source,{"BB"}), "AA", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"AA.1", "AA.2"}),
    Number = Table.TransformColumnTypes(Split,{{"AA.1", Int64.Type}, {"AA.2", Int64.Type}}),
    AA = Table.AddColumn(Table.AddIndexColumn(Number, "Score2", 1, 1, Int64.Type), "Type", each "AA"),
    Score1 = Table.AddColumn(AA, "Score1", each {[AA.1]..[AA.2]}),
    TSC = Table.SelectColumns(Table.ExpandListColumn(Score1, "Score1"),{"Type", "Score1", "Score2"})
in
    TSC

// Table1BB
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Split = Table.SplitColumn(Table.RemoveColumns(Source,{"AA"}), "BB", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"BB.1", "BB.2"}),
    Number = Table.TransformColumnTypes(Split,{{"BB.1", Int64.Type}, {"BB.2", Int64.Type}}),
    BB = Table.AddColumn(Table.AddIndexColumn(Number, "Score2", 1, 1, Int64.Type), "Type", each "BB"),
    Score1 = Table.AddColumn(BB, "Score1", each {[BB.1]..[BB.2]}),
    TSC = Table.SelectColumns(Table.ExpandListColumn(Score1, "Score1"),{"Type", "Score1", "Score2"})
in
    TSC

// Append1
let
    Source = Table.Combine({Table1AA, Table1BB}),
    Type = Table.TransformColumnTypes(Source,{{"Type", type text}, {"Score1", Int64.Type}, {"Score2", Int64.Type}})
in
    Type

---