Error on Combine and transform

Power Query
1

I get this error when I run the script. O interestingly it affects the four last files in eth folder (when in alpha sort).

Error = An error occurred in the ‘Transform File (2)’ query. Expression.Error: The column ‘r_ReportDate’ of the table wasn’t found.
Details: r_ReportDate

Does anyone have any ideas?

Allister

let
Source = #“Folder Parameter”,
#“Filtered Hidden Files1” = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
#“Invoke Custom Function1” = Table.AddColumn(#“Filtered Hidden Files1”, “Transform File (2)”, each #“Transform File (2)”([Content])),
#“Renamed Columns1” = Table.RenameColumns(#“Invoke Custom Function1”, {“Name”, “Source.Name”}),
#“Removed Other Columns1” = Table.SelectColumns(#“Renamed Columns1”, {“Source.Name”, “Transform File (2)”}),
#“Expanded Transform File (2)” = Table.ExpandTableColumn(#“Removed Other Columns1”, “Transform File (2)”, {“r_ReportDate”, “r_ReportFreq”, “R_ReportLastReportDate”, “r_ReportPeriod”, “r_Version”}, {“r_ReportDate”, “r_ReportFreq”, “R_ReportLastReportDate”, “r_ReportPeriod”, “r_Version”}),
#“Added Custom” = Table.AddColumn(#“Expanded Transform File (2)”, “Custom”, each Text.Start([Source.Name],3)),
#“Reordered Columns” = Table.ReorderColumns(#“Added Custom”,{“Source.Name”, “Custom”, “r_ReportDate”, “r_ReportFreq”, “R_ReportLastReportDate”, “r_ReportPeriod”, “r_Version”}),
#“Renamed Columns” = Table.RenameColumns(#“Reordered Columns”,{{“Custom”, “Branch Code”}}),
#“Source Name” = #“Renamed Columns”{13}[Source.Name]
in
#“Source Name”

2

@AllisterB,

The error indicates below:

The column ‘r_ReportDate’ of the table wasn’t found.

So, it seems that in one of those affected files, this column(r_ReportDate) does not exist and that is why you are getting this error.

Please provide some more context if it is not the case.

Kind Regards,
Hafiz

3

Thanks for posting your question @AllisterB. To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.

Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

Including all of the above will likely enable a quick solution to your question.

4

Thank you
Is it likely that the file at fault is the first one in the alphasort of files in the folder not to be imported. Does PQ stops importing more data if it comes across a file that has a fault?

5

Hi @AllisterB,

I’d suggest to use the #table intrinsic to define an empty table with same data structure and use that to replace possible error values in the #“Expanded Transform File (2)” step

try Table.ExpandTableColumn(#"Removed Other Columns1", 
    "Transform File (2)", 
        {"r_ReportDate", "r_ReportFreq", "R_ReportLastReportDate", "r_ReportPeriod", "r_Version"}, 
        {"r_ReportDate", "r_ReportFreq", "R_ReportLastReportDate", "r_ReportPeriod", "r_Version"}
)
otherwise 
    #table( 
        {"r_ReportDate", "r_ReportFreq", "R_ReportLastReportDate", "r_ReportPeriod", "r_Version"}, 
        {}
    )

I hope this is helpful

2 Likes
6

Hi @AllisterB, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!