Power Query add custom column with name and merge ...

trickdigger · ‎12-09-2016

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:

Product1.txt
Product2.txt
Product3.txt
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 · ‎12-09-2016

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/

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

trickdigger · ‎12-12-2016

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.