cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
drorli
Helper I
Helper I

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

 

1 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]))),

View solution in original post

8 REPLIES 8
Greg_Deckler
Super User
Super User

@ImkeF @edhans 


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

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?

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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)

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?

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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]))),

View solution in original post

Hi @drorli ,

 

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

 

Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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
Community Champion
Community Champion

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.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.