Getting web contents based on dynamic information

Question

Hi All,&nbsp;Struggling a bit with this one. So, I have an online spreadsheet which is now within BI as a table (Score), with 3 columns:ID, Title, URL&nbsp;The id is an id of a custom api, title is exactly that and the URL is the custom link to the specific api link (which basically returns a figure, such as 55.&nbsp;Is there any way I can use this information to pull web contents dynamically? I've seen you can add parameters but I can't seem to specify anything dynamically here.&nbsp;If i manually set this up with web contents and use the URL, it works fine and returns the number, but each month there will be new ones I wish to add.&nbsp;Thanks

fowmy · Answer

thyjones&nbsp;Can you share the M Code that connects to the file?________________________If my answer was helpful, please consider Accept it as the solution to help the other members find itClick on the Thumbs-Up icon if you like this reply 🙂YouTube&nbsp; LinkedIn

thyjones · Answer

Hi Fowmy, yes its:&nbsp;letSource = Excel.Workbook(Web.Contents("https://customlink/Documents/file.xlsx"), null, true),Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],#"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}, {"Column2", type text}}),#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "ID"}, {"Column2", "Title"}}),#"Promoted Headers" = Table.PromoteHeaders(#"Renamed Columns", [PromoteAllScalars=true]),#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID", Int64.Type}, {"Title of NPS Visual", type text}, {"URL", type text}})in#"Changed Type1"

fowmy · Answer

thyjones&nbsp;Which part of the query do you need to make dynamic?Provide an example, please?________________________If my answer was helpful, please consider Accept it as the solution to help the other members find itClick on the Thumbs-Up icon if you like this reply 🙂YouTube&nbsp; LinkedIn

thyjones · Answer

Hi Fowmy,&nbsp;Sorry, I've not been that clear.&nbsp;The information already provided gets the URL's.&nbsp;Then I currently have a web contents query which accesses a URL, like:&nbsp;= Web.BrowserContents("https://app.whatever.com/api.php?apiKey=asfsafsaf436436&amp;getMethod=getValue" &amp; "&amp;fromDate=2020-09-01" &amp; "&amp;toDate=2020-09-30")&nbsp;&nbsp;letSource = Web.BrowserContents("https://app.whatever.com/api.php?apiKey=asfsafsaf436436&amp;getMethod=getValue" &amp; "&amp;fromDate=2020-09-01" &amp; "&amp;toDate=2020-09-30"),#"Converted to Table" = #table(1, {{Source}}),#"Replaced Value" = Table.ReplaceValue(#"Converted to Table","&lt;html&gt;&lt;head&gt;&lt;/head&gt;&lt;body&gt;","",Replacer.ReplaceText,{"Column1"}),#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","&lt;/body&gt;&lt;/html&gt;","",Replacer.ReplaceText,{"Column1"}),#"Renamed Columns" = Table.RenameColumns(#"Replaced Value1",{{"Column1", "NPS"}}),#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"NPS", Int64.Type}})in#"Changed Type"&nbsp;&nbsp;So that is relatively set, I need the Web.BrowserContents to request the URL's in the original query.I hope that explains it a little better?Thanks

fowmy · Answer

thyjones&nbsp;You can simply replace the Source value to the query which you use to connect, let's say the name is Query, then you can assign&nbsp;Source = Querylet
Source = Query,
#"Converted to Table" = #table(1, {{Source}}),
#"Replaced Value" = Table.ReplaceValue(#"Converted to Table","&lt;html&gt;&lt;head&gt;&lt;/head&gt;&lt;body&gt;","",Replacer.ReplaceText,{"Column1"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","&lt;/body&gt;&lt;/html&gt;","",Replacer.ReplaceText,{"Column1"}),
#"Renamed Columns" = Table.RenameColumns(#"Replaced Value1",{{"Column1", "NPS"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"NPS", Int64.Type}})
in
#"Changed Type"&nbsp;________________________If my answer was helpful, please consider Accept it as the solution to help the other members find itClick on the Thumbs-Up icon if you like this reply 🙂YouTube&nbsp; LinkedIn&nbsp;

Forum Discussion

Getting web contents based on dynamic information

6 Replies

Related content

[DataSource.Error] Web.Contents failed to get contents

Error to get the information using the web version

The content is blocked.

Rest API Informations

Missing information