PQ - How to add columns from a list? each item in a list means a new column

[sandy666]

Source data

Financial StatementMain SubDetailed Sub20162017201820192020202120222023202420252026

(1) Balance Sheet[1-1] Non Current AssetsFixed assets
14
11
6
47
44
49
5
99
65
33
122

(1) Balance Sheet[1-1] Non Current AssetsInvestments available for sale
1
30
33
39
29
7
7
11
5
66
111

(1) Balance Sheet[1-1] Non Current Assetsintangible assets
34
26
17
3
46
13
23
3
(55)
99
100

(1) Balance Sheet[1-1] Non Current AssetsDeferred tax assets
51
43
25
16
40
8
31
49
(115)
132
89

(1) Balance Sheet[6-1] Balance Sheet TotalsTotal non-current assets
100
110
81
105
159
77
41
33
(175)
165
78

(1) Balance Sheet[2-1] Current AssetsInventory
37
48
45
42
52
31
14
42
(235)
198
67

(1) Balance Sheet[2-1] Current AssetsCash and the like
36
2
22
38
10
50
8
66
(295)
231
56

(1) Balance Sheet[2-1] Current AssetsWork in progress
32
24
5
35
41
4
55
999
(355)
264
45

(1) Balance Sheet[2-1] Current AssetsClient
20
28
19
21
15
9
33
666
(415)
297
34

(1) Balance Sheet[2-1] Current AssetsPrepaid expenses and other debit balances
53
12
54
18
23
27
44
777
(475)
330
23

(1) Balance Sheet[6-1] Balance Sheet TotalsTotal current assets
178
114
145
154
141
121
50
111
(535)
363
11

(1) Balance Sheet[6-1] Balance Sheet Totalstotal assets
278
224
226
259
300
198
300
500
(595)
396
222



Rule1: =Table1[@2016]/$D$13
Rule2: if additional column will be added to the source table , new column should appear in the result table automatically
Rule3: if any of value will be blank - the result will be less by the number of empty cells
Rule4: if last value will be 0 you'll get #NUM! error (remember, don't divide by zero!!!)
Rule5: if any of value will be space or any text character- result will be blank column


see next post

[sandy666]

Result


A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U
V
W
X
Y

1Financial StatementMain SubDetailed Sub20162017201820192020202120222023202420252026201 6V2017V2018V2019V2020V2021V2022V2023V2024V2025V202 6V


2(1) Balance Sheet[1-1] Non Current AssetsFixed assets
14
11
6
47
44
49
5
99
65
33
122
5%
5%
3%
18%
15%
25%
2%
20%
-11%
8%
55%


3(1) Balance Sheet[1-1] Non Current AssetsInvestments available for sale
1
30
33
39
29
7
7
11
5
66
111
0%
13%
15%
15%
10%
4%
2%
2%
-1%
17%
50%


4(1) Balance Sheet[1-1] Non Current Assetsintangible assets
34
26
17
3
46
13
23
3
-55
99
100
12%
12%
8%
1%
15%
7%
8%
1%
9%
25%
45%


5(1) Balance Sheet[1-1] Non Current AssetsDeferred tax assets
51
43
25
16
40
8
31
49
-115
132
89
18%
19%
11%
6%
13%
4%
10%
10%
19%
33%
40%


6(1) Balance Sheet[6-1] Balance Sheet TotalsTotal non-current assets
100
110
81
105
159
77
41
33
-175
165
78
36%
49%
36%
41%
53%
39%
14%
7%
29%
42%
35%


7(1) Balance Sheet[2-1] Current AssetsInventory
37
48
45
42
52
31
14
42
-235
198
67
13%
21%
20%
16%
17%
16%
5%
8%
39%
50%
30%


8(1) Balance Sheet[2-1] Current AssetsCash and the like
36
2
22
38
10
50
8
66
-295
231
56
13%
1%
10%
15%
3%
25%
3%
13%
50%
58%
25%


9(1) Balance Sheet[2-1] Current AssetsWork in progress
32
24
5
35
41
4
55
999
-355
264
45
12%
11%
2%
14%
14%
2%
18%
200%
60%
67%
20%


