Power Query - Split folder path with different structure into same columns

[Zdenek_Moravec]

Dear colleagues,

I want to create a list of folders and files and split the folder names into columns. Unfortunatelly the structure of all subfolders is not the same.

Example:

C:\EUROPE\GERMANY\2019\file1.docx

C:\EUROPE\GERMANY\2020\file2.docx

C:\EUROPE\FRANCE\2019\file3.docx

C:\EUROPE\FRANCE\2020\file4.docx

C:\AMERICA\2019\file5.docx

C:\AMERICA\2020\file6.docx

The result in columns should be

Continent	Country	Year	File
Europe	Germany	2019	file1.docx
Europe	Germany	2019	file1.docx
Europe	France	2019	file3.docx
Europe	France	2019	file4.docx
AMERICA		2019	file5.docx
AMERICA		2020	file6.docx

If I split by delimiter, I get European countries with America years together. But how to say PQ, that the folder with year should come into  the same column?

Thank you

Zdenek Moravec

Cesky Krumlov, Czech Republic

[#2]

@Zdenek_Moravec A quick-and-dirty solution attached.

[#3 · Accepted solution]

@Zdenek_Moravec 

Slightly different variant (without transition from letter to number), but with same assumption we have only 4 parts and only second one (country) could be missed.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Exclude Drive" = Table.AddColumn(
        Source, "path",
        each Text.AfterDelimiter([FileName], ":\"),
        type text
    ),
    #"Get Region" = Table.SplitColumn(
        #"Exclude Drive", "path",
        Splitter.SplitTextByEachDelimiter({"\"}, QuoteStyle.Csv, false),
        {"Region", "Txt"}
    ),
    #"Get File" = Table.SplitColumn(
        #"Get Region", "Txt",
        Splitter.SplitTextByEachDelimiter({"\"}, QuoteStyle.Csv, true),
        {"Txt", "File"}
    ),
    #"Get Country" = Table.AddColumn(
        #"Get File", "Country",
        each Text.BeforeDelimiter([Txt], "\", {0, RelativePosition.FromEnd}),
        type text
    ),
    #"Get Year" = Table.AddColumn(
        #"Get Country", "Year",
        each Text.AfterDelimiter([Txt], "\", {0, RelativePosition.FromEnd}),
        type text
    ),
    #"Keep Final" = Table.SelectColumns(
        #"Get Year",
        {"Region", "Country", "Year", "File"}
    )
in
    #"Keep Final"

[#4]

Hello @Riny_van_Eekelen 

Thank You for the hint. I understand, that this task has not an universal solution, but the usage of SplitTextByCharacterTransition can be usefull. I have never seen this function in online trainings. By the debugging I found, that the structure is slightly different and even worser for SplitTextByCharacterTransition - first year, then countries, finally files.

C:\EUROPE\2019\GERMANY\file1.docx

C:\EUROPE\2020\GERMANY\file2.docx

C:\EUROPE\2019\FRANCE\file3.docx

C:\EUROPE\2020\FRANCE\file4.docx

C:\AMERICA\2019\file5.docx

C:\AMERICA\2020\file6.docx

[#5]

Hello @Sergei Baklan 

Thank You for the next new functions for me. Text.AfterDelimiter and Text.BeforeDelimiter look flexible for this kind of scenario. I will play with these functions, with regard to the fact above, that the countries are after the years.

[#6]

@Zdenek_Moravec 

That's all in user interface, you may call them both in Transform and Add Column under Extract.

[#7]

@Sergei Baklan @Riny_van_Eekelen 

Task completed and two more functions learned. Thank you both!

Zdenek

[#8]

@Zdenek_Moravec , glad to help