Results 1 to 5 of 5

Thread: How to handle error of table not present

  1. #1
    Acolyte GreenBoy's Avatar
    Join Date
    Apr 2015
    Posts
    21
    Articles
    0
    Excel Version
    Office 365

    Question How to handle error of table not present



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

  2. #2
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,184
    Articles
    0
    Excel Version
    Office 365 Subscription
    Can't you edit the query so that it no longer looks for that particular source? Hard to say without seeing your set-up.
    Ali
    Enthusiastic self-taught user of MS Excel!

  3. #3
    Acolyte GreenBoy's Avatar
    Join Date
    Apr 2015
    Posts
    21
    Articles
    0
    Excel Version
    Office 365
    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

  4. #4
    Acolyte horseyride's Avatar
    Join Date
    Nov 2017
    Posts
    72
    Articles
    0
    Excel Version
    Office 365 Pro Plus 1708.8431.
    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

    Code:
    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:

    Code:
    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"

  5. #5
    Acolyte GreenBoy's Avatar
    Join Date
    Apr 2015
    Posts
    21
    Articles
    0
    Excel Version
    Office 365
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •