Cant understand why this function based web query wont refresh

[masplin]

I have written 2 functions ot extract price data off the FT website.  They seem to work fine in the query editior, but when i refresh they just time out. 

Fn are

(Code as text) as table =>
let
    Source = Web.Page(Web.Contents("https://markets.ft.com/data/indices/tearsheet/summary?s="&Code&":STX")),
    Data0 = Source{0}[Data],
    Children = Data0{0}[Children],
    Children1 = Children{3}[Children],
    Children2 = Children1{11}[Children],
    Children3 = Children2{3}[Children],
    Children4 = Children3{1}[Children],
    Children5 = Children4{1}[Children],
    Children6 = Children5{0}[Children],
    Children7 = Children6{0}[Children],
    Children8 = Children7{1}[Children],
    Children9 = Children8{0}[Children],
    Children10 = Children9{0}[Children],
    Children11 = Children10{1}[Children],
    #"Removed Other Columns" = Table.SelectColumns(Children11,{"Text"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns",{{"Text", "Last"}})
in
    #"Renamed Columns"

and

I then have this table created

Where Source is 

IndexCode

Technology	SX8P
Healthcare	SXDP
Basic	SXPP
Oil	SXEP

I get this result in the query  editor which is all correct

but if I click refresh it doesnt ever fin sih churning.  The invoke functions seem to work fine individually 

i'm mystified why it doenst work as a full thing. Its only make 8 calls to the website. 

Appreciate any advice

Mike

[spinfuzer · Accepted solution]

Maybe try to Table.Buffer( ) around the first or 2nd step.

[masplin]

so do you mean add it like this as last step in each functions?

let
    Source = Sql.Database("SomeSQLServer", "MyDb"),
    MyTable = Source{[Item="MyTable"]}[Data],
    BufferMyTable = Table.Buffer(dbo_MyTable)
in
    BufferMyTable

Thanks

[AlexisOlson]

I got better performance when I turned off Background Data.

You can try playing with parallel loading too.

Resources I found useful on this topic:

https://blog.crossjoin.co.uk/2019/10/13/why-does-power-bi-query-my-data-source-more-than-once/

https://www.thebiccountant.com/speedperformance-aspects/

[masplin]

2nd function fell off

(Code as text) as table =>
let
    Source = Web.Page(Web.Contents("https://markets.ft.com/data/indices/tearsheet/summary?s="&Code&":STX")),
    Data0 = Source{0}[Data],
    Children = Data0{0}[Children],
    Children1 = Children{3}[Children],
    Children2 = Children1{11}[Children],
    Children3 = Children2{3}[Children],
    Children4 = Children3{1}[Children],
    Children5 = Children4{1}[Children],
    Children6 = Children5{0}[Children],
    Children7 = Children6{0}[Children],
    Children8 = Children7{1}[Children],
    Children9 = Children8{0}[Children],
    Children10 = Children9{1}[Children],
    Children11 = Children10{1}[Children],
    Children12 = Children11{0}[Children],
    #"Filtered Rows" = Table.SelectRows(Children12, each ([Kind] = "Text")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Text"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Other Columns", "Text", Splitter.SplitTextByEachDelimiter({"/"}, QuoteStyle.Csv, false), {"Text.1", "Text.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Text.1", type number}, {"Text.2", Percentage.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Text.1", "Change"}, {"Text.2", "Change %"}})


in
   #"Renamed Columns"