Forum Discussion
XLSX file 10MB, but Power Query loads 100MB into Power Query
Hi Anonymous ,
I believe this is the problematic step:#"Filtered Rows2" = Table.SelectRows(#"Changed Type1", let latest = List.Max(#"Changed Type1"[Data Date]) in each [Data Date] = latest),In there, you're referencing the previous whole table for each row in your table.
This is where buffering usually helps.
If your source table is very large, you can buffer the list whose Max you are retrieving in a previous step like so:BufferedList = List.Buffer(#"Changed Type1"[Data Date]), #"Filtered Rows2" = Table.SelectRows(#"Changed Type1", let latest = List.Max( BufferedList ) in each [Data Date] = latest),You can find more performance tips on my blog here: Speed/Performance aspects – The BIccountant
That helped to cut down to around 80 MB loaded. How do I find out what other steps causes additional evaluations?
Hi Anonymous ,
the next potential cause for performance problems is that you're referencing a nested object within an iterator:
That might also possibly cause duplicate evaluation.
As you are importing multiple files from a folder here, I'm wondering why you don't use the "standard-method" to do all the file-related transformations in the function procedure that will automatically be created if you click on expanding the binaries.
Hard to follow just the code and imagine what's actually happening, but you seem to do some heavy logic with column names from the actual files that might cause the whole content from the files beeing evaluated multiple times.