none
How do I test for a refresh error in a scheduled PowerQuery RRS feed

  • Question

  • I have a number of Excel files using PowerQuery to gather data from a remote server.  The updates run automatically every evening and I have started seeing incomplete refreshes.  How do I capture or test for this error shown in the image below?

    thanks

    Friday, March 20, 2015 11:53 PM

Answers

  • Hi richtheh,

    Maybe in power shell you could check to see if the refreshed file size is less than some threshold (i.e. the file size when the refreshed table is empty), and in that case call your error handling logic?

    Just an idea.

    Ehren

    Monday, March 30, 2015 11:43 PM
    Owner

All replies

  • Could you share which error you see when you navigate to the error of the relevant query in the Workbook Queries pane?

    Can you elaborate how you automate the refresh? Perhaps you can share a snippet of the code here.

    Saturday, March 21, 2015 1:39 PM
  • Gil,

    It looks like a remote server error and I am working with my vendor to resolve that issue.

    But I am also hoping to be able to test for or capture the table(s) that are not refreshing for follow up action later or the next day.

    I am currently using a PowerShell script to capture all the files I need to refresh and feed them into a foreach loop to open Excel, refresh, save and count rows updated. Here is my code for that portion of my script.

    $connsource = "\\...\sql source files\toberefreshed\"
    $conntarget = "\\...\sql source files\tobeimported\"
    $d = get-date -displayhint date

    $files = Get-ChildItem -path $connsource -Filter *xlsx |
        where-object {$_.LastWriteTime -le ($d.adddays(0))} |
        Select-object -ExpandProperty name

    $filescount = ($files | Measure-Object)
    $fc = $filescount.count

    $gd = get-date
    $inputstring =  "$gd : $fc files to be refreshed"
    Add-Content -Path $logpath -Value $inputstring -Force
    start-sleep -s 2

    foreach($f in $files)
    {
    $a = New-Object -ComObject Excel.Application
    $a.Visible = $True
    start-sleep -s 2
    $b = $a.workbooks.Open("$connsource$f");
    start-sleep -s 5
    #log refresh start time
    $gd = get-date
    $inputstring1 =  "$gd : $f refreshed from eB started"
    Add-Content -Path $logpath -Value $inputstring1 -Force
    start-sleep -s 5

    $b.refreshall()
    $a.CalculateUntilAsyncQueriesDone()
    start-sleep -s 5
    $a.activeworkbook.save()
    start-sleep -s 5
    $b.save()
    start-sleep -s 5
    $b.saveas("$connsource$f.csv",6)
    #log refresh finish time
    $gd = get-date
    $inputstring2 =  "$gd : $f refreshed from eB finished"
    Add-Content -Path $logpath -Value $inputstring2 -Force
    start-sleep -s 5

    Bear with me on any poor syntax or bad code.  I'm still learning PowerShell and am sort of piecing this all together as I go from step to step.  The code basically works and I am trying to add some error handling or identify problems with the data rather than reviewing table by table.

    Thank you again and let me know if there are other questions.

    Tuesday, March 24, 2015 12:11 AM
  • Hi richtheh,

    Maybe in power shell you could check to see if the refreshed file size is less than some threshold (i.e. the file size when the refreshed table is empty), and in that case call your error handling logic?

    Just an idea.

    Ehren

    Monday, March 30, 2015 11:43 PM
    Owner