Power Query: Apply a Transformation to a Dynamic Column Range

[takoyaki]

Hello all,

I've written a transformation to look up a column header in a table and return the number of hours corresponding to that value. The original value in the cell is then divided by this number. The amount of header columns and their names can vary depending on the workbook so it doesn't make practical sense to repeat this transformation for every single column in the table. I'm wondering if there is a way this transformation can be applied to all columns starting with the first column to the right of Column1 ((index 1) and to the last column at the far right (index 12). I believe it would be best to refer to them by their index number rather than their name, if possible.

The top table is how the data originally looks, followed by the lookup table, and the resulting table after the transformation is applied to column 1/1/2022. Any help is greatly appreciated!

M-Code so far:

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"1/1/2022", Int64.Type}, {"2/1/2022", Int64.Type}, {"3/1/2022", Int64.Type}, {"4/1/2022", Int64.Type}, {"5/1/2022", Int64.Type}, {"6/1/2022", Int64.Type}, {"7/1/2022", Int64.Type}, {"8/1/2022", Int64.Type}, {"9/1/2022", Int64.Type}, {"10/1/2022", Int64.Type}, {"11/1/2022", Int64.Type}, {"12/1/2022", Int64.Type}}),
    #"Divided Column" = Table.TransformColumns(#"Changed Type", {{"1/1/2022", each _ / Hours[Hours]{List.PositionOf(Hours[Period],Date.FromText(Table.ColumnNames(#"Changed Type"){Table.Schema(#"Changed Type"){[Name="1/1/2022"]}[Position]}))}}})
in
    #"Divided Column"

 

[JGordon11]

let
    tbl1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tbl2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    tbl3 = Table.TransformColumnTypes(tbl2,{{"Period", type date}}),
    tbl4 = List.Accumulate(List.Skip(Table.ColumnNames(tbl1)), tbl1, (s,c)=> 
        Table.TransformColumns(s, {{c, each _/tbl3{[Period = Date.From(c)]}[Hours]}}))
in
    tbl4

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Column1	1/1/2022	2/1/2022	3/1/2022	4/1/2022	5/1/2022	6/1/2022	7/1/2022	8/1/2022	9/1/2022	10/1/2022	11/1/2022	12/1/2022
2	a	43	32	34	17	57	60	1	41	55	37	3	21
3	b	42	38	18	44	39	50	40	15	49	30	52	22
4	c	56	11	51	58	9	59	25	24	36	27	29	6
5	d	28	23	45	12	47	19	13	54	10	2	53	35
6	e	4	26	20	48	16	7	33	8	14	46	5	31
7
8	Period	Hours
9	1/1/2022	205
10	2/1/2022	218
11	3/1/2022	156
12	4/1/2022	135
13	5/1/2022	162
14	6/1/2022	184
15	7/1/2022	101
16	8/1/2022	160
17	9/1/2022	156
18	10/1/2022	101
19	11/1/2022	127
20	12/1/2022	101
21
22	Column1	1/1/2022	2/1/2022	3/1/2022	4/1/2022	5/1/2022	6/1/2022	7/1/2022	8/1/2022	9/1/2022	10/1/2022	11/1/2022	12/1/2022
23	a	0.209756098	0.146788991	0.217948718	0.125925926	0.351851852	0.326086957	0.00990099	0.25625	0.352564103	0.366336634	0.023622047	0.207920792
24	b	0.204878049	0.174311927	0.115384615	0.325925926	0.240740741	0.27173913	0.396039604	0.09375	0.314102564	0.297029703	0.409448819	0.217821782
25	c	0.273170732	0.050458716	0.326923077	0.42962963	0.055555556	0.320652174	0.247524752	0.15	0.230769231	0.267326733	0.228346457	0.059405941
26	d	0.136585366	0.105504587	0.288461538	0.088888889	0.290123457	0.10326087	0.128712871	0.3375	0.064102564	0.01980198	0.417322835	0.346534653
27	e	0.019512195	0.119266055	0.128205128	0.355555556	0.098765432	0.038043478	0.326732673	0.05	0.08974359	0.455445545	0.039370079	0.306930693
28
Sheet1

 

[takoyaki]

let
    tbl1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tbl2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    tbl3 = Table.TransformColumnTypes(tbl2,{{"Period", type date}}),
    tbl4 = List.Accumulate(List.Skip(Table.ColumnNames(tbl1)), tbl1, (s,c)=>
        Table.TransformColumns(s, {{c, each _/tbl3{[Period = Date.From(c)]}[Hours]}}))
in
    tbl4

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Column1	1/1/2022	2/1/2022	3/1/2022	4/1/2022	5/1/2022	6/1/2022	7/1/2022	8/1/2022	9/1/2022	10/1/2022	11/1/2022	12/1/2022
2	a	43	32	34	17	57	60	1	41	55	37	3	21
3	b	42	38	18	44	39	50	40	15	49	30	52	22
4	c	56	11	51	58	9	59	25	24	36	27	29	6
5	d	28	23	45	12	47	19	13	54	10	2	53	35
6	e	4	26	20	48	16	7	33	8	14	46	5	31
7
8	Period	Hours
9	1/1/2022	205
10	2/1/2022	218
11	3/1/2022	156
12	4/1/2022	135
13	5/1/2022	162
14	6/1/2022	184
15	7/1/2022	101
16	8/1/2022	160
17	9/1/2022	156
18	10/1/2022	101
19	11/1/2022	127
20	12/1/2022	101
21
22	Column1	1/1/2022	2/1/2022	3/1/2022	4/1/2022	5/1/2022	6/1/2022	7/1/2022	8/1/2022	9/1/2022	10/1/2022	11/1/2022	12/1/2022
23	a	0.209756098	0.146788991	0.217948718	0.125925926	0.351851852	0.326086957	0.00990099	0.25625	0.352564103	0.366336634	0.023622047	0.207920792
24	b	0.204878049	0.174311927	0.115384615	0.325925926	0.240740741	0.27173913	0.396039604	0.09375	0.314102564	0.297029703	0.409448819	0.217821782
25	c	0.273170732	0.050458716	0.326923077	0.42962963	0.055555556	0.320652174	0.247524752	0.15	0.230769231	0.267326733	0.228346457	0.059405941
26	d	0.136585366	0.105504587	0.288461538	0.088888889	0.290123457	0.10326087	0.128712871	0.3375	0.064102564	0.01980198	0.417322835	0.346534653
27	e	0.019512195	0.119266055	0.128205128	0.355555556	0.098765432	0.038043478	0.326732673	0.05	0.08974359	0.455445545	0.039370079	0.306930693
28
Sheet1

Thank you so much, it's almost perfect! In my real workbook I have about 50 columns of data before the date columns with numbers below them that I need to skip this transformation to, along with the very last column to the rightmost of the table. How would I adjust this code to reflect this?

 

[takoyaki]

Thank you so much, it's almost perfect! In my real workbook I have about 50 columns of data before the date columns with numbers below them that I need to skip this transformation to, along with the very last column to the rightmost of the table. How would I adjust this code to reflect this?

I moved the column at the rightmost of the table to the leftmost and adjusted the code to account for this, now it's working perfectly! Cheers!

let
    tbl1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tbl2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    tbl3 = Table.TransformColumnTypes(tbl2,{{"Period", type date}}),
    tbl4 = List.Accumulate(List.Skip(Table.ColumnNames(tbl1),51), tbl1, (s,c)=> 
        Table.TransformColumns(s, {{c, each _/tbl3{[Period = Date.From(c)]}[Hours]}}))
in
    tbl4