10(1) Balance Sheet[2-1] Current AssetsClient
20
28
19
21
15
9
33
666
-415
297
34
7%
13%
8%
8%
5%
5%
11%
133%
70%
75%
15%


11(1) Balance Sheet[2-1] Current AssetsPrepaid expenses and other debit balances
53
12
54
18
23
27
44
777
-475
330
23
19%
5%
24%
7%
8%
14%
15%
155%
80%
83%
10%


12(1) Balance Sheet[6-1] Balance Sheet TotalsTotal current assets
178
114
145
154
141
121
50
111
-535
363
11
64%
51%
64%
59%
47%
61%
17%
22%
90%
92%
5%


13(1) Balance Sheet[6-1] Balance Sheet Totalstotal assets
278
224
226
259
300
198
300
500
-595
396
222
100%
100%
100%
100%
100%
100%
100%
100%
100%
100%
100%

[sandy666]

// Table1B
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Index = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type)
in
Index


// Table1P
let
Base = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
RCC = Table.RemoveColumns(Base,{"Financial Statement", "Main Sub", "Detailed Sub"}),
USC = Table.Unpivot(Source, Table.ColumnNames(RCC), "Year", "Value"),
RR = Table.ReverseRows(USC),
Distinct = Table.Distinct(RR, {"Year"}),
Asc = Table.Sort(Distinct,{{"Year", Order.Ascending}}),
TSC = Table.SelectColumns(Asc,{"Year", "Value"}),
PT = Table.Pivot(TSC, List.Distinct(TSC[Year]), "Year", "Value"),

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Index = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
UOC = Table.UnpivotOtherColumns(Index, {"Financial Statement", "Main Sub", "Detailed Sub", "Index"}, "Year", "Value"),
TSAsc = Table.Sort(UOC,{{"Year", Order.Ascending}, {"Index", Order.Ascending}}),
RC = Table.RemoveColumns(TSAsc,{"Index"}),
Idx = Table.AddIndexColumn(RC, "Index", 0, 1, Int64.Type),
Divide = Table.AddColumn(Idx, "Divide", each Idx{[Index]}[Value]/TSC{[Year=[Year]]}[Value]),
Grp = Table.Group(Divide, {"Year"}, {{"Count", each _, type table [Financial Statement=text, Main Sub=text, Detailed Sub=text, Year=text, Value=number, Index=number, Divide=nullable number]}}),
Suffix = Table.TransformColumns(Grp, {{"Year", each _ & "V", type text}}),
Lst = Table.AddColumn(Suffix, "Lst", each [Count][Divide]),
RCCount = Table.RemoveColumns(Lst,{"Count"}),
Ext = Table.TransformColumns(RCCount, {"Lst", each Text.Combine(List.Transform(_, Text.From), "="), type text}),
Split = Table.SplitColumn(Ext, "Lst", Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv), {"Lst.1", "Lst.2", "Lst.3", "Lst.4", "Lst.5", "Lst.6", "Lst.7", "Lst.8", "Lst.9", "Lst.10", "Lst.11", "Lst.12"}),
Dec = Table.TransformColumnTypes(Split,{{"Lst.1", type number}, {"Lst.2", type number}, {"Lst.3", type number}, {"Lst.4", type number}, {"Lst.5", type number}, {"Lst.6", type number}, {"Lst.7", type number}, {"Lst.8", type number}, {"Lst.9", type number}, {"Lst.10", type number}, {"Lst.11", type number}, {"Lst.12", type number}}),
Trans = Table.Transpose(Dec),
Promo = Table.PromoteHeaders(Trans, [PromoteAllScalars=true]),
AddIndex = Table.AddIndexColumn(Promo, "Index", 1, 1, Int64.Type)
in
AddIndex


// Join
let
Source = Table.NestedJoin(Table1B, {"Index"}, Table1P, {"Index"}, "Table", JoinKind.Inner),
RI = Table.RemoveColumns(Source,{"Index"}),
Exp = Table.ExpandTableColumn(RI, "Table", List.RemoveItems(Table.ColumnNames(RI[Table]{0}), {"Financial Statement"})),
RI1 = Table.RemoveColumns(Exp,{"Index"})
in
RI1