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

**sandy666** · 07-11-2023, 03:58 AM

Source data

Financial Statement	Main Sub	Detailed Sub	2016	2017	2018	2019	2020	2021	2022	2023	2024	2025	2026
(1) Balance Sheet	[1-1] Non Current Assets	Fixed assets	14	11	6	47	44	49	5	99	65	33	122
(1) Balance Sheet	[1-1] Non Current Assets	Investments available for sale	1	30	33	39	29	7	7	11	5	66	111
(1) Balance Sheet	[1-1] Non Current Assets	intangible assets	34	26	17	3	46	13	23	3	(55)	99	100
(1) Balance Sheet	[1-1] Non Current Assets	Deferred tax assets	51	43	25	16	40	8	31	49	(115)	132	89
(1) Balance Sheet	[6-1] Balance Sheet Totals	Total non-current assets	100	110	81	105	159	77	41	33	(175)	165	78
(1) Balance Sheet	[2-1] Current Assets	Inventory	37	48	45	42	52	31	14	42	(235)	198	67
(1) Balance Sheet	[2-1] Current Assets	Cash and the like	36	2	22	38	10	50	8	66	(295)	231	56
(1) Balance Sheet	[2-1] Current Assets	Work in progress	32	24	5	35	41	4	55	999	(355)	264	45
(1) Balance Sheet	[2-1] Current Assets	Client	20	28	19	21	15	9	33	666	(415)	297	34
(1) Balance Sheet	[2-1] Current Assets	Prepaid expenses and other debit balances	53	12	54	18	23	27	44	777	(475)	330	23
(1) Balance Sheet	[6-1] Balance Sheet Totals	Total current assets	178	114	145	154	141	121	50	111	(535)	363	11
(1) Balance Sheet	[6-1] Balance Sheet Totals	total 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** · 07-11-2023, 03:59 AM

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
1	Financial Statement	Main Sub	Detailed Sub	2016	2017	2018	2019	2020	2021	2022	2023	2024	2025	2026	2016V	2017V	2018V	2019V	2020V	2021V	2022V	2023V	2024V	2025V	2026V
2	(1) Balance Sheet	[1-1] Non Current Assets	Fixed 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 Assets	Investments 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 Assets	intangible 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 Assets	Deferred 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 Totals	Total 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 Assets	Inventory	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 Assets	Cash 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 Assets	Work 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 Assets	Client	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 Assets	Prepaid 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 Totals	Total 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 Totals	total assets	278	224	226	259	300	198	300	500	-595	396	222	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%	100%

**sandy666** · 07-11-2023, 04:01 AM

Code:

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

Code:

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

Code:

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