Speeding Up Power Query

[excelbytes]

I have a workbook that's only 437Kb. In it I am using Power Query to take a data range that is 367 rows and 33 columns, and transforming that to a table that is 11,286 rows. All I'm doing is unpivoting the data. Any time I make a
single change and refresh all, it takes around 19 seconds to refresh. I've included the steps attached. I've unchecked "Allow data previews to download in the background". There are 366 cells containing each of the following formulas:
=IFERROR(FILTER(Table_Table1[[Vet]:[Vet]],(Table_Table1[[Status]:[Status]]="W")*(Table_Table1[[Date]:[Date]]=F1)),"")
=SORT(IF(FILTER(F2:INDEX(F2:F30,COUNTA(F2:F30)),COUNTIF(Table3[1/1/2024],F2:INDEX(F2:F30,COUNTA(F2:F30)))<1,"")=0,"",FILTER(F2:INDEX(F2:F30,COUNTA(F2:F30)),COUNTIF(Table3[1/1/2024],F2:INDEX(F2:F30,COUNTA(F2:F30)))<1,"")),,1)

I was using the OFFSET function, but replaced it with F2:INDEX(F2:F30,COUNTA(F2:F30) to eliminate any volatile functions.

Any other thoughts to speed up the refresh cycle?

 

[Matt Allington]

19 seconds is fast. PQ is not supposed to be like Excel formulas (Sub second)

 

[GraH]

33 columns is a wide table. Unpivot is slow.
You can try to use this Query (I can count to 2, while I even have a column more to unpivot) I assume you are unpivotting the last 32 columns.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Record_as_list = Table.AddColumn(Source, "Cols", each List.Skip(Record.ToList(_),1)),
    Expand_List_as_row = Table.ExpandListColumn(Record_as_list, "Cols"),
    Select_cols = Table.SelectColumns(Expand_List_as_row,{"Col1", "Cols"})
in
    Select_cols

 

[GraH]

Sorry, did not mean to confirm the post yet. Still working on it to get the table names in.

 

[GraH]

Hi again,
Sometimes our brain tricks us in false memories. So I revisited a piece of my work at work and this is the code I wanted to share:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Set_types = Table.TransformColumnTypes(Source,{{"Col1", type text}, {"Col2", Int64.Type}, {"Col3", Int64.Type}, {"Col4", Int64.Type}, {"Col5", Int64.Type}, {"Col6", Int64.Type}, {"Col7", Int64.Type}, {"Col8", Int64.Type}, {"Col9", Int64.Type}, {"Col10", Int64.Type}, {"Col11", Int64.Type}, {"Col12", Int64.Type}, {"Col13", Int64.Type}, {"Col14", Int64.Type}, {"Col15", Int64.Type}, {"Col16", Int64.Type}, {"Col17", Int64.Type}, {"Col18", Int64.Type}, {"Col19", Int64.Type}, {"Col20", Int64.Type}, {"Col21", Int64.Type}, {"Col22", Int64.Type}, {"Col23", Int64.Type}, {"Col24", Int64.Type}, {"Col25", Int64.Type}, {"Col26", Int64.Type}, {"Col27", Int64.Type}, {"Col28", Int64.Type}, {"Col29", Int64.Type}, {"Col30", Int64.Type}, {"Col31", Int64.Type}, {"Col32", Int64.Type}, {"Col33", Int64.Type}}),
    Get_record = Table.CombineColumnsToRecord(Set_types, "Rec", List.Skip(Table.ColumnNames(Source),1)),
    Rec_to_table = Table.TransformColumns(Get_record,{{"Rec", Record.ToTable, type table}}),
    Expand_rec = Table.ExpandTableColumn(Rec_to_table, "Rec", {"Name", "Value"}, {"Name", "Value"}),
    Type_expanded = Table.TransformColumnTypes(Expand_rec,{{"Name", type text}, {"Value", Int64.Type}})
in
    Type_expanded
[CODE=pq]

[/CODE]

I made a silly sample like below (limited to 3000 cells):

test_unpivot.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
1	Col1	Col2	Col3	Col4	Col5	Col6	Col7	Col8	Col9	Col10	Col11	Col12	Col13	Col14	Col15	Col16	Col17	Col18	Col19	Col20	Col21	Col22	Col23	Col24	Col25	Col26	Col27	Col28	Col29	Col30	Col31	Col32	Col33
2	A	239	110	28	207	125	37	156	129	114	195	222	221	90	62	191	243	32	193	152	140	126	189	63	21	183	239	121	103	197	204	250	106
3	B	247	183	174	220	190	113	52	147	126	228	120	157	23	43	186	56	154	100	27	59	105	143	48	132	42	59	103	67	217	28	25	50
4	C	199	166	221	49	205	178	115	186	47	94	108	70	138	216	195	43	227	74	203	46	19	123	232	172	183	213	24	213	10	170	139	200
5	D	128	69	235	53	219	143	190	30	82	61	109	194	245	116	65	84	219	159	212	108	193	164	66	170	132	128	109	110	226	16	98	224
6	E	124	48	126	195	224	193	138	52	70	160	114	101	98	94	211	51	101	61	216	57	40	68	153	10	224	32	63	10	197	249	235	249
7	F	31	58	152	199	110	113	86	94	111	38	176	174	185	83	216	209	114	187	26	65	193	154	18	87	186	55	139	206	188	242	245	194
8	G	190	102	74	77	74	164	226	126	93	59	174	103	24	208	14	117	178	208	211	62	48	34	140	138	219	51	250	10	23	45	120	213
9	H	24	117	69	115	222	57	197	145	28	34	122	55	89	81	82	141	101	25	209	90	134	205	50	176	232	42	234	196	26	77	89	184
10	I	70	235	66	105	207	146	188	185	151	213	172	218	18	213	72	175	90	123	210	244	166	98	27	159	106	21	217	102	228	78	164	116
11	J	12	120	84	188	194	53	221	201	87	232	34	28	73	104	66	69	167	17	170	58	183	13	126	119	136	19	203	15	182	136	112	114
12	K	200	104	203	36	54	198	70	199	131	130	98	152	201	44	164	114	39	196	59	54	93	112	229	67	157	16	53	197	220	42	129	47
13	L	233	155	28	198	199	134	117	192	212	175	236	229	207	221	163	68	45	13	52	100	237	241	42	134	63	133	229	36	120	138	46	48
14	M	174	216	103	68	119	249	200	129	71	177	43	40	16	145	248	219	75	219	248	181	69	13	60	95	126	209	95	141	12	151	80	206
15	N	147	238	163	133	69	232	40	15	179	57	67	132	96	26	201	241	155	98	140	91	137	44	156	118	64	84	126	55	69	152	138	158
16	O	136	20	120	180	54	72	120	19	143	200	22	119	226	72	93	131	242	65	233	106	183	215	219	185	58	111	248	198	201	108	159	147
17	P	214	147	233	89	28	234	245	110	87	239	45	162	199	54	188	143	127	238	121	231	11	51	128	164	229	173	61	53	54	220	166	249
18	Q	60	247	212	170	237	61	22	204	217	155	101	27	87	175	10	49	201	194	117	240	133	131	217	81	248	146	23	179	92	71	200	215
19	R	54	139	45	97	106	59	131	205	104	104	187	235	108	83	227	109	27	182	38	152	242	185	22	131	205	58	52	72	125	29	86	38
20	S	207	202	114	232	55	189	166	110	196	205	181	100	63	241	245	154	183	72	210	45	96	108	87	229	124	18	37	228	202	12	249	13
Sheet1