Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to Solution.
Maybe try to Table.Buffer( ) around the first or 2nd step.
Maybe try to Table.Buffer( ) around the first or 2nd step.
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
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/
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"
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.