adding new columns when importing files using 'From Folder'

illusionek

Board Regular
Joined
Jun 21, 2014
Messages
104
Hello

I cannot figure out how to get new columns into my Power Query if the data source changes.

I import multiple CSV files from a specific folder using Get Data -> From File -> From Folder option

Recently CSV files have been amended where couple additional columns were added at the end. Unfortunately my import query doesnt pick-up new columns at all.

I am desperate to get them asap but would like to avoid option where I have to redo the whole query from scratch.

Please help :biggrin:

Code:
let    Source = Folder.Files("\\XXX\Data"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from Sales Orders Report (2)", each #"Transform File from Sales Orders Report (2)"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from Sales Orders Report (2)"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Sales Orders Report (2)", Table.ColumnNames(#"Transform File from Sales Orders Report (2)"(#"Sample File (3)"))),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Table Column1",{{"SalesID", "Order Number"}, {"SalesOrigin", "Origin"}, {"HeaderCreatedDateTime", "Order Date"}, {"DeliveryDate", "RDD"}, {"CustomerAccountNumber", "Customer Account"}, {"CustomerName", "Customer Name"}, {"InvoiceAccountNumber", "Invoice Account"}, {"InvoiceAccountName", "Invoice Account Name"}, {"channel", "Channel"}, {"Sales_Channel", "Sales Channel"}, {"salessubchannel", "Sales Sub Channel"}, {"accountmanager", "Account Manager"}, {"InventorySite", "Inventory Site"}, {"Order_Status", "Order Status"}, {"WAXReleaseStatus", "Release Status"}, {"DocumentStatus", "Document Status"}, {"DeliveryMode", "Delivery Mode"}, {"LineNumber", "Line Number"}, {"Line_Status", "Line Status"}, {"ItemID", "Item"}, {"ItemName", "Item Description"}, {"subcategory1", "Cat 1"}, {"subcategory2", "Cat 2"}, {"ReportingGroup", "Reporting Group"}, {"QTYOrdered", "Ordered Qty"}, {"Delivered_Qty", "Delivered Qty"}, {"SalesUnit", "Unit"}, {"SalesPrice", "Sales Price"}, {"Line_Amount_GBP", "Line Amount GBP"}, {"Line_Amount_Curr", "Line Amount Curr"}, {"CurrencyCode", "Currency Code"}, {"CustomerPurchaseOrderNumber", "Customer Order Number"}, {"Hdr_Time", "Order Time"}, {"CreatedBy", "User ID"}, {"CreatedByName", "User Name"}, {"FSCreditBlocked", "Credit Hold"}, {"SalesType", "Sales Type"}, {"payment", "Payment Terms"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Order Date", type date}, {"RDD", type date}, {"Ordered Qty", type number}, {"Delivered Qty", type number}, {"Sales Price", type number}, {"Line Amount GBP", type number}, {"Line Amount Curr", type number}, {"Order Time", type time}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Origin] <> "Debit Note")),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each [Category] <> "Service")
in
    #"Filtered Rows1"
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Many thanks for your help. Amending the sample file did the trick. I havent even realised until now this query existed ?
 
Upvote 0

Forum statistics

Threads
1,224,815
Messages
6,181,136
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top