SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-2119202%22%20slang%3D%22en-US%22%3EPower%20Query%20-%20Split%20folder%20path%20with%20different%20structure%20into%20same%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2119202%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20colleagues%2C%3C%2FP%3E%3CP%3EI%20want%20to%20create%20a%20list%20of%20folders%20and%20files%20and%20split%20the%20folder%20names%20into%20columns.%20Unfortunatelly%20the%20structure%20of%20all%20subfolders%20is%20not%20the%20same.%3C%2FP%3E%3CP%3EExample%3A%3C%2FP%3E%3CP%3EC%3A%5CEUROPE%5CGERMANY%5C2019%5Cfile1.docx%3C%2FP%3E%3CP%3EC%3A%5CEUROPE%5CGERMANY%5C2020%5Cfile2.docx%3C%2FP%3E%3CP%3EC%3A%5CEUROPE%5CFRANCE%5C2019%5Cfile3.docx%3C%2FP%3E%3CP%3EC%3A%5CEUROPE%5CFRANCE%5C2020%5Cfile4.docx%3C%2FP%3E%3CP%3EC%3A%5CAMERICA%5C2019%5Cfile5.docx%3C%2FP%3E%3CP%3EC%3A%5CAMERICA%5C2020%5Cfile6.docx%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20result%20in%20columns%20should%20be%3C%2FP%3E%3CTABLE%20width%3D%22307%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2290%22%3EContinent%3C%2FTD%3E%3CTD%20width%3D%2272%22%3ECountry%3C%2FTD%3E%3CTD%20width%3D%2245%22%3EYear%3C%2FTD%3E%3CTD%20width%3D%22100%22%3EFile%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EEurope%3C%2FTD%3E%3CTD%3EGermany%3C%2FTD%3E%3CTD%3E2019%3C%2FTD%3E%3CTD%3Efile1.docx%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EEurope%3C%2FTD%3E%3CTD%3EGermany%3C%2FTD%3E%3CTD%3E2019%3C%2FTD%3E%3CTD%3Efile1.docx%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EEurope%3C%2FTD%3E%3CTD%3EFrance%3C%2FTD%3E%3CTD%3E2019%3C%2FTD%3E%3CTD%3Efile3.docx%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EEurope%3C%2FTD%3E%3CTD%3EFrance%3C%2FTD%3E%3CTD%3E2019%3C%2FTD%3E%3CTD%3Efile4.docx%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EAMERICA%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E2019%3C%2FTD%3E%3CTD%3Efile5.docx%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EAMERICA%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E2020%3C%2FTD%3E%3CTD%3Efile6.docx%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20split%20by%20delimiter%2C%20I%20get%20European%20countries%20with%20America%20years%20together.%20But%20how%20to%20say%20PQ%2C%20that%20the%20folder%20with%20year%20should%20come%20into%26nbsp%3B%20the%20same%20column%3F%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3CP%3EZdenek%20Moravec%3C%2FP%3E%3CP%3ECesky%20Krumlov%2C%20Czech%20Republic%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2119202%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2121517%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20-%20Split%20folder%20path%20with%20different%20structure%20into%20same%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2121517%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F295513%22%20target%3D%22_blank%22%3E%40Zdenek_Moravec%3C%2FA%3E%26nbsp%3B%2C%20glad%20to%20help%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2121051%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20-%20Split%20folder%20path%20with%20different%20structure%20into%20same%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2121051%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETask%20completed%20and%20two%20more%20functions%20learned.%20Thank%20you%20both!%3C%2FP%3E%3CP%3EZdenek%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2119898%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20-%20Split%20folder%20path%20with%20different%20structure%20into%20same%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2119898%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F295513%22%20target%3D%22_blank%22%3E%40Zdenek_Moravec%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20all%20in%20user%20interface%2C%20you%20may%20call%20them%20both%20in%20Transform%20and%20Add%20Column%20under%20Extract.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2119648%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20-%20Split%20folder%20path%20with%20different%20structure%20into%20same%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2119648%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20You%20for%20the%20next%20new%20functions%20for%20me.%20Text.AfterDelimiter%20and%20Text.BeforeDelimiter%20look%20flexible%20for%20this%20kind%20of%20scenario.%20I%20will%20play%20with%20these%20functions%2C%20with%20regard%20to%20the%20fact%20above%2C%20that%20the%20countries%20are%20after%20the%20years.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2119644%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20-%20Split%20folder%20path%20with%20different%20structure%20into%20same%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2119644%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20You%20for%20the%20hint.%20I%20understand%2C%20that%20this%20task%20has%20not%20an%20universal%20solution%2C%20but%20the%20usage%20of%20SplitTextByCharacterTransition%20can%20be%20usefull.%20I%20have%20never%20seen%20this%20function%20in%20online%20trainings.%20By%20the%20debugging%20I%20found%2C%20that%20the%20structure%20is%20slightly%20different%20and%20even%20worser%20for%20SplitTextByCharacterTransition%20-%20first%20year%2C%20then%20countries%2C%20finally%20files.%3C%2FP%3E%3CTABLE%20width%3D%22387%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22387%22%3EC%3A%5CEUROPE%5C2019%5CGERMANY%5Cfile1.docx%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EC%3A%5CEUROPE%5C2020%5CGERMANY%5Cfile2.docx%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EC%3A%5CEUROPE%5C2019%5CFRANCE%5Cfile3.docx%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EC%3A%5CEUROPE%5C2020%5CFRANCE%5Cfile4.docx%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EC%3A%5CAMERICA%5C2019%5Cfile5.docx%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EC%3A%5CAMERICA%5C2020%5Cfile6.docx%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2119627%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20-%20Split%20folder%20path%20with%20different%20structure%20into%20same%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2119627%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F295513%22%20target%3D%22_blank%22%3E%40Zdenek_Moravec%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESlightly%20different%20variant%20(without%20transition%20from%20letter%20to%20number)%2C%20but%20with%20same%20assumption%20we%20have%20only%204%20parts%20and%20only%20second%20one%20(country)%20could%20be%20missed.%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-powerquery%22%3E%3CCODE%3Elet%0A%20%20%20%20Source%20%3D%20Excel.CurrentWorkbook()%7B%5BName%3D%22Table1%22%5D%7D%5BContent%5D%2C%0A%20%20%20%20%23%22Exclude%20Drive%22%20%3D%20Table.AddColumn(%0A%20%20%20%20%20%20%20%20Source%2C%20%22path%22%2C%0A%20%20%20%20%20%20%20%20each%20Text.AfterDelimiter(%5BFileName%5D%2C%20%22%3A%5C%22)%2C%0A%20%20%20%20%20%20%20%20type%20text%0A%20%20%20%20)%2C%0A%20%20%20%20%23%22Get%20Region%22%20%3D%20Table.SplitColumn(%0A%20%20%20%20%20%20%20%20%23%22Exclude%20Drive%22%2C%20%22path%22%2C%0A%20%20%20%20%20%20%20%20Splitter.SplitTextByEachDelimiter(%7B%22%5C%22%7D%2C%20QuoteStyle.Csv%2C%20false)%2C%0A%20%20%20%20%20%20%20%20%7B%22Region%22%2C%20%22Txt%22%7D%0A%20%20%20%20)%2C%0A%20%20%20%20%23%22Get%20File%22%20%3D%20Table.SplitColumn(%0A%20%20%20%20%20%20%20%20%23%22Get%20Region%22%2C%20%22Txt%22%2C%0A%20%20%20%20%20%20%20%20Splitter.SplitTextByEachDelimiter(%7B%22%5C%22%7D%2C%20QuoteStyle.Csv%2C%20true)%2C%0A%20%20%20%20%20%20%20%20%7B%22Txt%22%2C%20%22File%22%7D%0A%20%20%20%20)%2C%0A%20%20%20%20%23%22Get%20Country%22%20%3D%20Table.AddColumn(%0A%20%20%20%20%20%20%20%20%23%22Get%20File%22%2C%20%22Country%22%2C%0A%20%20%20%20%20%20%20%20each%20Text.BeforeDelimiter(%5BTxt%5D%2C%20%22%5C%22%2C%20%7B0%2C%20RelativePosition.FromEnd%7D)%2C%0A%20%20%20%20%20%20%20%20type%20text%0A%20%20%20%20)%2C%0A%20%20%20%20%23%22Get%20Year%22%20%3D%20Table.AddColumn(%0A%20%20%20%20%20%20%20%20%23%22Get%20Country%22%2C%20%22Year%22%2C%0A%20%20%20%20%20%20%20%20each%20Text.AfterDelimiter(%5BTxt%5D%2C%20%22%5C%22%2C%20%7B0%2C%20RelativePosition.FromEnd%7D)%2C%0A%20%20%20%20%20%20%20%20type%20text%0A%20%20%20%20)%2C%0A%20%20%20%20%23%22Keep%20Final%22%20%3D%20Table.SelectColumns(%0A%20%20%20%20%20%20%20%20%23%22Get%20Year%22%2C%0A%20%20%20%20%20%20%20%20%7B%22Region%22%2C%20%22Country%22%2C%20%22Year%22%2C%20%22File%22%7D%0A%20%20%20%20)%0Ain%0A%20%20%20%20%23%22Keep%20Final%22%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2119225%22%20slang%3D%22en-US%22%3ERe%3A%20Power%20Query%20-%20Split%20folder%20path%20with%20different%20structure%20into%20same%20columns%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2119225%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F295513%22%20target%3D%22_blank%22%3E%40Zdenek_Moravec%3C%2FA%3E%26nbsp%3BA%20quick-and-dirty%20solution%20attached.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

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

ContinentCountryYearFile
EuropeGermany2019file1.docx
EuropeGermany2019file1.docx
EuropeFrance2019file3.docx
EuropeFrance2019file4.docx
AMERICA 2019file5.docx
AMERICA 2020file6.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

7 Replies

@Zdenek_Moravec A quick-and-dirty solution attached.

 

best response confirmed by Zdenek_Moravec (Contributor)
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"

 

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

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.

@Zdenek_Moravec 

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

@Sergei Baklan @Riny_van_Eekelen 

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

Zdenek