Forum Discussion
Retain file name column when using Folder as a data source - Power Query M
Actually, it's pretty easy with this line of code:
= Table.AddColumn(Source, "Custom", each Table.PromoteHeaders(Csv.Document([Content],[Delimiter=";", Encoding=1252])))
Replace your Step 2&3 with it.
It will retain all the metadata from the 1st step and add the content in an additional custom column.
If your csv's have all the same headers, you simply expand this column and the headers of the 1st file will be shown - and expand all other files on these cols as well.
If there are differnt cols - just come back & I'll post the code for the auto-expand of different headers.
Your code should look like this:
let
Source = Folder.Files("C:\Users\pbakaric\Google Drive\DFM Dashboard\Reports"),
#"Added Custom" = Table.AddColumn(Source, "Custom", each Table.PromoteHeaders(Table.Skip(Csv.Document([Content], [Delimiter=";", Columns=4, Encoding=1252, QuoteStyle=QuoteStyle.None]),1))),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Name", "Custom"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Datum", "Menge", "Wert", "Kommentar"}, {"Datum", "Menge", "Wert", "Kommentar"})
in
#"Expanded Custom"
Check out the video as well, as there you'll find another method, showing how to use an explicit function to do that. This might be more convenient if there are more steps to perform an the nesting into the #"Added Custom"-steps becomes too confusing.
Hi ImkeF,
I'm following the steps outlined in the video to retain the file names and I'm receiving an error at the very last step, when I add a custom column:
= Table.AddColumn(Source, "Custom", each Folder([Content]))
The error I am receiving is as follows: "Expression error: The name 'Folder" wasn't recognized. Make sure it's spelled correctly."
Here's the function I created:
= (Content) =>
let
// Source = Folder.Files("C:XXXContentDrilldown"),
// #"C:XXXContentDrilldown csv" = Source{[#"Folder Path"="C:\XXXContentDrilldown\",Name="XXXContent Drilldown 20160501-20160531.csv"]}[Content],
#"Imported CSV" = Csv.Document(Content,[Delimiter=",", Columns=7, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Changed Type" = Table.TransformColumnTypes(#"Imported CSV",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}}),
#"Removed Top Rows" = Table.Skip(#"Changed Type",6),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows")
in #"Promoted Headers"
I would appreciate any insight.
Thanks!
Margaryta
Hi Margaryta,
The error not recognize the name "Folder" refers to the name of the function (the name of the query):
So you either rename the function/query or edit your code to the name you gave it already.
Thank you ImkeF, it works now. I appreciate your propmt response.
Regards,
Morvaryt
Hi I am trying a variation of this unsuccessfully. I need to add columns 'Country' and 'Broker' to the tables which reside in the 'Promoted Headers' column. The step needs to be created between steps "Changed Type" and "Promote Headers" Any help greatly appreciate.
let
Source = FolderPath,
Custom1 = Folder.Contents(Source),
#"Removed Other Columns1" = Table.SelectColumns(Custom1,{"Name", "Content"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns1",{{"Name", "FolderName"}, {"Content", "FolderContent"}}),
#"Expanded Content" = Table.ExpandTableColumn(#"Renamed Columns", "FolderContent", {"Content", "Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}, {"Content", "Name", "Extension", "Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}),
#"Added Custom" = Table.AddColumn(#"Expanded Content", "Custom", each if [Extension] = ".csv" then Csv.Document([Content]) else Excel.Workbook([Content])),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"FolderName", "Name", "Extension", "Custom"}),
#"Added Custom1" = Table.AddColumn(#"Removed Other Columns", "PromoteHeaders", each if [Extension] = ".csv" then Table.PromoteHeaders([Custom]) else Table.PromoteHeaders([Custom]{0}[Data])),
#"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom1", "FolderName", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Country", "Broker"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Country", type text}, {"Broker", type text}}),
PromoteHeaders = #"Changed Type"[PromoteHeaders],
#"TableCombine" = Table.Combine(PromoteHeaders),
#"Removed Columns" = Table.RemoveColumns(TableCombine,{"", "Column9"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Gross Weight", type number}, {"Net Weight", type number}, {"Value AWB", type number}, {"Origin Total Freight", type number}, {"Non-EU Freight", type number}, {"EU Freight", type number}, {"Value Duty", type number}, {"Total Freight Eur", type number}, {"Import duty", type number}, {"PCS", type number}, {"Customs VAT", type number}}),
#"OutputTable" = Function.Invoke(ParentFunction,{#"Changed Type1",paramCountry,paramBroker})in
#"OutputTable"
