adding new columns when importing files using 'From Folder'

[illusionek]

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

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"

 

[Matt Allington]

Have a read about how Power Query Combine works in my article. https://exceleratorbi.com.au/understanding-power-query-combine/. You need to find where the source columns are expanded in the sample file query. You may have to reexecute that step. Also make sure there is no “remove other columns” step that is removing the new columns.

 

[illusionek]

Many thanks for your help. Amending the sample file did the trick. I havent even realised until now this query existed ?