How can I save a path criteria when I import from

Question

I import many textfiles from a folder with subfolders, everything works fine so far. My folder has the same text file in many folders for different Periods, so e.g.:

AllData\201501\Revenues.txt
AllData\201502\Revenues.txt
AllData\201502\Revenues.txt
.....

I need a Part from the path like "201501" inside my query which is preparing the Revenues. I can't write the date into the .txt file, is generated from third party.

How can I join the FullPath on my text file rows? Cutting it after that is simple.

Answer 1

(Sorry if any of the features below aren't in the public release of Power Query yet--this works in the Power BI Designer today!)

From Folders gives me:

Add a custom step using Text.FromBinary with a column named Text to get the file text:

Then remove every column except Folder Path and Text:

From there, split the Folder Path column by delimiter \

And then only keep the path column with the date:

Next, merge the two text columns with a custom :, and you're done!

Run this to set up the data used above:

cd c:\
mkdir alldata
cd alldata
mkdir 201501
mkdir 201502
mkdir 201503
echo Made $100 > 201501\Revenues.txt
echo Made $200 > 201502\Revenues.txt
echo Made $300 > 201503\Revenues.txt

Full M source code for this transformation:

let
    Source = Folder.Files("c:\AllData"),
    #"Added Custom" = Table.AddColumn(Source, "Text", each Text.FromBinary([Content])),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Folder Path", "Text"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Other Columns","Folder Path",Splitter.SplitTextByDelimiter("\"),{"Folder Path.1", "Folder Path.2", "Folder Path.3", "Folder Path.4"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Folder Path.1", type text}, {"Folder Path.2", type text}}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Changed Type",{"Folder Path.3", "Text"}),
    #"Merged Columns" = Table.CombineColumns(#"Removed Other Columns1",{"Folder Path.3", "Text"},Combiner.CombineTextByDelimiter(": ", QuoteStyle.None),"Revenues")
in
    #"Merged Columns"