Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Folder name to Column

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?

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

5 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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?

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago
      GL CODEDATECONSIGNMENT/REFERENCEORDER NUMBER  CHARGE    FUEL    SUBTOTAL    GST    TOTAL  
       3810 0000 1190 1000 18/06/2018P4419448.1O4419448 $  126.78  $  126.78 $    12.68 $  139.46

       

       

      GL CODEDATECONSIGNMENT/REFERENCEORDER 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's avatar
        Anonymous
        Not applicable
        7 years ago

        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