Folder name to Column

[Anonymous]

I am trying to create a report where the excel sheets are all stored inside separate folders inside the root directory folder. If I use the root directory folder for "Get Data", is there a way to make the respective names of the separate folders the excel sheet was in as a column in Power BI?

[Anonymous]

Hi @Anonymous,

If you can please share some sample to test, it is hard to coding formula from simple description.

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng

[Anonymous]

GL CODE	DATE	CONSIGNMENT/REFERENCE	ORDER NUMBER	CHARGE	FUEL	SUBTOTAL	GST	TOTAL
3810 0000 1190 1000	18/06/2018	P4419448.1	O4419448	$  126.78		$  126.78	$    12.68	$  139.46

GL CODE	DATE	CONSIGNMENT/REFERENCE	ORDER NUMBER	CHARGE	FUEL	SUBTOTAL	GST	TOTAL
4450 0735 6800 0000			$    78.60	$           -	$    78.60	$       7.86	$    86.46

Here is an 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 posisble 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 for multiple folders (20+)

Thank for your time.

[Anonymous · Accepted solution]

Hi @Anonymous,

You can add a custom column with below formula to get file's parent folder name as custom column value.

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

Regards,

Xiaoxin Sheng

[itsmebvk]

@Anonymous Can you try this method

https://www.linkedin.com/pulse/parsing-folders-power-bi-adding-folder-name-column-query-rob-reilly

[Anonymous]

In this case the file names in this folders are random worksheets rather than 5 different CSVs. How does this change the steps I need to use?