Currently loading from a folder all of the XLSX monthly files from a vendor. Occassionally the vendor will send the headers in lower case instead of UPPER case. When this occurs the Transform function cause columns to exclude data from the lower case files until the headers are manually changed to UPPER case. Below is the load query from the advanced error. Is there a method that can be implemented that would perform this function (HeadersUpperCase = Table.TransformColumnNames(Source,Text.Upper)) during the #"Expanded Table Column1" line of the let query?
let
Source = Folder.Files("c:\PBIClar\My_Testing"),
#"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Extension], ".xls")),
#"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each Text.StartsWith([Name], "Batch Error Report")),
#"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows1", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from Batch Error (3)", each #"Transform File from Batch Error (3)"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from Batch Error (3)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Batch Error (3)", Table.ColumnNames(#"Transform File from Batch Error (3)"(#"Sample File (6)"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"CLIENT CODE", type text}, {"BASE ERROR CODE", type text}, {"ERROR DESCRIPTION", type text}, {"STATUS CATEGORY", type text}, {"SOURCE IND", type text}, {"TRANS TYPE", type text}, {"CLAIMTYPE", type text}, {"PAYERNAME", type text}, {"DOCUMENT NUMBER", type text}, {"PAT ACCT NUMBER", type text}, {"TOTAL CHARGE", Int64.Type}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1)
in
#"Added Index"
A couple different methods have been attempted with no success. Any help is greatly appreciated.
