none
Power Query occassionally connects, seems to load, but returns only 200 rows with 1 error RRS feed

  • Question

  • I have a Power Query that i have used for few weeks, successfully, but occasionally requires being run a few times to return records.  Just lately it seems to require run more than a few times to make it work. 

    When it doesn't return records , it seems to connects and start retrieving data, but returns only randomly like 100 - 200 rows with 1 error.  The error doesn't have any message. So its hard to troubleshoot from that perspective.

    Query appends two other large and merges in a few others. The data sources are either csv files, Sharepoint Lists and tables in workbook.

    I haven't identified anything in terms of environment or query changes that might cause this.

    When it finally does retrieve all records,  it might be after i have done the following things, but i can't say for certain exactly what did the trick:

    * to run query multiple times

    *maybe simply changing anything in query resolves issue

    * making upstream queries (that are merged or appended into this query)  to Not Load to worksheet or model, eg just have connection

    I'd think this type of error and situation while not common is encountered by others.

    Does this ring a bell for anyone, and if so, can you please shed some light on the reasons and resolutions for this?

    Thanks!


    • Edited by sitrucp Tuesday, February 17, 2015 7:27 PM
    Tuesday, February 17, 2015 6:43 PM

Answers

  • If you click on "1 error" it should show the editor with a query that will select the rows with errors. This unfortunately might not show all errors for various reasons, but you should try it.

    If that doesn't work, here's an example of how to retrieve error details.

    Suppose the following query:

    = Table.FromRecords({[A="B", B="C" + 1]})

    Notice how we're using the + operator with a string and number. This will result in an error.

    I can create a custom column that uses the 'try' operator over the B column. This returns a record with details which I then expand to retrieve the message.

    let
        Source = Table.FromRecords({[A="B", B="C" + 1]}),
        #"Added Custom" = Table.AddColumn(Source, "Custom", each try [B]),
        #"Expand Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"HasError", "Error"}, {"Custom.HasError", "Custom.Error"}),
        #"Expand Custom.Error" = Table.ExpandRecordColumn(#"Expand Custom", "Custom.Error", {"Message"}, {"Custom.Error.Message"})
    in
        #"Expand Custom.Error"

    Let me know how this works for you.

    Tristan

    Wednesday, February 18, 2015 10:45 PM
    Moderator
  • Hi,

    I don't know why your query gives you these transient errors. If you were using Web.Page I would've blamed that but the data sources you're using seem like they should be stable. It could be connectivity issues.

    You could modify your query to display the error using the 'try' operator or click on it in the editor and see its details.

    If this still fails, you can enable tracing and look at the trace files to see if there's anything there or send them to us with the send-a-frown and we could have a look at them for you.

    Tristan

    Wednesday, February 18, 2015 6:24 PM
    Moderator
  • Hi,

    You would need to do before you apply the operation to keep only the errors.

    Another solution is to enable tracing from the 'Options' panel and inspect the trace files.

    I'm sorry that our current error handling experience is so difficult to use from the UI. Could you use the "Send a Frown" feature in PQ to send our feedback alias details about your issue? We can help you diagnose this in detail. This would also give more visibility internally that we need to improve the error handling experience.

    Regards,
    Tristan

    Monday, March 2, 2015 10:24 PM
    Moderator

All replies

  • Hi,

    I don't know why your query gives you these transient errors. If you were using Web.Page I would've blamed that but the data sources you're using seem like they should be stable. It could be connectivity issues.

    You could modify your query to display the error using the 'try' operator or click on it in the editor and see its details.

    If this still fails, you can enable tracing and look at the trace files to see if there's anything there or send them to us with the send-a-frown and we could have a look at them for you.

    Tristan

    Wednesday, February 18, 2015 6:24 PM
    Moderator
  • Hi Tristan,

    I haven't used the 'try' operator yet.

    Can't see it on the Power QUery formula category page.

    Where does it go and do you have syntax example.

    Btw, below is screenshot of the query result. It has 1 error but no info about it.

    Thanks


    • Edited by sitrucp Wednesday, February 18, 2015 7:36 PM
    Wednesday, February 18, 2015 7:11 PM
  • If you click on "1 error" it should show the editor with a query that will select the rows with errors. This unfortunately might not show all errors for various reasons, but you should try it.

    If that doesn't work, here's an example of how to retrieve error details.

    Suppose the following query:

    = Table.FromRecords({[A="B", B="C" + 1]})

    Notice how we're using the + operator with a string and number. This will result in an error.

    I can create a custom column that uses the 'try' operator over the B column. This returns a record with details which I then expand to retrieve the message.

    let
        Source = Table.FromRecords({[A="B", B="C" + 1]}),
        #"Added Custom" = Table.AddColumn(Source, "Custom", each try [B]),
        #"Expand Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"HasError", "Error"}, {"Custom.HasError", "Custom.Error"}),
        #"Expand Custom.Error" = Table.ExpandRecordColumn(#"Expand Custom", "Custom.Error", {"Message"}, {"Custom.Error.Message"})
    in
        #"Expand Custom.Error"

    Let me know how this works for you.

    Tristan

    Wednesday, February 18, 2015 10:45 PM
    Moderator
  • Thanks for the details. I haven't had chance to test this yet.  I will report back when i do that.
    Sunday, February 22, 2015 7:01 PM
  • Hi Tristan,

    Had issue occur again.  I hadn't fully read the steps above, but in my case, the Error query has no lines at all, so there is no way to 'try' anything.

    I am looking for troubleshooting steps.  I have the logging on but not sure what to look for or how to interpret audit log.  Are there perhaps some instructions or notes about Power Query audit logs in an MS technical document somewhere?

    THanks!


    Monday, March 2, 2015 9:20 PM
  • Hi,

    You would need to do before you apply the operation to keep only the errors.

    Another solution is to enable tracing from the 'Options' panel and inspect the trace files.

    I'm sorry that our current error handling experience is so difficult to use from the UI. Could you use the "Send a Frown" feature in PQ to send our feedback alias details about your issue? We can help you diagnose this in detail. This would also give more visibility internally that we need to improve the error handling experience.

    Regards,
    Tristan

    Monday, March 2, 2015 10:24 PM
    Moderator
  • Ok, i guess in my case I wouldn't know which column to apply the "each try" part to as I  don't know which column has error.

    And I am afraid I can't use your "Send a Frown" feature because the  data cannot be sent off premises : )

    Is there anything I can look for myself in the trace files? Some keywords that might indicate issues?

    Thanks!


    • Edited by sitrucp Tuesday, March 3, 2015 1:28 AM
    Tuesday, March 3, 2015 1:27 AM
  • Ok, i guess in my case I wouldn't know which column to apply the "each try" part to as I  don't know which column has error.

    And I am afraid I can't use your "Send a Frown" feature because the  data cannot be sent off premises : )

    Is there anything I can look for myself in the trace files? Some keywords that might indicate issues?

    Thanks!


    Hi TechDavie,

    I was facing a similar situation like yours, in my case I had 88 columns and a larger number of error. As it turned out, some of the columns that were set as numeric had non-numeric values and that was causing grief to Power Query. I would suggest if you could do a bit of data profiling.

    your last bet, and not a nice way to go, would be to add a remove error step in the end of the query and see if that does the trick?

    Thanks

    Kaz

    Friday, September 16, 2016 8:47 AM