How to handle error of table not present

[GreenBoy]

Hi
Sorry if this is going to sound a little strange - i get 3 periodic reports from a client which contain Purchase Order information (PO), Goods Receipting Information (GRN), and what is called Requested Information (Req).

The PO and GRN files always have the same layout so no problems there, however the Req file can someday's contain not information, meaning nothing has been Requested or is in process to becoming a PO - and this report does not even contain a blank table, if literally has the text

No data returned for this view. This might be because the applied filter excludes all data.

This is in the cell that is usually the top right for the table, when it is present.

This data is issued automatically from the clients database, and they cant/ wont amend it.

Can anyone point me at some articles or a simple solution on how to handle this error, so the system doesn't throw up error messages, which spooks the hell out of the other users at my company, so i stop getting bugged every time there is a not data day?

Thanks in advance for any suggestions or advice.
Greenboy.

 

[AliGW]

Can't you edit the query so that it no longer looks for that particular source? Hard to say without seeing your set-up.

 

[GreenBoy]

Hi ALi

My setup basically looks into a folder where all the reports for Req are put - we keep all files in case of audit, and then look for the newest.

I am not on works PC, but the query is something like this

let
Source = Folder.Files("D:\Downloads\ICS Work"),
#"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".xlsx")),
#"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Date created", Order.Descending}}),
#"Kept First Rows" = Table.FirstN(#"Sorted Rows",1),
#"Filtered Hidden Files1" = Table.SelectRows(#"Kept First Rows", each [Attributes]?[Hidden]? <> true),
#"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from ICS Work", each #"Transform File from ICS Work"([Content])),
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File from ICS Work"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from ICS Work", Table.ColumnNames(#"Transform File from ICS Work"(#"Sample File"))),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"Column1", type any}, {"Column2", type text}, {"Column3", type text}, {"Column4", type any}, {"ICS00064", type any}, {"Column6", type text}, {"Column7", type any}})
in
#"Changed Type"

From here i manipulate the data - so what i am looking to do is to look at the 5th row of the fist column of the sheet - to see if its a table or the text in my first post - if its the text i want to end the query - which is what i dont know if its possible in PQ.

Thanks for the reply

 

[horseyride]

If I had a XLS file that normally would be multiple rows like

a/b/c
1/2/3
3/2/1
etc

and instead it sometimes came in with "No data returned for this view. This might be because the applied filter excludes all data." then something like this could work -- check number of records on that table. If there are zero then replace the table with another table containing headers and a single row of dummy data that later code will just not find use for, but will process without error, like this:

a/b/c
0/0/0

let Source = Excel.Workbook(File.Contents("C:\temp2\a.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
UseMe = if Table.RowCount(#"Promoted Headers") > 0 then #"Promoted Headers" else Table.FromRecords({[a = 0, b = 0, c=0]}) 
in UseMe

If the source was a CSV, I might consider just replacing the data as I read it in, like:

let Source = Text.Replace(Text.FromBinary(File.Contents("C:\temp2\b.txt")), "No data returned for this view. This might be because the applied filter excludes all data.","a,b,c#(lf)0,0,0"),
Continue = Lines.FromText(Source),
#"Converted to Table" = Table.FromList(Continue, Splitter.SplitTextByDelimiter(","), null, null, ExtraValues.Error),
#"Promoted Headers" = Table.PromoteHeaders(#"Converted to Table", [PromoteAllScalars=true])
in #"Promoted Headers"

 

[GreenBoy]

Thanks Horseyride

This forum is awesome, and you sir,are definitely a stalwart of Power Query. You should be an MVP given the level of fantastic support you give.

Thank you.

AliGW - thank you also for your comments - i take away from your post that i need to ensure i provide more relevant information when i post a query.

Thanks to all
GreenBoy