Power Query add custom column with name and merge all files

[trickdigger]

Hi, 

I am new to PowerBI/Power Query and was trying to solve this problem. 

I have a folder of files that contain data, but these files do not contain a column that indicates what product it is for. What I'm trying to do is add a column with the file name and the combine all of the files so that I have one table. 

I.E, my files are located in c:\files and are named:

1. Product1.txt
2. Product2.txt
3. Product3.txt
4. Product4.txt

I need to add a column to each file that says "Product1", for example, so that the product can be distinguised after the files are combined. Ideally this would be able to be automated so that the new files are dropped in and then refreshed. 

Could someone get me started? I searched for similar threads and I ended up getting lost trying to recreate the steps. 

Thanks

[BhaveshPatel]

Check out this recent blog post from Gil Raviv on PowerPivotPro.com

https://www.powerpivotpro.com/2016/12/import-csv-files-folder-filenames-power-bi/

[trickdigger]

Bhavesh,

I tried it and it works, but I can't export the rows due to data limit. So I really need to do this in Power Query as the end users need the data in a pivoted excel file.