Power Query IF function then loop until?

[sauzee]

Hi everyone

I'm really new to power query and have created some basic code to scrape a table from a website (mainly through the UI with a couple of tweaks in advanced editor). It's a really great tool, especially for people like me who don't really know how to code. However, because of the way that websites loads the table it sometimes returns blank. It's not a huge amount of data so refreshing until it is non-blank should be fine.

let

    Source = Function.InvokeAfter(
()=> Web.Page(Web.Contents("https://www.sportingindex.com/spread-betting/golf/ryder-cup/group_c.1ed2996f-9381-40e8-bef8-0f7ae460b2ec/ryder-cup-outright-markets")),
#duration(0,0,0,0)),
    Data0 = Source{0}[Data],
    #"Removed Columns" = Table.RemoveColumns(Data0,{"", "Column6", "Column7", "Current"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{Table.ColumnNames(Data0){0}, "SPIN"}})
in

    #"Renamed Columns"

When the data I want is not returned by the query the first row will be "No markets available". So can anyone help with IF the first row under the header in column 1 is "No markets available" THEN LOOP the data import until row 1 column 1 <> "No markets available".

It feels like it should be really simple to me but I just can't find much online to help me out with it. I thought Function.InvokeAfter may help but no joy (is why it's set at zero above)

Thanks in advance for any help

 

[tallan]

Power Query does not have any looping constructs.

Since you are making a reference to Power Query (and not Get Data), I am assuming you are using a version of Excel. At least since Excel 2013 you have been able to schedule a background refresh or a refresh when opening workbook.

There is also a free version of the Power Update utility that you can download from here and use to schedule refreshes.

Tom
PowerPivotPro - Transforming your Business with Power Pivot and Power BI

 

[MarcelBeug]

Power Query does not have any looping constructs.

That's not correct. Power Query has 2 looping constructs: List.Generate and recursive functions.

Find below code using List.Generate. I put the wait to 1 second and I included a Counter to prevent endless looping,

From the results of step "Loop" you can see if there was any looping: if so, you get a list of tables, otherwise the list will be empty.

