Feb 09 2021 08:46 AM - edited Feb 09 2021 08:48 AM
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
Feb 09 2021 09:03 AM
@Zdenek_Moravec A quick-and-dirty solution attached.
Feb 09 2021 02:59 PM
SolutionSlightly 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"
Feb 09 2021 03:21 PM
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 |
Feb 09 2021 03:28 PM
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.
Feb 10 2021 12:36 AM
That's all in user interface, you may call them both in Transform and Add Column under Extract.
Feb 10 2021 02:22 PM
@Sergei Baklan @Riny_van_Eekelen
Task completed and two more functions learned. Thank you both!
Zdenek
Feb 11 2021 02:42 AM
@Zdenek_Moravec , glad to help
Feb 09 2021 02:59 PM
SolutionSlightly 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"