Combine files from different folders + Add a column with file path

Question

Hi all,&nbsp;I have a scenario where a monthly CSV dataset is generated from a system.&nbsp; For audit purposes, this dataset is then stored into a corresponding monthly folder on SharePoint, eg: Jan 2022, Feb 2022, Mar 2022 and so on.&nbsp; This data underpins various reporting products and an objective is to automate pulling the latest months' data into a Power BI report.&nbsp;The file name is always the same, let's call it 'Audit Dataset'.&nbsp; So, I tested out a technique which involves searching for that string in the File Name field, to filter down.&nbsp; This works well in identifying the file across multiple folders - so I'm half way there&nbsp;🙂&nbsp; However, I need to be able to differentiate time periods and for that, pull in the folder name 'ie: Mar 2022' and have that as a column within the appended dataset.&nbsp;But I can't see a way to reference the source file path and align that as a new column.&nbsp; If I could do that, I'd simply just trim the end of it to create my Reporting Month column - voila, job done.&nbsp;Any thoughts / help most appreciated...&nbsp;Thanks 🙂

mussaenda · Accepted Answer

Hi&nbsp;DJL,
&nbsp;
of course you can do that.
If you are pulling data using Folder instead of single file,
You can see these applied steps:

&nbsp;
then on the removed other columns,
select also the folder path so it will not be removed.
&nbsp;

&nbsp;
viola, job done.
&nbsp;
hope this helps.
&nbsp;

Forum Discussion

Combine files from different folders + Add a column with file path

2 Replies

Related content

Power Query Combine Multiple Files From Folder

Combining files with different field names from Sharepoint folder

Folder Combine Wizard creates sample file queries but does not generate final combined query

Tables combine

Combine Files From SharePoint Folder