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