Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Hi @Anonymous ,
Can you please share us some screenshots? How do you create "Invoke Custom Function1"? The function of Table.TransformColumns will not remove the data. So I think your problem might be caused by the step of "Invoke Custom Function1".
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Here are the screen shots, thanks for your assistance. Let me know if they need more explanation.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |