Forum Discussion
Power Query - Combine Data from Folder with Relative Paths
Hi Justin,
Bit more in addition. Combine (clicking on Content column with binaries) doesn't work with file path defined as variable, it shall be the text. The approach is in link what Yury gave and more illustrative here http://www.howtoexcel.org/power-query/how-to-import-all-files-in-a-folder-with-power-query (mainly second part of the post).
In brief, idea is to create the function which returns the table or the sheet (as you define the item to return) where the parameters for that function are file name and it's path. Instead of Combine you add custom column to your table with binaries into which that function returns tables from each file (taken file name/path as parameters from each row). After that expand that custom column and you are here.
Hi Justin,
Bit more in addition. Combine (clicking on Content column with binaries) doesn't work with file path defined as variable, it shall be the text. The approach is in link what Yury gave and more illustrative here http://www.howtoexcel.org/power-query/how-to-import-all-files-in-a-folder-with-power-query (mainly second part of the post).
In brief, idea is to create the function which returns the table or the sheet (as you define the item to return) where the parameters for that function are file name and it's path. Instead of Combine you add custom column to your table with binaries into which that function returns tables from each file (taken file name/path as parameters from each row). After that expand that custom column and you are here.
I am doing add custom column but problem is the columns that get populated does not get refreshed when my source data files change. Even after refresh, column headers remain the same which existed in earlier files and not in the currently loaded files. Every time I have to delete expand query and add new Table expand from custom column to get correct data.
Any solution? May be if I can use a function like select all columns from table.
