question

GunasekaranGuruprakash-1265 avatar image
0 Votes"
GunasekaranGuruprakash-1265 asked ·

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

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

1 Answer

Lz-3068 avatar image
0 Votes"
Lz-3068 answered ·

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

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

!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 Votes 0 ·
screenshot.png (100.5 KiB)
Lz-3068 avatar image Lz-3068 GunasekaranGuruprakash-1265 ·

@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 Votes 0 ·

@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 Votes 0 ·
Show more comments