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

[DJL]

Hi all,

I have a scenario where a monthly CSV dataset is generated from a system.  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.  This data underpins various reporting products and an objective is to automate pulling the latest months' data into a Power BI report.

The file name is always the same, let's call it 'Audit Dataset'.  So, I tested out a technique which involves searching for that string in the File Name field, to filter down.  This works well in identifying the file across multiple folders - so I'm half way there 🙂  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.

But I can't see a way to reference the source file path and align that as a new column.  If I could do that, I'd simply just trim the end of it to create my Reporting Month column - voila, job done.

Any thoughts / help most appreciated...

Thanks 🙂

[mussaenda · Accepted solution]

Hi @DJL,

of course you can do that.

If you are pulling data using Folder instead of single file,

You can see these applied steps:

then on the removed other columns,

select also the folder path so it will not be removed.

viola, job done.

hope this helps.

[DJL]

Hi Mussaenda,

That's brilliant - works a charm!

Thanks for the help, very much appreciated 😎

DJL