How to stop Refresh-All in Excel on 1st Error

[drorli]

Hi,

I have an Excel which reads multiple peices of data from same source (namely Epics, Features and User-Stories from JIRA).

In this Excel I have defined a generic function getEntityJiraData() which gets a text parameter of the enity type and retrieves data from JIRA for that entity.

Each entity loads to a seperate sheets in the Workbook using a query were source is the result from the call to this function with a different parameter (i.e. getEntityJiraData("Epic")).

The getEntityJiraData() function called getResultsFromURL() one or more times (for pagination of over 1,000 records)

When I press "Refresh All", Excel attempts to refesh all the Sheets/Queries one after the other.

I am looking for a way to stop this in case of an error in the 1st sheet.

Main reason is that if there is an authentication error (i.e. user replaced his password) I don't want to attempt to refresh the 2nd and 3rd sheets with the same incorrect password (which locks the user out ...).

I saw some nice article on how to implement error handling (https://powerbi.tips/2020/01/handle-errors-in-data-refreshes-with-power-automate/) and I think I know what to wrap with 'try ()'. Question is what statement can I place in the 'otherwise ()' clause so that the next call to this function within the ongoing refresh will fail without calling again to source syste,

Function getResultsFromURL

= (filterUrl as text) => 
let
Source = 
 try 
  (
   Csv.Document(Web.Contents(filterUrl),[Delimiter=",", Encoding=65001])
  )
 otherwise
  (
   // How to stop refresh ?
   "Error"
  ),
 Result = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
 Result

[Greg_Deckler]

@ImkeF @edhans 

[ImkeF]

Hi @drorli ,

do you apply this function in a Table.AddColumn-function ?
If so, it wouldn't work, as there is now awareness of the previous row within it.

You would have to switch to the List.Generate-function instead.

Or do you have a different setting here?

[drorli]

Thanks for the prompt response @ImkeF .

Indeed I am using Table.AddColumn within getEntityJiraData().

Below are more details on the functions call-stack.

Any advice how to change in a manner to allow proper error-hanling (stop Refresh-All of Excel on 1st error) will be appreciated.

1. Outer layer: Epics query

let
    jiraData = getEntityJiraData("Epic"),

2.  In getEntityJiraData()

(entity as text) as table => 
let
    filterID = getEntityFilterID(entity),
    maxEntries = getEntityMaxRecords(entity),
    Source = getUrlsTable(filterID, maxEntries),
    jiraDataResult = Table.AddColumn(Source, "Results", each getResultsFromURL([ExtractURL])),
    jiraDataCollapsed = Table.SelectColumns(jiraDataResult, {"Results"}),

3. The URLs table is a simple table with 1 column called ExtractURL and with configurable number of entries (parameter maxEntries)

[ImkeF]

Hi @CNENFRNL ,

that's exactly the way to go.

Haven't checked the code closely, but the method is correct:
Check for errors in the 2nd function argument of List.Generate before moving on to iterate through the list.

@drorli  have you been able to try this out?

[drorli · Accepted solution]

Yes @ImkeF  it works and thank you @CNENFRNL for the support.

I had 2 issues with the sample code, one with the having a parameted named '' to used in the loop and other with the result needing casting from a List to a table.

Below is the final code I am using

    jiraDataListResult = 
        [
            URLsList = Source[ExtractURL],
            EachResult = getResultsFromURL(URLsList{0}),
            jiraResult = List.Generate(
                () => [counter = 0, EachResult = getResultsFromURL(URLsList{counter})],
                each not (try [EachResult])[HasError], 
                each [counter = [counter] + 1, EachResult = getResultsFromURL(URLsList{counter})],
                each EachResult
            )
        ][jiraResult],
    jiraDataCollapsed = Table.FromList(jiraDataListResult, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    jiraDataExpanded = Table.ExpandTableColumn(jiraDataCollapsed, "Column1",Table.ColumnNames(Table.Combine(jiraDataCollapsed[Column1])), Table.ColumnNames(Table.Combine(jiraDataCollapsed[Column1]))),

[v-xuding-msft]

Hi @drorli ,

Glad that you have resolved it! Please accept your answer as solution. Others will benefit from it. Thanks!!

[drorli]

Thank you @ImkeF and @CNENFRNL for the professional and clear response.

This week is a bit hectic for me and I did not find the time to test this solution. Based on reading the code I am confident it will stop the execution of pagination sequence within a query per entity (i.e. if I attempted to get up-to 3K entities and the fetch of 1st 1K fails, will not attempt next 2 iterations).

My original question relates to stopping the execution of different queries within the sequence of Refresh All of Excel. For example, if there is an error in execution of EPIC query I want Excel to stop the whole refresh sequence and not attempt to run the FEATURES query.

Thanks again, dror.

[CNENFRNL]

Hi, @drorli , thanks to @ImkeF 's hint, I also learnt a new pattern!

As to your issue, pls try to replace applied step "jiraDataResult" with the following code,

jiraDataResult = 
    [
        URLsList = Source[ExtractURL],
        jiraResult = List.Generate(
            () => [counter = 0, EachResult = getResultsFromURL(URLsList{counter})],
            each not (try [EachResult])[HasError], 
            each [counter = [counter] + 1, EachResult = getResultsFromURL(URLsList{counter})],
            each [EachResult]
        )
    ][jiraResult]

A list will be created with successful results returned by getResultsFromURL func; the iteration of ExtractURL column ceases on the 1st error occurrance. You may expand the list subsequently.

As I have no mockup dataset to test the code, it's only a pattern I come up with; highly likely it requires some tweaks.