Speeding Up Power Query

excelbytes

Active Member
Joined
Dec 11, 2014
Messages
291
Office Version
  1. 365
Platform
  1. Windows
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?
Query Steps.png
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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.
Power Query:
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
 
Upvote 0
Sorry, did not mean to confirm the post yet. Still working on it to get the table names in.
 
Upvote 0
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:
Power Query:
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
BCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAH
1Col1Col2Col3Col4Col5Col6Col7Col8Col9Col10Col11Col12Col13Col14Col15Col16Col17Col18Col19Col20Col21Col22Col23Col24Col25Col26Col27Col28Col29Col30Col31Col32Col33
2A23911028207125371561291141952222219062191243321931521401261896321183239121103197204250106
3B24718317422019011352147126228120157234318656154100275910514348132425910367217282550
4C19916622149205178115186479410870138216195432277420346191232321721832132421310170139200
5D12869235532191431903082611091942451166584219159212108193164661701321281091102261698224
6E1244812619522419313852701601141019894211511016121657406815310224326310197249235249
7F3158152199110113869411138176174185832162091141872665193154188718655139206188242245194
8G1901027477741642261269359174103242081411717820821162483414013821951250102345120213
9H24117691152225719714528341225589818214110125209901342055017623242234196267789184
10I702356610520714618818515121317221818213721759012321024416698271591062121710222878164116
11J1212084188194532212018723234287310466691671717058183131261191361920315182136112114
12K2001042033654198701991311309815220144164114391965954931122296715716531972204212947
13L23315528198199134117192212175236229207221163684513521002372414213463133229361201384648
14M17421610368119249200129711774340161452482197521924818169136095126209951411215180206
15N1472381631336923240151795767132962620124115598140911374415611864841265569152138158
16O136201201805472120191432002211922672931312426523310618321521918558111248198201108159147
17P21414723389282342451108723945162199541881431272381212311151128164229173615354220166249
18Q6024721217023761222042171551012787175104920119411724013313121781248146231799271200215
19R5413945971065913120510410418723510883227109271823815224218522131205585272125298638
20S20720211423255189166110196205181100632412451541837221045961088722912418372282021224913
Sheet1
 
Upvote 0

Forum statistics

Threads
1,223,905
Messages
6,175,297
Members
452,633
Latest member
DougMo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top