Error Handling if Data Source is Empty

Question

Am trying to fetch data from an api Source and create dash board on daily basis. The source have data some days and somedays doesn't have any data (It is perfectly fine).Whenever there is no data am getting message from API as below . I want to show "No Data" if the api doesnt provide any table value. Any help will be appreciated ![77192-image.png][1] [1]: /api/attachments/77192-image.png?platform=QnA

'''

let
Source = Csv.Document(Web.Contents(" https://abc.com" & Date.ToText(DateTime.Date(DateTime.LocalNow()), "MM/dd/yyyy") & "&csv-format=1"),[Delimiter=",", Columns=15, Encoding=1252, QuoteStyle=QuoteStyle.None]),

"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),

"Changed Type" = Table.TransformColumnTypes(xxxx), #"Renamed Columns" = Table.RenameColumns(xxxx)

in

"Renamed Columns"

'''

Answer 1

Hi @Gunasekaran, Guruprakash

Can't simulate your API so let's take the following query (make sure the query Regional Settings is set to EN-US otherwise the ChangeTypes step will fail):

let  
    Source = Web.Page(  
        Web.Contents("https://old.nasdaq.com/symbol/msft/financials?query=income-statement&data=quarterly")  
    ),  
    FirstDataTable = Table.First(Source)[Data],  
    ChangedTypes = Table.TransformColumnTypes(FirstDataTable,  
        {{List.First(Table.ColumnNames(FirstDataTable)), type text}} &  
        List.Transform(List.Skip(Table.ColumnNames(FirstDataTable)), each  
            {_, Currency.Type}  
        )  
    )  
in  
    ChangedTypes  

Above query modified to handle error in Source:

let  
    Source = try Web.Page(  
        Web.Contents("https://old.nasdaq.com/symbol/msft/financials?query=income-statement&data=quarterly")  
    ),  
    SourceHasData = Source[Value],  
    FirstDataTable = Table.First(SourceHasData)[Data],  
    ChangedTypes = Table.TransformColumnTypes(FirstDataTable,  
        {{List.First(Table.ColumnNames(FirstDataTable)), type text}} &  
        List.Transform(List.Skip(Table.ColumnNames(FirstDataTable)), each  
            {_, Currency.Type}  
        )  
    ),  
    SourceHasNoData = #table(type table [Source = text], {{"No Data"}}),      
    Result = if Source[HasError] then SourceHasNoData else ChangedTypes  
in  
    Result  

Now, to simulate an error in Source and output "No Data", change the https string (line #3) with i.e. "https://zzz.nasdaq...."

Just in case: Power Query M Error Handling chapter is here

EDIT Corresponding sample workbook avail. here