Is there a way to import multiple files at once but apply the same data transformation on each one BEFORE appending? RRS feed

  • Question

  • The reason for this is that my data files have variable column numbers so straight appending doesn't seem to work in Power Query. What I can do is to import each file - unpivot some columns and then I have the same number of columns for each dataset and then appending is easy. However the only option in the import "from folder" option is to first append and then transform. 

    Anyone know of a workaround for this?


    Sunday, April 16, 2017 3:22 AM


  • Since the November 2016 upgrade of Power BI Desktop and later also in Excel (depending on your update frequency), the functionality is exactly what you want it to be.

    Once you have indicated the folder and applied any filtering, you have a navigation list with your files from the folder as a column with binaries. If you use the right icon in the column header - combine binaries - you get 4 new query objects and 4 new steps in you main query.

    One of the 4 new objects is an example transform query that performs the required transformations on 1 example file. The code of this transform query is incorporated in a transform query (which is ome of the other query objects that are created by "combine binaries"), which is used to apply the transformations to each of the files from your folder. This function is used to add a column in your main query with nested tables (i.e. the result of transforming each binary); when this column is expanded, the transformed files are effectively appended.

    So if you want to apply specific transformation: adjust the example transform query accordingly.
    The adjustments will automatically be promoted to the function.

    Otherwise, if you don't use the current "Combine Binaries"  functionality, you can still create your own function to perform transformations on each binary.

    Sunday, April 16, 2017 4:41 AM