Re: Getting web contents based on dynamic informat...

thyjones · ‎09-23-2020

Hi All,

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

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.

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.

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.

Thanks

v-xicai · ‎09-25-2020

Hi @thyjones ,

Do the suggestions from engineers make sense? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best regards

Amy

Fowmy · ‎09-23-2020

@thyjones

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 it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up

⭕ Subscribe and learn Power BI from these videos

⚪ Website ⚪ LinkedIn ⚪ PBI User Group

thyjones · ‎09-23-2020

Hi Fowmy, yes its:

let
Source = 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 · ‎09-23-2020

@thyjones

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 it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up

⭕ Subscribe and learn Power BI from these videos

⚪ Website ⚪ LinkedIn ⚪ PBI User Group

thyjones · ‎09-23-2020

Hi Fowmy,

Sorry, I've not been that clear.

The information already provided gets the URL's.

Then I currently have a web contents query which accesses a URL, like:

= Web.BrowserContents("https://app.whatever.com/api.php?apiKey=asfsafsaf436436&getMethod=getValue" & "&fromDate=2020-09-01" & "&toDate=2020-09-30")

let
Source = Web.BrowserContents("https://app.whatever.com/api.php?apiKey=asfsafsaf436436&getMethod=getValue" & "&fromDate=2020-09-01" & "&toDate=2020-09-30"),
#"Converted to Table" = #table(1, {{Source}}),
#"Replaced Value" = Table.ReplaceValue(#"Converted to Table","<html><head></head><body>","",Replacer.ReplaceText,{"Column1"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","</body></html>","",Replacer.ReplaceText,{"Column1"}),
#"Renamed Columns" = Table.RenameColumns(#"Replaced Value1",{{"Column1", "NPS"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"NPS", Int64.Type}})
in
#"Changed Type"

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 · ‎09-23-2020

@thyjones

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
Source = Query

let
Source = Query,
#"Converted to Table" = #table(1, {{Source}}),
#"Replaced Value" = Table.ReplaceValue(#"Converted to Table","<html><head></head><body>","",Replacer.ReplaceText,{"Column1"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","</body></html>","",Replacer.ReplaceText,{"Column1"}),
#"Renamed Columns" = Table.RenameColumns(#"Replaced Value1",{{"Column1", "NPS"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"NPS", Int64.Type}})
in
#"Changed Type"

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up

⭕ Subscribe and learn Power BI from these videos

⚪ Website ⚪ LinkedIn ⚪ PBI User Group