Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

XLSX file 10MB, but Power Query loads 100MB into Power Query

Hi,   My file size is only 10MB, but when I load it into Power Query, it seems to be loading 10 times the file size.   I do not have any tables or pivot tables in the dataset and am wondering why...
Need Help
Tips and Tricks
  • ImkeF's avatar
    ImkeF
    4 years ago

    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

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Anonymous ,

 

Chris Webb has written a lot about optimizing query performance using files as a data source. Consider the following link as a start: Chris Webb's BI Blog: Comparing The Performance Of CSV And Excel Data Sources In Power Query Chris W...

 

You might also considering using the M function Table.Buffer Table.Buffer - PowerQuery M | Microsoft Docs This can reduce the number of re-reading the base file.

 

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.