none
How to read from each file only once during a transformation? RRS feed

  • Question

  • Hello,

    A well know pattern in Power Query is the creation of a function to perform a transformation in a series of files/tables.
    I usually do a Table.Group in the interested table, grouping all rows and then I call the function with each nested table as an argument, following the steps.

    What I observe is the same problem cited by Chris Webb in the article "Power Query Performance And Expanding Columns" : in many cases the transformation read the files multiple times.

    Chris wrote another article ("Storing Large Images In Power BI Datasets") showing a transformation which read each file only once, converting a table to a list, transforming the list and back to a table.

    What I would like to know is why the second approach read the files only once and what are the best practices to avoid this multiple file read problem?

    Thanks in advance.


    Sunday, April 12, 2020 11:14 PM

Answers

  • If you want to avoid multiple reads (but at the expense of consuming more memory), you can buffer the data (i.e. load it into memory, breaking any connection back to the original data source) before processing it. The functions that do this include Table.Buffer, Binary.Buffer, and List.Buffer.

    Ehren

    Wednesday, April 15, 2020 8:56 PM
    Owner

All replies