Populate Power BI columns based on SharePoint folder structure

Hi,

I have a requirement where I have to set up a folder structure on SharePoint and integrate it with Power BI so that the files stored in the folders are imported based on a refresh schedule/ manually. As part of the data import, I need to automatically populate couple of dimensions/ columns in the data model based on the SharePoint folder structure. For instance, if I have separate files placed in the below folder structure, and I have files that have lets say 10 columns of data (excel files AA and BB below). I need to pull the value 'Folder A' in a NEW column that is merged with the imported data adding an 11th column for all the records.

https://XXX.sharepoint.com/sites/RootFolder/Folder A/file AA.xlsx

https://XXX.sharepoint.com/sites/RootFolder/Folder B/file BB.xlsx

Any help will be greatly appreciated.