Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
BoaRoland
Regular Visitor

Need help with Power Query web/URL

I have the below Power Query which results in a table with financial data from Yahoo Finance for 3 specific securities (Microsoft, Apple, and Disney in this case). I want to build a solution which looks at another worksheet which has a list of security symbols; the solution would then change the below query to match the security symbols in that worksheet. To clarify, neither the end user not I as the developer should have to "manually" modify the query. 

I have not found a way to "code this in VBA". Can you point me in the right direction or point out books, blogs, Youtubes, etc.

Thank you all in advance.

 

Here is the query M-code (I did build this with the PQ GUI):

let
Source = Web.Page(Web.Contents("https://finance.yahoo.com/quotes/MSFT,AAPL,DIS/view/v1")),
Data0 = Source{0}[Data],
#"Changed Type" = Table.TransformColumnTypes(Data0,{{"Symbol", type text}, {"Last Price", type number}, {"Change", type number}, {"Chg %", Percentage.Type}, {"Currency", type text}, {"Market Time", type text}, {"Volume", type text}, {"Shares", type text}, {"Avg Vol (3m)", type text}, {"Day Range", type text}, {"52-Wk Range", type text}, {"Day Chart", type text}, {"Market Cap", type text}, {"", type text}})
in
#"Changed Type"

4 REPLIES 4
BoaRoland
Regular Visitor

Thank you very much for the info on RelativePath.

Roland

lbendlin
Super User
Super User

you need to use RelativePath,  and construct the symbol list separately (or source it from your Excel etc)

 

 

 

 

let
Symbols = {"MSFT","AAPL","DIS"},
Source = Web.Page(Web.Contents("https://finance.yahoo.com/quotes/",[RelativePath= Text.Combine(Symbols,",") & "/view/v1"])),
Data0 = Source{0}[Data],
#"Changed Type" = Table.TransformColumnTypes(Data0,{{"Symbol", type text}, {"Last Price", type number}, {"Change", type number}, {"Chg %", Percentage.Type}, {"Currency", type text}, {"Market Time", type text}, {"Volume", type text}, {"Shares", type text}, {"Avg Vol (3m)", type text}, {"Day Range", type text}, {"52-Wk Range", type text}, {"Day Chart", type text}, {"Market Cap", type text}, {"", type text}})
in
#"Changed Type"

 

 

Here's all the documentation you will ever need 🙂  Web.Contents - PowerQuery M | Microsoft Learn

Thank very much lbendlin,

Can I use a named range in my Excel to hold the symbol list and then reference that named range in the Power Query code? I'm still fairly new with M-syntax/code.

How can I use the set the variable "Symbols" to equal the named range "Secs"?

Or maybe I should refer to the named range in the Text.Combine part??

I tried various syntax, but all resulted in errors.

Thank you very much in advance.

Roland

 

Can I use a named range in my Excel 

The preferred way is to use a table. Then use Text.Combine on the relevant column of that table source.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors