Forum Discussion
Refreshing dynamic sources in the Service
I am trying to web scrape data from a website, collect a bunch of attributes such as a URL then cycle through each URL to obtain further attributes. So basically, I get the data from the root URL initially then drill-through to more detail using another URL for each person. This is how my query looks;
let
Source = Web.BrowserContents("https://www.xxxxxxxxxx.com/en-gb/contacts/find-a-lawyer/?Name=&t=&Service=&Role=partner&Location=&Office=&Industry=&ShowAll=1"),
#"Extracted Table From Html" = Html.Table(Source, {{"Persons Name", ".profile-image-grad-bg + *"}, {"Job Title", "P:nth-child(2):nth-last-child(2) > :nth-last-child(1)"}, {"Office Location", ".tag_country"}, {"Profile URL", ".mix > A:nth-child(1):nth-last-child(1)", each [Attributes][href]?}}, [RowSelector=".mix"]),
#"Changed Type" = Table.TransformColumnTypes(#"Extracted Table From Html",{{"Persons Name", type text}, {"Job Title", type text}, {"Office Location", type text}, {"Profile URL", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Full Profile URL", each "https://www.xxxxxxxxx.com" & [Profile URL]),
#"Invoked Custom Function" = Table.AddColumn(#"Added Custom", "BioDetails", each BioDetails([Full Profile URL])),
#"Expanded BioDetails" = Table.ExpandTableColumn(#"Invoked Custom Function", "BioDetails", {"Colleague Name", "Email Address", "Primary Capabilities"}, {"Colleague Name", "Email Address", "Primary Capabilities"}),
#"Added Custom1" = Table.AddColumn(#"Expanded BioDetails", "Primary Capabilities New", each Text.Replace([Primary Capabilities], "#(lf)", "; ")),
#"Replaced Value" = Table.ReplaceValue(#"Added Custom1"," "," ",Replacer.ReplaceText,{"Primary Capabilities New"}),
#"Reordered Columns" = Table.ReorderColumns(#"Replaced Value",{"Colleague Name", "Persons Name", "Job Title", "Office Location", "Profile URL", "Full Profile URL", "Primary Capabilities", "Primary Capabilities New"}),
#"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"Persons Name"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Primary Capabilities", "Raw Primary Capabilities"}, {"Primary Capabilities New", "Primary Capabilities"}}),
#"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 1, 1, Int64.Type),
#"Reordered Columns1" = Table.ReorderColumns(#"Added Index",{"Index", "Colleague Name", "Job Title", "Office Location", "Profile URL", "Full Profile URL", "Raw Primary Capabilities", "Primary Capabilities"})
in
#"Reordered Columns1"
When I invoke the custom function it appends further data based on the URL that it passes into the function. In the desktop, the data works perfectly for me but it doesn't refresh in the Service. Would you happen to know how I can configure this query to refresh in the Service but obtain the same information?
Hi. When you use variables or parameters at URL, the engine can't find the correct source. You need to make sure that url is a single one that fits for all changes at variables. You can do that using relative path and query params for the Web.Contents function (let's see if that works instead browser contents). Check this post to understand how to use them: https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-in-power-query-and-power-bi-m-code/
I hope that helps,
3 Replies
Hi. When you use variables or parameters at URL, the engine can't find the correct source. You need to make sure that url is a single one that fits for all changes at variables. You can do that using relative path and query params for the Web.Contents function (let's see if that works instead browser contents). Check this post to understand how to use them: https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-in-power-query-and-power-bi-m-code/
I hope that helps,
Cmoore, As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by our super users for your issue worked? or let us know if you need any further assistance here?
Thanks,
Prashanth Are
MS Fabric community support
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query
Cmoore As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by our super users for your issue worked? or let us know if you need any further assistance here?
ibarrau, thanks for your promt response here
Thanks,
Prashanth Are
MS Fabric community support
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query
