Forum Discussion
How to stop Refresh-All in Excel on 1st Error
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
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]))),
8 Replies
Hi Anonymous ,
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?
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)
