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.
(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"