Error Handling for API that returns empty dataset

2013smt — Wed, 17 Mar 2021 13:59:26 GMT

Hi,

When I attempt to refresh a dataset (Source[data]) that is empty and error is thrown:

Expression.Error: The column 'Column1' of the table wasn't found.
Details:
Column1

Below is M Query call to API.

let

HeaderKey = [Headers =[#"api-key"="xyz"]],
SourceUrl = "https://myapi.com/api/v1/disabledtrain",
Source = Json.Document(Web.Contents(SourceUrl,HeaderKey)),

Data = Source[data],
#"Converted to Table" = Table.FromList(Data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "DisabledTrain"}}),
#"Expanded DisabledTrain" = Table.ExpandRecordColumn(#"Renamed Columns", "DisabledTrain", {"affected", "goId", "trainId", "connectingTrnId", "trainArriveTime", "connectingTrainDepartTime", "stationId", "majorLocCd", "updatedBy", "updatedDate"}, {"DisabledTrain.affected", "DisabledTrain.goId", "DisabledTrain.trainId", "DisabledTrain.connectingTrnId", "DisabledTrain.trainArriveTime", "DisabledTrain.connectingTrainDepartTime", "DisabledTrain.stationId", "DisabledTrain.majorLocCd", "DisabledTrain.updatedBy", "DisabledTrain.updatedDate"}),
#"Renamed Columns1" = Table.RenameColumns(#"Expanded DisabledTrain",{{"DisabledTrain.affected", "Affected"}, {"DisabledTrain.connectingTrainDepartTime", "Connect Depart Time"}, {"DisabledTrain.connectingTrnId", "Connecting Train"}, {"DisabledTrain.goId", "General Order"}, {"DisabledTrain.majorLocCd", "Major Location Code"}, {"DisabledTrain.stationId", "Station"}, {"DisabledTrain.trainArriveTime", "Train Arrive Time"}, {"DisabledTrain.updatedBy", "Updated By"}, {"DisabledTrain.updatedDate", "Updated Date"}, {"DisabledTrain.trainId", "Train"}})
in
#"Renamed Columns1"

How can I handle this situation?

Re: Error Handling for API that returns empty dataset

Anonymous — Fri, 19 Mar 2021 06:09:02 GMT

Hi 2013smt ,

Could you please confirm that whether it exist the field named "Column1" in your source? Please provide the related screenshot of source data in order to make troubleshooting. You can also refer the content in the following links to resolve your problem.

The column of the table wasn't found

Power Query: Preventing "The column of the table wasn't found" error

Best Regards

Re: Error Handling for API that returns empty dataset

Anonymous — Thu, 01 Apr 2021 09:58:40 GMT

Hi 2013smt ,

Whether your problem has been resolved?

Best Regards

topic Error Handling for API that returns empty dataset in Developer

Error Handling for API that returns empty dataset

Re: Error Handling for API that returns empty dataset

Re: Error Handling for API that returns empty dataset