Excel Power Query - Add custom column with shortened Folder Path

Occasional Contributor



I have an excel file with a folder query that maintains list of the files in a directory. It has columns: Folder Path, Name, Extension, Size, etc.. I want the Folder Path column to start from the last folder, which is, in this case, 01_Estimate for line 1, 03_RFP for line 2, etc., in short, I don't want it to display the path prior to the above-mentioned folders. Basically, everything from the 8th level on that I want to show. I tried to use a formula but no luck.


Below image: Folder Path column is the original column. Folder column is what I want it to display. Here I used 'Add Column from examples' and removed the previous levels and it carried down for the rest rows. I would like to have a formula for the custom column instead.



Thanks in advance,



P.S. I apologize for my scribble trying to hide the information.


5 Replies
best response confirmed by alisher_n (Occasional Contributor)

@alisher_n Why not just select the Folder Path column and with the Transform tab active, extract text after the 7th back-slash. Thus, use the back-slash as the delimiter and skip the first 6. No extra column needed. But perhaps I misunderstood your intentions.


As cosmetic comment, it's more reliable to scan from the end