Forum Discussion

masplin's avatar
masplin
Icon for Impactful Individual rankImpactful Individual
2 years ago
Solved

Cant understand why this function based web query wont refresh

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

TechnologySX8P
HealthcareSXDP
BasicSXPP
OilSXEP

 

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

Need Help

4 Replies

  • spinfuzer's avatar
    spinfuzer
    Icon for Solution Sage rankSolution Sage
    2 years ago

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

    • masplin's avatar
      masplin
      Icon for Impactful Individual rankImpactful Individual
      2 years ago

      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

  • masplin's avatar
    masplin
    Icon for Impactful Individual rankImpactful Individual
    2 years ago

    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"