Cleaner way to import files from folder - Avoid too many helper queries/function

Power Query
1

Hi everyone, how can I avoid too many helper queries when transforming files from a folder?

Transform file or helper queries are automatically added when transforming a table from folder.

Thanks in advance :slightly_smiling_face:

image
image357Ă—702 21.1 KB

2

You can create a custom function yourself with one of the files, and then invoke that funciton on the table.

Folder Function Test.zip (20.8 KB)
Folder Function.pbix (16.3 KB)

image
image1362Ă—459 41.1 KB

2 Likes
3

Hi @ronald_balza, did the response provided by @AntrikshSharma help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

4

Hi @ronald_balza , we’ve noticed that no response has been received from you since May 30th. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

5

Not sure if this topic is still open, my solution to replace the helper queries is adding a custom column that extracts the content of each file in a table format. When opening the table contents you can select which columns to expand and the file contents will be appended.

Here are my most used custom column formulas:

  • CSV: Table.PromoteHeaders(Csv.Document([Content]))
  • Tab delimited: Table.PromoteHeaders(Csv.Document([Content],[Delimiter="#(tab)"]))

Additionally, excel files are a bit different because you’d need to specify the sheet or table.

- Directly from source file: Excel.Workbook(File.Contents( “[Path+filename+.xlsx]” ), true, true){[Item=“Sheet1”]}[Data]
- From a column with Binary values: Excel.Workbook([Content],true,true){[Item=" [Sheet name] "]}[Data]

Happy to hear anyone’s thoughts on this method. So far it’s helped me a lot in my work, but I’m always looking to optimize further :slight_smile:

1 Like