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 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
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
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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"
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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.