Remove Errors Problem

Power Query
1

Hi I have a PQ that does a Combine and Transform. Some Files do not have the Table that the PQ queries in and so I get errors - see attached image.
To handle this I have a Remove error action in the Query.

However, it seems that if all the files have errors and are removed then I get a second error

how does one handle this ?

Thank You

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)"}),
>     #"Removed Errors1" = Table.RemoveRowsWithErrors(#"Removed Other Columns1", {"Transform File (2)"}),
>     #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Errors1", "Transform File (2)", Table.ColumnNames(#"Transform File (2)"(#"Sample File (13)"))),
>     #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Column1", type number}, {"Column2", type any}, {"Column3", type text}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", Int64.Type}, {"Column16", type any}, {"Column17", Int64.Type}, {"Column18", type any}, {"Column19", type text}, {"Column20", type any}, {"Column21", type any}, {"Column22", type any}, {"Column23", type any}, {"Column24", type any}, {"Column25", type text}, {"Column26", type text}, {"Column27", type number}, {"Column28", type text}, {"Column29", Int64.Type}, {"Column30", type any}, {"Column31", type any}, {"Column32", type any}, {"Column33", type any}, {"Column34", type any}, {"Column35", type any}}),
>     #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Source.Name", "Column15", "Column19"}),
>     #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([Column15] <> null) and ([Column19] <> null)),
>     #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Column15", "Value"}, {"Column19", "TransType"}})
> in
>     #"Renamed Columns"

Capture501 Capture500

2

Use try~otherwise in case of errors, here’s a link to the documentation.

https://docs.microsoft.com/en-us/powerquery-m/errors

3

Thank you for the link.
I have read it. However in my case I want to run the script only if there is a least one table with the target t able.
So how would I do it with a try else statement

Allister

4

Hi @AllisterB,

You would wrap the try~otherwise on the most outer expression, so if there is data, no error occurs and the target table will be retrieved.

An other approach could be to use the if~then~else statement, wrap a Table.IsEmpty around the result of the Removed Error step if the table is empty that will return true.

5

Hi @AllisterB, we’ve noticed that no response has been received from you since the 8th of October. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. 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 checkbox. Thanks!

6

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. Thanks!