Load the query in Excel get stuck at 1009 rows and it's very slow

[nicksportslabs]

First of all as a rookie I managed to make a big part of the query somehow working with all the information I could find. But I ran into a wall at the moment. The problem is that it will load the full query in the power editor based on a table which contains concatenate URLs, but when I load it to excel it is very very slow(I unchecked preview data loading btw) and it will not complete the query even after more than one hour loading and it got stuck at 1009 rows. 

I wish I could upload the file, but everything I tried this website didn't let me.

Funtions to get the data, inclusive urls from the specific website

(page as number) as table =>
let
    Bron = Json.Document(Web.Contents("https://sportvloeren.sport.nl/umbraco/api/sportvloerenlijstapi/getsportvloeren?page=" & Number.ToText(page) & "")),
    #"Geconverteerd naar tabel" = Table.FromList(Bron, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Column1 uitgevouwen" = Table.ExpandRecordColumn(#"Geconverteerd naar tabel", "Column1", {"ID", "Naam", "Type", "Typetoevoeging", "Niveau", "Toplaag", "InvulLaag", "Fundering", "Onderbouw", "Aannemer", "Status", "Url"}, {"Column1.ID", "Column1.Naam", "Column1.Type", "Column1.Typetoevoeging", "Column1.Niveau", "Column1.Toplaag", "Column1.InvulLaag", "Column1.Fundering", "Column1.Onderbouw", "Column1.Aannemer", "Column1.Status", "Column1.Url"}),
    #"Kolommen verwijderd" = Table.RemoveColumns(#"Column1 uitgevouwen",{"Column1.ID", "Column1.Type", "Column1.Typetoevoeging", "Column1.Niveau", "Column1.Aannemer", "Column1.Status", "Column1.InvulLaag"}),
    #"Kolom splitsen op scheidingsteken" = Table.SplitColumn(#"Kolommen verwijderd", "Column1.Toplaag", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Column1.Toplaag.1", "Column1.Toplaag.2"}),
    #"Type gewijzigd" = Table.TransformColumnTypes(#"Kolom splitsen op scheidingsteken",{{"Column1.Toplaag.1", type text}, {"Column1.Toplaag.2", type text}}),
    #"Kolom splitsen op scheidingsteken1" = Table.SplitColumn(#"Type gewijzigd", "Column1.Fundering", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Column1.Fundering.1", "Column1.Fundering.2", "Column1.Fundering.3", "Column1.Fundering.4", "Column1.Fundering.5"}),
    #"Type gewijzigd1" = Table.TransformColumnTypes(#"Kolom splitsen op scheidingsteken1",{{"Column1.Fundering.1", type text}, {"Column1.Fundering.2", type text}, {"Column1.Fundering.3", type text}, {"Column1.Fundering.4", type text}, {"Column1.Fundering.5", type text}}),
    #"Kolom splitsen op scheidingsteken2" = Table.SplitColumn(#"Type gewijzigd1", "Column1.Onderbouw", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Column1.Onderbouw.1", "Column1.Onderbouw.2"}),
    #"Type gewijzigd2" = Table.TransformColumnTypes(#"Kolom splitsen op scheidingsteken2",{{"Column1.Onderbouw.1", type text}, {"Column1.Onderbouw.2", type text}})
in
    #"Type gewijzigd2"

 The table to load in Excel:

let
    Bron = List.Generate( ()=> [Result= try GetData(0) otherwise null, Page = 0], each [Result] <> null, each [Result = try GetData([Page]+1) otherwise null, Page =[Page]+1], each [Result]),
    #"Geconverteerd naar tabel" = Table.FromList(Bron, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Column1 uitgevouwen" = Table.ExpandTableColumn(#"Geconverteerd naar tabel", "Column1", {"Column1.Naam", "Column1.Toplaag.1", "Column1.Toplaag.2", "Column1.Fundering.1", "Column1.Fundering.2", "Column1.Fundering.3", "Column1.Fundering.4", "Column1.Fundering.5", "Column1.Onderbouw.1", "Column1.Onderbouw.2", "Column1.Url"}, {"Column1.Column1.Naam", "Column1.Column1.Toplaag.1", "Column1.Column1.Toplaag.2", "Column1.Column1.Fundering.1", "Column1.Column1.Fundering.2", "Column1.Column1.Fundering.3", "Column1.Column1.Fundering.4", "Column1.Column1.Fundering.5", "Column1.Column1.Onderbouw.1", "Column1.Column1.Onderbouw.2", "Column1.Column1.Url"})
in
    #"Column1 uitgevouwen"

In the excel sheet there is a table which concatenate a working url.

Function to read the table and the URLs to retrieve the data:

(URL) as table => 
let
    Bron = Web.Page(Web.Contents(URL)),
    Data0 = Bron{0}[Data],
    Children = Data0{0}[Children],
    Children1 = Children{1}[Children],
    Children2 = Children1{8}[Children],
    Children3 = Children2{12}[Children],
    Children4 = Children3{2}[Children],
    Children5 = Children4{0}[Children],
    #"Geconverteerd naar tabel" = Table.FromList(Bron, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Kolommen verwijderd" = Table.RemoveColumns(Children5,{"Kind", "Name", "Children"}),
    #"Namen van kolommen gewijzigd" = Table.RenameColumns(#"Kolommen verwijderd",{{"Text", "Toevoeging.1"}})
in
    #"Namen van kolommen gewijzigd"

Table to load in Excel which gives the above stated problems

let
    Bron = Excel.CurrentWorkbook(){[Name="Tabel4"]}[Content],
    #"Type gewijzigd" = Table.TransformColumnTypes(Bron,{{"URL", type text}}),
    #"Aangepaste kolom toegevoegd" = Table.AddColumn(#"Type gewijzigd", "Toevoeging1", each fxToevoeging1([URL])),
    #"Fouten vervangen" = Table.ReplaceErrorValues(#"Aangepaste kolom toegevoegd", {{"Toevoeging1", null}}),
    #"Toevoeging1 uitgevouwen" = Table.ExpandTableColumn(#"Fouten vervangen", "Toevoeging1", {"Toevoeging.1"}, {"Toevoeging1.Toevoeging.1"})
in
    #"Toevoeging1 uitgevouwen"

I hope someone is able to help me.

Kind regards,

Nick

[v-lid-msft · Accepted solution]

Hi @nicksportslabs ,

First of all, if the api and the url in your origin post contain any confidential information, we suggest you to delete it.

Based on my test, your query stuck at 1405 rows after approximate 20 minutes, we think it is because that using Web.Page() & Web.Contents() function needs to download entire html from the url, it means post many request to the server, then the server may decide to block the request after too many requests, we suggest you to use another RESTFul API to get the content from those URLs instead of use Web.Page Function.

Best regards,

[nicksportslabs]

Thanks for your reply and for clearifying the issue.

We work together with the company which we want the data from. Last friday I reached out to them for asking a usefull API which they acknowledge before. So my hope is on that outcome.

I'll have to duck deeper in the information of a RESTful API and API alone because that's kind of acrcadabra to me at the moment.