Populate columns in Power BI based on the folder structure/ folder names in SharePoint

[Anonymous]

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.

[amitchandak]

@Anonymous , refer this

https://community.powerbi.com/t5/Desktop/Folder-name-to-Column/td-p/600676

#"Added Custom" = Table.AddColumn(#"Previous Steps", "Parent", each List.First(List.LastN(Text.Split([Folder Path],"\"),2)))

file name

https://www.sqlservercentral.com/blogs/include-file-name-in-content-using-power-query

[lbendlin]

Connect using the Sharepoint Folder connector. 

Split the path column by the forward slash.

Done.

[Anonymous]

Thank you for your response. 

I believe your step splits the columns at the "Source" level however, by the time I proceed with importing the actual data from the file, I am not sure how do I get the split column from a previous step.

[lbendlin]

There is no real need as the file object is already available in the source step.

Or did I misunderstand?

[Anonymous]

I'll try to give another example.

These two tables are in separate excel files in excel (Let's call them A and B). A and B themselves are also in separate folders, where A is in Folder C and B is in Folder D. Folder C and D are both inside root folder E. I want to know if I run Power BI with folder E as the root folder, if it's possible to create a column where if the file is from Folder C it will get "C" as a value in the column, or if it s from Folder it will get a "D" value in that column and have these columns appended to the rest of data columns in the actul file

Below is file A and B respectively and I need an additional column added to both them that has the 'folder name' value where they are coming from. Apologies if it sounds redundant. 

@amitchandak : I tried using the solution in your link but that lets me add this column in the "Source" table and not the actual imported file content table. 

[lbendlin]

Still not clear.  Show your Power Query code.