PQ-Transposing Data and Splitting Cells

[sandy666]

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.


TypeScore 2TypeScore1Score2

AABBAA
0
1

0-70-5
1AA
1
1

8-106-7
2AA
2
1

11-148-9
3AA
3
1

15-1910-11
4AA
4
1

20-2412-13
5AA
5
1

25-2914-15
6AA
6
1

30-3516-18
7AA
7
1

36-4019-20
8AA
8
2

41-4421-22
9AA
9
2

45-4823-23
10AA
10
2

49-5324-25
11AA
11
3

54-5626-26
12AA
12
3

57-5827-28
13AA
13
3

59-6029-29
14AA
14
3

61-6230-30
15AA
15
4

63-6331-31
16AA
16
4

64-6432-32
17AA
17
4

65-6533-33
18AA
18
4

66-6634-36
19AA
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


// 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