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

Question

Hi,&nbsp;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.&nbsp;As part of the data import, I need to automatically populate couple of dimensions/ columns&nbsp; 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.&nbsp;https://XXX.sharepoint.com/sites/RootFolder/Folder A/file AA.xlsxhttps://XXX.sharepoint.com/sites/RootFolder/Folder B/file BB.xlsx&nbsp;Any help will be greatly appreciated.

lbendlin · Answer

Connect using the Sharepoint Folder connector.&nbsp;
Split the path column by the forward slash.
Done.

amitchandak · Answer

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)))
&nbsp;
&nbsp;
file name
https://www.sqlservercentral.com/blogs/include-file-name-in-content-using-power-query

anonymous · Answer

Thank you for your response.&nbsp;&nbsp;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.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

lbendlin · Answer

There is no real need as the file object is already available in the source step.
&nbsp;
Or did I misunderstand?

anonymous · Answer

I'll try to give another example.&nbsp;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&nbsp;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.&nbsp;&nbsp;&nbsp;amitchandak&nbsp;: 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.&nbsp;&nbsp;&nbsp;

Forum Discussion

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

6 Replies

Related content

Table structure

ELT JSON / Semi structured

Population dynamic graaph

ELT JSON Semi Structured Loop

Visualising SharePoint online structure