Source Files Not Consistent - Breaks Query

[a68tbird]

Hello All -
Looking for some clever suggestions on how to handle my situation. I have a report that gathers source data from a folder of invoices. I have a function that cleans and models the data in the invoice, and then I call that function in my main query. The problem is that the provider of the invoices changed the layout - they have changed the name of a column, and added a new column. This now breaks my query since my column headers will be all wrong. What is the best way to handle this so that my report can still model the data from old invoices, while recognizing the new layout.

Not sure if this will help or not, but here is my query:

let
    Source = Folder.Files("C:\Users\mycomputer\Billing - Documents"),
    FiltereXLSX = Table.SelectRows(Source, each ([Extension] = ".xlsx")),
    FilterYTD = Table.SelectRows(FiltereXLSX, each Date.IsInYearToDate([Date created])),
    RemoveColumns = Table.SelectColumns(FilterYTD,{"Folder Path", "Name"}),
    InvokeFunction = Table.AddColumn(RemoveColumns, "fxInvoice", each fxInvoice([Folder Path], [Name])),
    Expand = Table.ExpandTableColumn(InvokeFunction, "fxInvoice", {"Handling Fees", "VAT", "Tax", "Shipping", "Country", "Order ID", "Order", "Date"}, {"Handling Fees", "VAT", "Tax", "Shipping", "Country", "Order ID", "Order", "Date"}),
    RemoveFolderNameCols = Table.RemoveColumns(Expand,{"Folder Path", "Name"}),
    ReorderColumns = Table.ReorderColumns(RemoveFolderNameCols,{"Date", "Order", "Order ID", "Country", "Handling Fees", "VAT", "Tax", "Shipping"}),
    AddTotalShipping = Table.AddColumn(ReorderColumns, "TotalShipping", each [Handling Fees]+[VAT]+[Tax]+[Shipping]),
    ChangeTypes = Table.TransformColumnTypes(AddTotalShipping,{{"Date", type date}, {"Order ID", Int64.Type}, {"Handling Fees", type number}, {"VAT", type number}, {"Tax", type number}, {"Shipping", type number}, {"TotalShipping", type number}})
in
    ChangeTypes

And here's the function:

(myPath,myFile) =>

let
    Source = Excel.Workbook(File.Contents(myPath&myFile), null, true),
    Orders_Sheet = Source{[Item="Orders",Kind="Sheet"]}[Data],
    RemoveTopRows = Table.Skip(Orders_Sheet,2),
    PromoteHeaders = Table.PromoteHeaders(RemoveTopRows, [PromoteAllScalars=true]),
    FilterForNull = Table.SelectRows(PromoteHeaders, each ([Order ID] <> null)),
    ReplaceDash = Table.ReplaceValue(FilterForNull,"-","",Replacer.ReplaceText,{"Tax", "VAT"}),
    ChangeDataTypes = Table.TransformColumnTypes(ReplaceDash,{{"Tax", type number}, {"VAT", type number}, {"Date", type date}, {"Products", type number}, {"Discount", type number}, {"Shipping", type number}, {"Digitization", type number}, {"Branding", type number}, {"Total", Currency.Type}, {"Order ID", Int64.Type}}),
    ReplaceNulls = Table.ReplaceValue(ChangeDataTypes,null,0,Replacer.ReplaceValue,{"Tax", "VAT"}),
    AddFeeColumn = Table.AddColumn(ReplaceNulls, "Handling Fees", each [Total]-([Products]+[Discount]+[Shipping]+[Digitization]+[Branding]+[Tax]+[VAT]), type number),
    ReorderColumns = Table.ReorderColumns(AddFeeColumn,{"Date", "Order", "Order ID", "Country", "State", "Payment Instrument", "Status", "Products", "Discount", "Shipping", "Digitization", "Branding", "Tax", "VAT", "Handling Fees", "Total"}),
    RemoveColumns = Table.SelectColumns(ReorderColumns,{"Handling Fees", "VAT", "Tax", "Shipping", "Country", "Order ID", "Order", "Date"}),
    RoundNumbers = Table.TransformColumns(RemoveColumns,{{"Handling Fees", each Number.Round(_, 2), type number}})
in
    RoundNumbers

Am I going to have to create two separate functions and two aggregate queries - one for each type of invoice - and then merge the two queries? Hoping there may be a more eloquent way to do this.

Thanks!

 

[GraH]

Foresee all the column names in your function, but add MissingField.Ignore.
Query should not break.

 

[a68tbird]

@GraH Wow! I had no idea this existed! MissingField.Ignore works perfectly, and with minimal update to the function code.

Thanks very much!

 

[GraH]

Glad to read you we're able to follow the suggestion. It's such an event, where the UI let's us down.