Forum Discussion
An unknown error occurred when navigating to the web page.
Hello! So I am querying this website:
Each page returns 25 results. There are instances when entries overflow to another page, so I created another query for pages 2 and 3:
I appended these three tables and processed the data to get what I need in the right format (split, clean, trim, etc). Everything loads fine in PowerQuery, but when I apply the query changes to pbi desktop, the appended table gets stuck on 'Evaluating', until it stops and gives me the error: An unknown error occurred when navigating to the web page.
I wonder if it's an issue with my queries, or with the website itself. I've been trying to wrap my head around this one since yesterday. Would appreciate any help :'(
3 Replies
Can you post the code of your queries from Advanced Editor? Can try to replicate.
Thanks for the reply!
Query 1: ACT_Live_01
let
Source = Web.Page(Web.Contents("https://www.tenders.act.gov.au/tender/search?keywords=&tenderCode=&tenderTitle=&tenderState=OPEN&tenderType=&prequalificationCategory=&procurementCategory=&issuingBusiness.id=&issuingBusiness.name=&awardedSupplier.id=&awardedSupplier.name=&openThisWeek=false&openingDateFrom=&openingDateTo=&ageRestriction=&closeThisWeek=false&closingDateFrom=&closingDateTo=&awardedThisWeek=false&awardedDateFrom=&awardedDateTo=&orderBy=&groupBy=NONE&page=1&searchTitle=", [Timeout=#duration(0, 0, 30, 0)])),
#"Expanded Data" = Table.ExpandTableColumn(Source, "Data", {"RFx Number", "Details", "Date", "", "Kind", "Name", "Children", "Text"}, {"Data.RFx Number", "Data.Details", "Data.Date", "Data.", "Data.Kind", "Data.Name", "Data.Children", "Data.Text"})
in
#"Expanded Data"Query 2: ACT_Live_02
let
Source = Web.Page(Web.Contents("https://www.tenders.act.gov.au/tender/search?keywords=&tenderCode=&tenderTitle=&tenderState=OPEN&tenderType=&prequalificationCategory=&procurementCategory=&issuingBusiness.id=&issuingBusiness.name=&awardedSupplier.id=&awardedSupplier.name=&openThisWeek=false&openingDateFrom=&openingDateTo=&ageRestriction=&closeThisWeek=false&closingDateFrom=&closingDateTo=&awardedThisWeek=false&awardedDateFrom=&awardedDateTo=&orderBy=&groupBy=NONE&page=2&searchTitle=",[Timeout=#duration(0, 0, 30, 0)])),
#"Expanded Data" = Table.ExpandTableColumn(Source, "Data", {"RFx Number", "Details", "Date", "", "Kind", "Name", "Children", "Text"}, {"Data.RFx Number", "Data.Details", "Data.Date", "Data.", "Data.Kind", "Data.Name", "Data.Children", "Data.Text"})
in
#"Expanded Data"Query 3: ACT_Live_03
let
Source = Web.Page(Web.Contents("https://www.tenders.act.gov.au/tender/search?keywords=&tenderCode=&tenderTitle=&tenderState=OPEN&tenderType=&prequalificationCategory=&procurementCategory=&issuingBusiness.id=&issuingBusiness.name=&awardedSupplier.id=&awardedSupplier.name=&openThisWeek=false&openingDateFrom=&openingDateTo=&ageRestriction=&closeThisWeek=false&closingDateFrom=&closingDateTo=&awardedThisWeek=false&awardedDateFrom=&awardedDateTo=&orderBy=&groupBy=NONE&page=3&searchTitle=",[Timeout=#duration(0, 0, 30, 0)])),
#"Expanded Data" = Table.ExpandTableColumn(Source, "Data", {"RFx Number", "Details", "Date", "", "Kind", "Name", "Children", "Text"}, {"Data.RFx Number", "Data.Details", "Data.Date", "Data.", "Data.Kind", "Data.Name", "Data.Children", "Data.Text"})
in
#"Expanded Data"Query 4: ACT_FinalTable
let
Source = Table.Combine({ACT_Live_01, ACT_Live_02, ACT_Live_03}),
#"Filtered Rows" = Table.SelectRows(Source, each ([Data.RFx Number] <> null)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Caption", "Source", "ClassName", "Id"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "Data.RFx Number", Splitter.SplitTextByEachDelimiter({"RFx Number"}, QuoteStyle.Csv, false), {"Data.RFx Number.1", "Data.RFx Number.2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Data.RFx Number.1", type text}, {"Data.RFx Number.2", type text}}),
#"Removed Columns1" = Table.RemoveColumns(#"Changed Type",{"Data.RFx Number.1"}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Removed Columns1", "Data.RFx Number.2", Splitter.SplitTextByEachDelimiter({"Open"}, QuoteStyle.Csv, false), {"Data.RFx Number.2.1", "Data.RFx Number.2.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Data.RFx Number.2.1", type text}, {"Data.RFx Number.2.2", type text}}),
#"Cleaned Text" = Table.TransformColumns(#"Changed Type1",{{"Data.RFx Number.2.1", Text.Clean, type text}}),
#"Trimmed Text" = Table.TransformColumns(#"Cleaned Text",{{"Data.RFx Number.2.1", Text.Trim, type text}}),
#"Cleaned Text1" = Table.TransformColumns(#"Trimmed Text",{{"Data.RFx Number.2.2", Text.Clean, type text}}),
#"Trimmed Text1" = Table.TransformColumns(#"Cleaned Text1",{{"Data.RFx Number.2.2", Text.Trim, type text}}),
#"Split Column by Delimiter2" = Table.SplitColumn(#"Trimmed Text1", "Data.Details", Splitter.SplitTextByEachDelimiter({"Details"}, QuoteStyle.Csv, false), {"Data.Details.1", "Data.Details.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Data.Details.1", type text}, {"Data.Details.2", type text}}),
#"Removed Columns2" = Table.RemoveColumns(#"Changed Type2",{"Data.Details.1"}),
#"Split Column by Delimiter3" = Table.SplitColumn(#"Removed Columns2", "Data.Details.2", Splitter.SplitTextByEachDelimiter({"Issued by"}, QuoteStyle.Csv, false), {"Data.Details.2.1", "Data.Details.2.2"}),
#"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter3",{{"Data.Details.2.1", type text}, {"Data.Details.2.2", type text}}),
#"Split Column by Delimiter4" = Table.SplitColumn(#"Changed Type3", "Data.Details.2.1", Splitter.SplitTextByEachDelimiter({"Prequalification Category:"}, QuoteStyle.Csv, false), {"Data.Details.2.1.1", "Data.Details.2.1.2"}),
#"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Delimiter4",{{"Data.Details.2.1.1", type text}, {"Data.Details.2.1.2", type text}}),
#"Cleaned Text2" = Table.TransformColumns(#"Changed Type4",{{"Data.Details.2.1.1", Text.Clean, type text}}),
#"Trimmed Text2" = Table.TransformColumns(#"Cleaned Text2",{{"Data.Details.2.1.1", Text.Trim, type text}}),
#"Cleaned Text3" = Table.TransformColumns(#"Trimmed Text2",{{"Data.Details.2.1.2", Text.Clean, type text}}),
#"Trimmed Text3" = Table.TransformColumns(#"Cleaned Text3",{{"Data.Details.2.1.2", Text.Trim, type text}}),
#"Split Column by Delimiter5" = Table.SplitColumn(#"Trimmed Text3", "Data.Details.2.2", Splitter.SplitTextByEachDelimiter({"#(lf)"}, QuoteStyle.Csv, false), {"Data.Details.2.2.1", "Data.Details.2.2.2"}),
#"Changed Type5" = Table.TransformColumnTypes(#"Split Column by Delimiter5",{{"Data.Details.2.2.1", type text}, {"Data.Details.2.2.2", type text}}),
#"Cleaned Text4" = Table.TransformColumns(#"Changed Type5",{{"Data.Details.2.2.1", Text.Clean, type text}}),
#"Trimmed Text4" = Table.TransformColumns(#"Cleaned Text4",{{"Data.Details.2.2.1", Text.Trim, type text}}),
#"Split Column by Delimiter6" = Table.SplitColumn(#"Trimmed Text4", "Data.Date", Splitter.SplitTextByEachDelimiter({"Datereleased"}, QuoteStyle.Csv, false), {"Data.Date.1", "Data.Date.2"}),
#"Changed Type6" = Table.TransformColumnTypes(#"Split Column by Delimiter6",{{"Data.Date.1", type text}, {"Data.Date.2", type text}}),
#"Removed Columns4" = Table.RemoveColumns(#"Changed Type6",{"Data.Date.1"}),
#"Split Column by Delimiter7" = Table.SplitColumn(#"Removed Columns4", "Data.Date.2", Splitter.SplitTextByEachDelimiter({"closing"}, QuoteStyle.Csv, false), {"Data.Date.2.1", "Data.Date.2.2"}),
#"Changed Type7" = Table.TransformColumnTypes(#"Split Column by Delimiter7",{{"Data.Date.2.1", type datetime}, {"Data.Date.2.2", type datetime}}),
#"Removed Columns5" = Table.RemoveColumns(#"Changed Type7",{"Data.", "Data.Kind", "Data.Name", "Data.Children", "Data.Text"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns5",{{"Data.RFx Number.2.1", "ID"}, {"Data.RFx Number.2.2", "Request Type"}, {"Data.Details.2.1.1", "Details"}, {"Data.Details.2.1.2", "Prequalification Category"}, {"Data.Details.2.2.1", "Issued by"}, {"Data.Date.2.1", "Published Date"}, {"Data.Date.2.2", "Closing Date"}, {"Data.Details.2.2.2", "UNSPSC"}}),
#"Cleaned Text5" = Table.TransformColumns(#"Renamed Columns",{{"UNSPSC", Text.Clean, type text}}),
#"Trimmed Text5" = Table.TransformColumns(#"Cleaned Text5",{{"UNSPSC", Text.Trim, type text}}),
#"Added Custom" = Table.AddColumn(#"Trimmed Text5", "New Today?", each if Date.AddDays(DateTime.Date([Published Date]),1) = DateTime.Date(DateTimeZone.SwitchZone(DateTimeZone.UtcNow(),8)) then "New Today" else "Older"),
#"Changed Type8" = Table.TransformColumnTypes(#"Added Custom",{{"New Today?", type text}})
in
#"Changed Type8"I have the exact same issue just with more URLs, did you have the time to look at his query?
