Power BI Exchange

Expand all | Collapse all

Web query, loop through a list, append to a single table by altering M code

  • 1.  Web query, loop through a list, append to a single table by altering M code

    Posted 06-12-2018 02:33 PM
      |   view attached

    Hopefully the tile of this thread is suitable, I don't have enough experience to know how to describe what I want to do.

    https://social.technet.microsoft.com/Forums/en-US/0f9fec02-4469-4c56-92ec-00d46b26b3fc/how-to-automateparameterizeloop-power-query?referrer=http://social.technet.microsoft.com/Forums/en-US/0f9fec02-4469-4c56-92ec-00d46b26b3fc/how-to-automateparameterizeloop-power-query?forum=powerquery

    I've been inspired by the above post, more specifically the lengthy reply mid-way through the page.  However, I'm a bit over my skis on this one. I can generally follow, but I get lost half way through trying to reinterpret for my use.  I guess it also doesn't help that I don't know M at all.

     Essentially, I want to scrape info from a table on Morningstar and repeat the info gathering process via a list from a csv/excel file.  Alternatively, if it helps I can build the web url for each symbol since it follows a certain pattern.  Attached is a pbix file with the initial test query that generated the M code:

    let

        Source = Web.Page(Web.Contents("http://financials.morningstar.com/fund/purchase-info.html?t=GGHCX&region=usa&culture=en-US")),

        Data2 = Source{2}[Data],

        #"Changed Type" = Table.TransformColumnTypes(Data2,{{"Fund Name", type text}, {"Front Load", type text}, {"Deferred Load", type text}, {"Expense Ratio", type number}, {"Min. Init. Purchase", type text}, {"12b-1 Actual", type text}, {"Purchase Constraint", type text}, {"Shareclass Attributes", type text}})

    in

        #"Changed Type"

    The pbix file also has two tables loaded in, one with a list of symbols the other has all the urls that will eventually be called on. Not sure which will be more useful, or the best way to alter the M code. For this particular web query, I'm only interested in table 2 "Review Other Classes".

    What I want to do is to cycle through all the symbols gather each table and append into a single table with the symbol associated with each record in its own column.  As an fyi each table can have different # of rows. How would I reference a source table either loaded into .pbix or in a folder location? How can I automate the query/ table retrieval and append process?

    Extra credit?? While I mentioned that I'd like to know which symbol retrieved what row info by using it in a column, (and if I figure this out I'll be using that process on a number of sites!) Ultimately the table on the website has a hyperlink/symbol associated with each share class fund description. If you hover over it it displays the symbol, if you click it you get redirected to the webpage for that share class. Would there be a way to scrape the symbol for each share class description and append to the table? or at minimum get the url (i can parse from there)

     

    Any help, ideas or redirection to more info very appreciated!



    ------------------------------
    Brian Brown
    Operations Manager

    ------------------------------

    Attachment(s)

    pbix
    12b-1 scrape.pbix   45K 1 version


  • 2.  RE: Web query, loop through a list, append to a single table by altering M code

    Posted 06-12-2018 04:13 PM
    Edited by Olivier Travers 06-12-2018 04:13 PM
    Turn your web query into a function then invoke that function from a copy (use "Reference") of your parameter table (e.g. list of stock tickers). It will look like this in PQ:

    I don't have time to expand but read this for details:
    Creating M Functions From Parameterised Queries In Power BI

    Be aware that schedule refreshes won't work in the service unless the first part of your web url works without parameters. See this for details:
    Web.Contents(), M Functions And Dataset Refresh Errors In Power BI

    ------------------------------
    Olivier Travers
    BI & SaaS Integration Consultant
    ------------------------------



  • 3.  RE: Web query, loop through a list, append to a single table by altering M code

    Posted 06-13-2018 09:28 AM
    Thanks for the link(s)!  I believe this will be sending me down the right path and then some.  I'll update the thread once i figure it out.

    ------------------------------
    Brian Brown

    ------------------------------



  • 4.  RE: Web query, loop through a list, append to a single table by altering M code

    Posted 06-13-2018 05:35 PM
      |   view attached
    OK I thought I'd be able to figure it out without further guidance. I need help on how to get the 'expand' option to work.  Per the great link suggested above it led me to some other links I've been using for those other newbies who may follow this thread in the future:

    Iterating over multiple pages of web data using Power Query
    Power Query M function reference | Microsoft Docs

    I was able to successfully create a function, have the manual parameter entry work, and add a custom column to my symbol list. I'm stuck on how to expand the 'function' associated with each symbol in the symbol list.  I suppose the other issue is that per the screen shots, the custom columns should be displaying "table" instead of "function". I've play around with putting "t" and "symbol" in various places the table query, but it just results in an error. I feel like I'm super close, but missing something minor but important.

    at this stage my function is GetShareClassInfo:

    ()=>
    (t as any) as table =>
    let
    Source = Web.Page(Web.Contents("http://financials.morningstar.com/fund/purchase-info.html?t=" & Text.From(t) & "&region=usa&culture=en-US")),
    Data2 = Source{2}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data2,{{"Fund Name", type text}, {"Front Load", type text}, {"Deferred Load", type text}, {"Expense Ratio", type number}, {"Min. Init. Purchase", type text}, {"12b-1 Actual", type text}, {"Purchase Constraint", type text}, {"Shareclass Attributes", type text}})
    in
    #"Changed Type"

    My Symbol table query is now:

    let
    Source = Excel.Workbook(File.Contents("D:\IPG\IPG - data analysis\TRW excel files\symbols.xlsx"), null, true),
    symbols_Table = Source{[Item="symbols",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(symbols_Table,{{"symbol", type text}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "Custom", each GetShareClassInfo())
    in
    #"Invoked Custom Function"

    any suggestions or additional resources to help clear up my misunderstanding?

    ------------------------------
    Brian Brown

    ------------------------------

    Attachment(s)

    pbix
    12b-1 scrape.pbix   51K 1 version


  • 5.  RE: Web query, loop through a list, append to a single table by altering M code

    Posted 06-14-2018 10:59 AM
    At a quick glance, the issue is that you're not passing along your symbol when you're invoking the function. Here's a couple snippets to show you how I structure things in my FX example, adapt as needed. See how the color coding maps the variables:

    "FX Pair" function:
    = (BaseCurrency as text, QuoteCurrency as text) => let Source = Xml.Tables(Web.Contents("https://sdw-wsrest.ecb.europa.eu/service/data/EXR", [RelativePath = "/D." & QuoteCurrency & "." & BaseCurrency & ".SP00.A"]))


    Function call from a list of currencies with a Currency column (this would be your list of stock symbols):

    = Table.AddColumn(Source, "Data", each #"FX Pair"("EUR", [Currency]))


    ------------------------------
    Olivier Travers
    BI & SaaS Integration Consultant
    ------------------------------