OLD DB or ODBC Error

[Anonymous]

Hi,

I'm pretty new to PBI...
I have a query that is pulling info from a Folder that we regularly load remittance files to. The files usually come in a standard format and have all the same columns but once in a while they have an additional field - which breaks my query.

Is there a way to edit the query so that it won't break every time a file comes in with an additional or missing column??

Going thru the applied steps, the errors show up on the step "Invoke Custom Function 1" step, and in the Transofrm File column, about half are errors.

I copied this from the Advanced Editor... but if there's a setting in parameters somewhere I could change rather than editing the code, that would be nice!

Thank you!

let
    Source = Folder.Files("Y:\MHS\AuthentiCare\CDNM Source Data\835 Remittance Advice"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File")))

[v-lid-msft]

Hi @Anonymous ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We suggest you to delete the URI in your Query.

If you want the static column to use, we can add a selectcolumn function after source step, then the following steps will not have error if you get extra column in source:

let
    Source = ,
    SelectWantedColumn = Table.SelectColumns(Source, Table.ToList(ColumnNameTable),MissingField.UseNull),
    #"Changed Type" = Table.TransformColumnTypes(SelectWantedColumn,{{"A", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}, {"D", Int64.Type}})

in
    #"Changed Type"

The ColumnNameTable is from a EnterTable, you can also import it from a excel/csv file.

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.