Hello!
I was hoping someone could give me a hand with some power query issues.
I have a qery below that scrapes data from a website including a URL. I then have a custom function to go to each URL it scrapes and scrape more data from that page.
Unfortuantely I am getting the cannot referesh data due to dynamic data source error in Power Bi Service. As far as I can see I have followed the nstructions from Chris Webb's Blog and Data Inspirations Blog but I am still having issues.
Does anyone have any other ideas?
(Apologies for the gross power query formating, I gave up on cleanliness when I started trying to get the custom function to work)
https://blog.datainspirations.com/2018/02/17/dynamic-web-contents-and-power-bi-refresh-errors/
https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-i...
let
BaseUrl = "https://www.tenders.act.gov.au",
RelativePath = "/tender/search?keywords=&tenderCode=&tenderTitle=&tenderState=ADVANCE_NOTICE&tenderType=&prequalificationCategory=&procurementCategory=&categories%5B0%5D.id=104789&categories%5B0%5D.percentage=100&issuingBusinessId=&awardedSupplier.id=&awardedSupplier.name=&openThisWeek=false&openingDateFrom=&openingDateTo=&ageRestriction=&closeThisWeek=false&closingDateFrom=&closingDateTo=&awardedThisWeek=false&awardedDateFrom=&awardedDateTo=&orderBy=&groupBy=NONE&page=&searchTitle=",
Source = Web.Contents(BaseUrl, [RelativePath=RelativePath]),
#"Extracted Table From Html" = Html.Table(Source, {{"Tender ID", "B:nth-child(1)"}, {"Title", ".strong"}, {"Agency", "DIV:nth-child(2) .line-item-detail"}, {"Link", ".strong", each [Attributes][href]?}, {"Published Date", ".opening_date"}, {"Estimated Approach", ".closing_date"}}, [RowSelector="TBODY TR"]),
#"Changed Type" = Table.TransformColumnTypes(#"Extracted Table From Html",{{"Tender ID", type text}, {"Title", type text}, {"Agency", type text}, {"Link", type text}, {"Published Date", type datetime}}),
#"Added Custom1" = Table.AddColumn(#"Changed Type", "Source", each "ACT Tenders"),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "State", each "Australian Capital Territory"),
#"Extracted Text After Delimiter" = Table.TransformColumns(#"Added Custom2", {{"Agency", each Text.AfterDelimiter(_, "Issued by: "), type text}}),
#"Added Custom3" = Table.AddColumn(#"Extracted Text After Delimiter", "Description", each ""),
#"Removed Columns2" = Table.RemoveColumns(#"Added Custom3",{"State"}),
#"Added Custom4" = Table.AddColumn(#"Removed Columns2", "guid", each ""),
#"Added Custom5" = Table.AddColumn(#"Added Custom4", "Relevant Content", each ""),
#"Added Custom6" = Table.AddColumn(#"Added Custom5", "Type", each ""),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom6",{{"Published Date", type date}}),
// Define a custom function to fetch data for each link using Power Query 2
GetLinkData = (link as text) => let
Source2 = Web.Contents("https://www.tenders.act.gov.au", [RelativePath = "/tender/view?", Query =[id = link]]),
#"Extracted Data" = Table.SelectColumns(Html.Table(Source2, {{"Column4", "[id*=""tenderDescription""] > :nth-child(2)"}}), {"Column4"})
in
#"Extracted Data",
// Add a custom column to fetch data for each link
#"Fetched Data" = Table.AddColumn(#"Changed Type1", "FetchedData", each GetLinkData([Link])),
#"Expanded FetchedData" = Table.ExpandTableColumn(#"Fetched Data", "FetchedData", {"Column4"}, {"FetchedData.Column4"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded FetchedData",{"Description"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"FetchedData.Column4", "Description"}}),
#"Cleaned Text" = Table.TransformColumns(#"Renamed Columns",{{"Description", Text.Clean, type text}}),
#"Renamed Columns1" = Table.RenameColumns(#"Cleaned Text",{{"Estimated Approach", "Estimated ATM Date"}}),
#"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns1",{"guid", "Relevant Content", "Type"}),
#"Extracted Text After Delimiter1" = Table.TransformColumns(#"Removed Columns1", {{"Estimated ATM Date", each Text.AfterDelimiter(_, ", "), type text}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Extracted Text After Delimiter1",{{"Estimated ATM Date", type datetime}}),
#"Changed Type3" = Table.TransformColumnTypes(#"Changed Type2",{{"Estimated ATM Date", type date}}),
#"Renamed Columns2" = Table.RenameColumns(#"Changed Type3",{{"Link", "ATM Link"}, {"Published Date", "Last Updated / Published Date"}})
in
#"Renamed Columns2"
