Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I am working on a dashboard that I would like to publish. I would also like to set a scheduled refresh for it. All of my data is currently sourced from the Web. And apparently it is not possible to set a scheduled refresh if I use the function Web.BrowserContents in the queries.
I have been trying to work a way around it with the function Web.Contents but all I get is an empty table.
Here is the original query, the one that gives the expected output :
let Source = Web.BrowserContents("https://ir.tevapharm.com" & "/news-and-events" & "/press-releases" & "/default.aspx"), #"Extracted Table From Html" = Html.Table( Source, { {"URL","a[href^=""/news-""]", each [Attributes][href]}, {"Headline", ".module-news .module_headline"}, {"Date", ".module-news .module_date-time"} }, [RowSelector=".module-news .module_item-wrap"] ), #"Replaced Value" = Table.ReplaceValue(#"Extracted Table From Html","/news-","https://ir.tevapharm.com/news-",Replacer.ReplaceText,{"URL"}), #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"URL", type text}, {"Headline", type text}, {"Date", type date}}) in #"Changed Type"
Here is the query with the Web function replaced, it gives an empty table:
let Source = Web.Contents("https://ir.tevapharm.com" & "/news-and-events" & "/press-releases" & "/default.aspx"), #"Extracted Table From Html" = Html.Table( Source, { {"URL","a[href^=""/news-""]", each [Attributes][href]}, {"Headline", ".module-news .module_headline"}, {"Date", ".module-news .module_date-time"} }, [RowSelector=".module-news .module_item-wrap"] ), #"Replaced Value" = Table.ReplaceValue(#"Extracted Table From Html","/news-","https://ir.tevapharm.com/news-",Replacer.ReplaceText,{"URL"}), #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"URL", type text}, {"Headline", type text}, {"Date", type date}}) in #"Changed Type"
Then I tried changing the format of the source before extracting the HTML contents with thies query, but I also get an empty table:
let Source = Web.Contents("https://ir.tevapharm.com" & "/news-and-events" & "/press-releases" & "/default.aspx"), #"Binary to text"=Text.FromBinary(Source), #"Extracted Table From Html" = Html.Table( #"Binary to text", { {"URL","a[href^=""/news-""]", each [Attributes][href]}, {"Headline", ".module-news .module_headline"}, {"Date", ".module-news .module_date-time"} }, [RowSelector=".module-news .module_item-wrap"] ), #"Replaced Value" = Table.ReplaceValue(#"Extracted Table From Html","/news-","https://ir.tevapharm.com/news-",Replacer.ReplaceText,{"URL"}), #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"URL", type text}, {"Headline", type text}, {"Date", type date}}) in #"Changed Type"
Has someone already had a similar problem ? Would love to know how you worked your way around it, thanks!
Solved! Go to Solution.
Hi @v-jingzhang ,
Thank you for taking a look at my issue.
I understand that there is a way around this problem by using a gateway but I work in an environment that does not provide me with one nor allows me to install one.
Please note that I reposted this issue because the first one was marked as spam. The spam categorization has been removed and @KNP has provided a solution that does not involve a gateway. The solution can be found here: Solved: Replace Web.BrowserContents with Web.Contents - Microsoft Power BI Community
Thanks,
Myriam
Hi @v-jingzhang ,
Thank you for taking a look at my issue.
I understand that there is a way around this problem by using a gateway but I work in an environment that does not provide me with one nor allows me to install one.
Please note that I reposted this issue because the first one was marked as spam. The spam categorization has been removed and @KNP has provided a solution that does not involve a gateway. The solution can be found here: Solved: Replace Web.BrowserContents with Web.Contents - Microsoft Power BI Community
Thanks,
Myriam
Wow, it's great! I will look at that solution to learn something I never dealt with. Thank you! 😁
Cheers,
Jing
Hi @myriamouan
It seems the behavior in Power BI Service has changed. I create a table with below code and publish it to the Service. When I refresh the dataset manually and set up a scheduled refresh for it, both succeed. I select Anonymous for Authentication method and Organizational for Privay level. You may have a try.
let
Source = Web.BrowserContents("https://ir.tevapharm.com/news-and-events/press-releases/default.aspx"),
#"Extracted Table From Html" =
Html.Table(
Source,
{
{"URL","a[href^=""/news-""]", each [Attributes][href]},
{"Headline", ".module-news .module_headline"},
{"Date", ".module-news .module_date-time"}
},
[RowSelector=".module-news .module_item-wrap"]
),
#"Replaced Value" = Table.ReplaceValue(#"Extracted Table From Html","/news-","https://ir.tevapharm.com/news-",Replacer.ReplaceText,{"URL"}),
#"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"URL", type text}, {"Headline", type text}, {"Date", type date}})
in #"Changed Type"
If it still fails, a common workaround is to use an on-premises data gateway. See Troubleshooting the Power Query Web connector | Microsoft Docs
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.