let
    Source = Function.InvokeAfter(
()=> Web.Page(Web.Contents("[URL]https://www.sportingindex.com/spread-betting/golf/ryder-cup/group_c.1ed2996f-9381-40e8-bef8-0f7ae460b2ec/ryder-cup-outright-markets[/URL]")),
#duration(0,0,0,0)),
    Data0 = Source{0}[Data],
    Loop = 
        List.Generate(() => [Table = Data0, Continue = Table.FirstValue = "No markets available", Counter = 1],
                      each [Continue],
                      each [Table = let
                                        Source = Function.InvokeAfter(
                                                  ()=> Web.Page(Web.Contents("[URL]https://www.sportingindex.com/spread-betting/golf/ryder-cup/group_c.1ed2996f-9381-40e8-bef8-0f7ae460b2ec/ryder-cup-outright-markets[/URL]")),
                                                  #duration(0,0,0,1)),
                                        Data0 = Source{0}[Data]
                                    in
                                        Data0,
                            Continue = Table.FirstValue = "No markets available" or [Counter] < 10,
                            Counter = [Counter] + 1],
                       each [Table]),
    TableWithData = if List.IsEmpty(Loop) then Data0 else List.Last(Loop),                          
    #"Removed Columns" = Table.RemoveColumns(TableWithData,{"", "Column6", "Column7", "Current"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{Table.ColumnNames(Data0){0}, "SPIN"}})
in    #"Renamed Columns"

I recently explained List.Generate on StackOverflow.

 

[tallan]

Marcel,

Many thanks for the correction! After wiping off some egg-on-my-face, I am going forward a wiser contributor.

Best regards,

Tom

 

[MarcelBeug]

Yesterday I didn't get any hit for "No markets available"; today I do, so I could correct the code as it didn't work properly.

Below the corrected code with max. 50 iterations.

let
    Source = Function.InvokeAfter(() => Web.Page(Web.Contents("https://www.sportingindex.com/spread-betting/golf/ryder-cup/group_c.1ed2996f-9381-40e8-bef8-0f7ae460b2ec/ryder-cup-outright-markets")),
            #duration(0,0,0,0)),
    Data0 = Table.Buffer(Source{0}[Data]),
    Continue = Table.FirstValue(Data0) = "No markets available",
    Loop = 
        List.Generate(() => [Table = Data0, Continue = Table.FirstValue(Table) = "No markets available", Counter = 1],
                      each [Continue],
                      each [Table = Table.Buffer(let
                                        Source = Function.InvokeAfter(() => Web.Page(Web.Contents("https://www.sportingindex.com/spread-betting/golf/ryder-cup/group_c.1ed2996f-9381-40e8-bef8-0f7ae460b2ec/ryder-cup-outright-markets")),
                                            #duration(0,0,0,0.1)),
                                        Data0 = Source{0}[Data]
                                    in
                                        Data0),
                            Continue = Table.FirstValue(Table) = "No markets available" and [Counter] < 50,
                            Counter = [Counter] + 1],
                       each [Table]),
    TableWithData = if List.IsEmpty(Loop) then Data0 else List.Last(Loop),                          
    #"Removed Columns" = Table.RemoveColumns(TableWithData,{"", "Column6", "Column7", "Current"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{Table.ColumnNames(#"Removed Columns"){0}, "SPIN"}})
in    
    #"Renamed Columns"

 

[sauzee]

Yesterday I didn't get any hit for "No markets available"; today I do, so I could correct the code as it didn't work properly.

Below the corrected code with max. 50 iterations.

let
    Source = Function.InvokeAfter(() => Web.Page(Web.Contents("https://www.sportingindex.com/spread-betting/golf/ryder-cup/group_c.1ed2996f-9381-40e8-bef8-0f7ae460b2ec/ryder-cup-outright-markets")),
            #duration(0,0,0,0)),
    Data0 = Table.Buffer(Source{0}[Data]),
    Continue = Table.FirstValue(Data0) = "No markets available",
    Loop = 
        List.Generate(() => [Table = Data0, Continue = Table.FirstValue(Table) = "No markets available", Counter = 1],
                      each [Continue],
                      each [Table = Table.Buffer(let
                                        Source = Function.InvokeAfter(() => Web.Page(Web.Contents("https://www.sportingindex.com/spread-betting/golf/ryder-cup/group_c.1ed2996f-9381-40e8-bef8-0f7ae460b2ec/ryder-cup-outright-markets")),
                                            #duration(0,0,0,0.1)),
                                        Data0 = Source{0}[Data]
                                    in
                                        Data0),
                            Continue = Table.FirstValue(Table) = "No markets available" and [Counter] < 50,
                            Counter = [Counter] + 1],
                       each [Table]),
    TableWithData = if List.IsEmpty(Loop) then Data0 else List.Last(Loop),                          
    #"Removed Columns" = Table.RemoveColumns(TableWithData,{"", "Column6", "Column7", "Current"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{Table.ColumnNames(#"Removed Columns"){0}, "SPIN"}})
in    
    #"Renamed Columns"

Thanks Marcel. That looks great! Unfortunately I'm still getting the odd refresh with "no markets available". Is this perhaps because at the point of checking the first row, columns 5, 6 & 7 can be blank instead of no markets available? Or is it simply it's running through the 50 iterations and hasn't had one successful import?!

I'm testing this on a slow tethered mobile internet connection which probably has something to do with the import not being succesfull. However, even if I change the iterations to 1000, it still seems to refresh very quickly and still get "no markets available"

Thanks!

 

[sauzee]

Thanks Marcel. That looks great! Unfortunately I'm still getting the odd refresh with "no markets available". Is this perhaps because at the point of checking the first row, columns 5, 6 & 7 can be blank instead of no markets available? Or is it simply it's running through the 50 iterations and hasn't had one successful import?!

I'm testing this on a slow tethered mobile internet connection which probably has something to do with the import not being succesfull. However, even if I change the iterations to 1000, it still seems to refresh very quickly and still get "no markets available"

Thanks!

In fact, apologies. I take it back. Does seem to be running the 1000 iterations when it's changed to that. Just doesn't solve the base problem as I thought it would. I thought the "no markets available" was a random occurrence and just refreshing a few more times would solve this. However, that doesn't seem to be the case so I'll have to try and work out a different solution. Many thanks though!

 

[bramn]

Hi Everyone,

I have a similar challenge. Although it is more of a 'Retry and wait'-concept. Of all the topics, this seemd the most relevant.

I am trying to use power query to read data from a webservice. I sent a POST request using the webcontents function. As a result the webservice generates an excel report. My next step is then to open the excel report using a GET request. The issue is however that the webservice takes time to generate the report. And this time fluctuates quite a bit (from seconds to over 10 minutes). In addition, the GET request returns either a html message telling me that the report is still generating or an excel report ones it is done.

The solution that I am trying to implement uses the List.Generate() function and the Function.InvokeAfter().


let
FnTestExcel = (x as binary) => if try Excel.Workbook(x) otherwise null = null then 1 else 2,
GetTimeAsText = ()=> = DateTime.ToText(DateTime.LocalNow())


//irrelevant steps that result in #"test" which is the binary of the HTML-file or excel file from my request
#"test" = #"Invoked Custom Function1"{1}[BekijkDownloadLocatie],


#"result" = List.Generate(
()=>[A= 1,B=1,C=100],
each [A] = FnTestExcel(#"test") and < 14,
each [A=FnTestExcel(#"test"), B=[B]+2, C= 10 + Function.InvokeAfter(GetTimeAsText(), #duration(0,0,0,30))],
each [A=[A], B=, C=[C], Addition=[A]+])
in
#"result"


I have created the C to avoid using the Function.InvokeAfter() on my FnTestExcel since that does not return a function. However my GetTimeAsText function only generates a function the first time round.

Is it possible to use Function.InvokeAfter() in a loop or a similar construct that does not need to return a function? OR
Is it possible to use a function that returns itself in Function.InvokeAfter() (this is a bit of a risky strategy cause it could cause an endless loop if it is the only condition) OR
Is there another way around the issue?

Any help is much appreciated!