SOLVED
Home

Power Query - Combine Data from Folder with Relative Paths

%3CLINGO-SUB%20id%3D%22lingo-sub-121002%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20-%20Combine%20Data%20from%20Folder%20with%20Relative%20Paths%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-121002%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Justin%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBit%20more%20in%20addition.%20Combine%20(clicking%20on%20Content%20column%20with%20binaries)%20doesn't%20work%20with%20file%20path%20defined%20as%20variable%2C%20it%20shall%20be%20the%20text.%20The%20approach%20is%20in%20link%20what%20Yury%20gave%20and%20more%20illustrative%20here%26nbsp%3B%3CA%20href%3D%22http%3A%2F%2Fwww.howtoexcel.org%2Fpower-query%2Fhow-to-import-all-files-in-a-folder-with-power-query%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Fwww.howtoexcel.org%2Fpower-query%2Fhow-to-import-all-files-in-a-folder-with-power-query%3C%2FA%3E%20(mainly%20second%20part%20of%20the%20post).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20brief%2C%20idea%20is%20to%20create%20the%20function%20which%20returns%20the%20table%20or%20the%20sheet%20(as%20you%20define%20the%20item%20to%20return)%20where%20the%20parameters%20for%20that%20function%20are%20file%20name%20and%20it's%20path.%20Instead%20of%20Combine%20you%20add%20custom%20column%20to%20your%20table%20with%20binaries%20into%20which%20that%20function%20returns%20tables%20from%20each%20file%20(taken%20file%20name%2Fpath%20as%20parameters%26nbsp%3Bfrom%20each%20row).%20After%20that%20expand%20that%20custom%20column%20and%20you%20are%20here.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-119978%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20-%20Combine%20Data%20from%20Folder%20with%20Relative%20Paths%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-119978%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3BJustin%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eplease%20have%20a%20look%20at%20the%26nbsp%3Bfollowing%20blog%2C%20which%20may%20be%20helpful%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.excelguru.ca%2Fblog%2F2015%2F02%2F25%2Fcombine-multiple-excel-workbooks-in-power-query%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.excelguru.ca%2Fblog%2F2015%2F02%2F25%2Fcombine-multiple-excel-workbooks-in-power-query%2F%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELater%26nbsp%3Bversions%20of%20Power%20Query%20automatically%20create%20a%20function%20to%20combine%20files%20excel%20files%20from%20folder.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYury%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-691843%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20-%20Combine%20Data%20from%20Folder%20with%20Relative%20Paths%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-691843%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20doing%20add%20custom%20column%20but%20problem%20is%20the%20columns%20that%20get%20populated%20does%20not%20get%20refreshed%20when%20my%20source%20data%20files%20change.%20Even%20after%20refresh%2C%20column%20headers%20remain%20the%20same%20which%20existed%20in%20earlier%20files%20and%20not%20in%20the%20currently%20loaded%20files.%20Every%20time%20I%20have%20to%20delete%20expand%20query%20and%20add%20new%20Table%20expand%20from%20custom%20column%20to%20get%20correct%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20solution%3F%20May%20be%20if%20I%20can%20use%26nbsp%3B%20a%20function%20like%20select%20all%20columns%20from%20table.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-119568%22%20slang%3D%22en-US%22%3EPower%20Query%20-%20Combine%20Data%20from%20Folder%20with%20Relative%20Paths%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-119568%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20folder%20(called%20Excel%20Exports)%20with%20a%20varying%20number%20of%20files%20within%20it%2C%20all%20the%20same%20format.%20This%20folder%20is%20distributed%20to%20many%20people%2C%20all%20residing%20on%20an%20unknown%20location%20on%20their%20local%20machine.%20The%20files%20within%20the%20folder%20are%20all%20the%20same%20format.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20using%20the%20relative%20path%20workaround%2C%20where%20I%20have%20a%20table%20(named%20FolderPath)%20with%20a%20single%20record%20within%20my%20sheet%20that%20identifies%20the%20local%20path.%20Then%20I%20feed%20this%20local%20path%20into%20my%20Power%20Queries%20to%20run%20the%20Get%20Data%20From%20Folder%20query.%20This%20all%20works%20great.%20My%20source%20looks%20like%20this%3C%2FP%3E%3CBLOCKQUOTE%3E%3CP%3ESource%20%3D%20Folder.Files(Table.FirstValue(Excel.CurrentWorkbook()%7B%5BName%3D%22FolderPath%22%5D%7D%5BContent%5D)%20%26amp%3B%20%22Excel%20Exports%22)%2C%3C%2FP%3E%3C%2FBLOCKQUOTE%3E%3CP%3EThe%20problem%20arises%20when%20I%20try%20to%20combine%20those%20files.%20All%20of%20the%20files%20are%20the%20exact%20same%20format%2C%20but%20with%20different%20data%20inside.%20When%20I%20run%20the%20Combine%20function%2C%20it%20gives%20me%20an%20error%20that%20says%20%22We%20couldn't%20find%20an%20Excel%20table%20named%20'FolderPath'.%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20the%20table%20is%20built%20properly%2C%20because%20that's%20the%20same%20table%20I'm%20referencing%20in%20my%20source%2C%20and%20it's%20working%20fine%20there.%20It%20appears%20that%20the%20Combine%20function%20does%20allow%20me%20to%20use%20this%20same%20workaround%20for%20some%20reason.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20ideas%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-119568%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Justin Schmidt
Visitor

I have a folder (called Excel Exports) with a varying number of files within it, all the same format. This folder is distributed to many people, all residing on an unknown location on their local machine. The files within the folder are all the same format.

 

I am using the relative path workaround, where I have a table (named FolderPath) with a single record within my sheet that identifies the local path. Then I feed this local path into my Power Queries to run the Get Data From Folder query. This all works great. My source looks like this

Source = Folder.Files(Table.FirstValue(Excel.CurrentWorkbook(){[Name="FolderPath"]}[Content]) & "Excel Exports"),

The problem arises when I try to combine those files. All of the files are the exact same format, but with different data inside. When I run the Combine function, it gives me an error that says "We couldn't find an Excel table named 'FolderPath'."

 

I know the table is built properly, because that's the same table I'm referencing in my source, and it's working fine there. It appears that the Combine function does allow me to use this same workaround for some reason.

 

Any ideas?

3 Replies

Hi Justin,

 

please have a look at the following blog, which may be helpful

https://www.excelguru.ca/blog/2015/02/25/combine-multiple-excel-workbooks-in-power-query/

 

Later versions of Power Query automatically create a function to combine files excel files from folder.

 

Yury

Solution

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.

Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
201 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies