Error Handling if Data Source is Empty

Question

question

GunasekaranGuruprakash-1265 asked · Mar 12 at 10:42 AM

Error Handling if Data Source is Empty

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]: /answers/storage/attachments/77192-image.png

'''

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"

'''

power-query-dataflows

image.png (10.1 KiB)

10 |1000 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 1 · 2021-03-12T14:11:09.437Z

Lz-3068 answered · Mar 12 at 02:11 PM

Hi @GunasekaranGuruprakash-1265

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

· 4 ·

10 |1000 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GunasekaranGuruprakash-1265 · Mar 12 at 02:33 PM

!Thanks for the reply

When i applied try am getting error (Token Literal expected) in the code :
SourceHasNoData = #table(type table [Source = text], {{"No Data"}}),

77229-screenshot.png

0 ·

screenshot.png (100.5 KiB)

Lz-3068 GunasekaranGuruprakash-1265 · Mar 12 at 05:21 PM

@GunasekaranGuruprakash-1265
I can't be sure with the picture you posted but I think this is due the (there might more than 1 => check everywhere)  I see before { {"No Data"}} => remove/delete these chars. This line should say:

SourceHasNoData = #table(type table [Source = text], { {"No Data"}}),

I have noticed this  if often coming from copy/paste to/from this site :((

0 ·

Lz-3068 Lz-3068 · Mar 12 at 05:48 PM

@GunasekaranGuruprakash-1265. I looked again at the picture you posted. Your Result line should say:

Result = if Source[HasError] then SourceHasNoData else #"Renamed Columns"

otherwise your query won't do what you expect

0 ·

Show more comments

question