+ Reply to Thread
Results 1 to 17 of 17

How to loop through values using Power Query?

  1. #1
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,824

    How to loop through values using Power Query?

    I'm not real familiar with Power Query, but I have this Workbook with Power Query working to get a couple of tables to download regarding information on Stocks (at Yahoo finance). Right now it works for Microsoft (hard coded). On one sheet I have a list of stock symbols that I'd like to use to loop through Power query so that I end up all the stocks appending their information to the 2 tables listed. In addition, as you can see on the output, that I've manually added the ticker symbol to the table. How can I get that to automatically add to the table?
    Thanks for any help you can give.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,824

    Re: How to loop through values using Power Query?

    I'm hoping someone can help here. I hope I put this under the right category (Excel - general).

  3. #3
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: How to loop through values using Power Query?

    The attached workbook uses PQ functions to get the Inst. Stock and Mutual Fund details for the listed stock symbols.

    Is that something you can work with?
    Attached Files Attached Files
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  4. #4
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,824

    Re: How to loop through values using Power Query?

    When I try to Refresh the data, I get a pop-up that says, "Data could not be retrieved from the database. Check the database server or contact your database admin. Make sure external db is available. Am I doing something wrong?

  5. #5
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: How to loop through values using Power Query?

    Have you tried editing one of the connecting queries? I'm thinking you may be prompted to reset the connection to "anonymous".

    Does that help?

  6. #6
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,824

    Re: How to loop through values using Power Query?

    Not so far. I'm trying all sorts of things and so far I haven't found the magic bullet. I'll keep trying and see if I get lucky.

  7. #7
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: How to loop through values using Power Query?

    I think the problem is on the Yahoo side. Most times I can run both queries. But sometimes I get that error. The website might be gettng jammed up by the repeated hits from the same query. When it does work it take several minutes.

  8. #8
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,824

    Re: How to loop through values using Power Query?

    OK - I'll keep trying, but so far it's never worked for me. If you can think of anything else, I'd appreciate it. Thanks.

  9. #9
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,824

    Re: How to loop through values using Power Query?

    Just wondering if you can think of anything else. It has never worked for me yet. Always getting the "Data could not be retrieved from the database."... error.

  10. #10
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: How to loop through values using Power Query?

    Just to make sure the query works for you....try processing a reduced number of symbols. Maybe 10?
    Let us know if that is successful.

  11. #11
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,824

    Re: How to loop through values using Power Query?

    Unfortunately not. I deleted all rows except for the first 9 on the stock symbols tab, then went to the "Institutional" tab, went to "Data" menu, then "Refresh" and I received the same error. I then deleted all the rows but the top 10 on this sheet and tried the same thing and received the same error.

  12. #12
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: How to loop through values using Power Query?

    OK....I'm officially puzzled. I can run all symbols (most times) without incident. So I know the queries certainly can work and when they don't, it seems to be traffic related). I'd be curious to know if anybody following this thread can open the file I'd attached and run the queries.

  13. #13
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,824

    Re: How to loop through values using Power Query?

    When I go to "Query", then "Edit" to bring up PQ Editor, I get the message:
    "Formula.Firewall: Query 'GetInstitutional' (step 'RemoveNullRows') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
    When I expand the "Queries" frame, it shows an exclamation in a triangle next to "Query1" and "GetInstitutional" and "GetmutualFunds".
    If I click on "Query1", at the top I get "Information is required about data privacy." and a continue button. I click on that button, and i get a pop-up about privacy levels. One option is a check box to Ignore Privacy levels checks for this file... I check that box and click "Save" and it refreshes - resulting in the word "null" in the center and the Exclamation point is replaced with a question mark.
    I then click on "GetInstitutional" and it says "Preview is 4 days old with a refresh button next to it. I click on that and it refreshes and the exclamation point goes away.
    Same for "GetMutualFunds".
    Under "Other Queries (3), "Top Institutional..." had an Exclamation point, so I click on that and exclamation point goes away and then there's "Errors in GetMutualFunds" so I click on that and at the top middle is the message, "Expression.Error: The column 'URL' of the table wasn't found. Details: URL with a "Go to Error" button. I click on that button and under Applied Steps it goes to "Kept Errors".
    I don't know what to do at this point.

  14. #14
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: How to loop through values using Power Query?

    I think there's some useful information in your response.

    Start with these Power Query options then try again:

    GLOBAL
    Data Load
    - Specify custom default load settings
    --Un-check: Load to worksheet
    --Un-check: Load to Data Model
    - Check: Fast Data Load

    Privacy
    - Select: Always ignore Privacy Level settings

    CURRENT WORKBOOK
    - Background Data
    -- Un-check: Allow data preview to download in the background

  15. #15
    Forum Expert
    Join Date
    02-10-2019
    Location
    Georgia, USA
    MS-Off Ver
    Office 365
    Posts
    2,824

    Re: How to loop through values using Power Query?

    OMG - that seemed to do it! Thank you!

  16. #16
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: How to loop through values using Power Query?

    Glad you got something you can use!

    If that resolves your issue, please take a moment to mark this thread as SOLVED (from the Thread Tools menu)

  17. #17
    Forum Contributor
    Join Date
    04-18-2011
    Location
    London, England
    MS-Off Ver
    Excel 2016
    Posts
    105

    Re: How to loop through values using Power Query?

    Hi Ron,

    I am on pro office 2016 and cant seem to load the file "Formula.Firewall: Query 'GetInstitutional' (step 'RemoveNullRows') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination."

    Thanks
    Attached Images Attached Images
    Last edited by nd4spd; 03-25-2020 at 09:15 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Power Query - excel formula translation into Power Query
    By afgi in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 02-19-2020, 03:38 AM
  2. Replies: 4
    Last Post: 02-17-2020, 06:03 AM
  3. Loop in Power Query
    By Szymon.L in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-19-2018, 03:04 AM
  4. Sub-Forum for Excel Power Tools (Power Query, Power Pivot & Power BI)
    By chullan88 in forum Suggestions for Improvement
    Replies: 10
    Last Post: 06-28-2018, 02:25 PM
  5. [SOLVED] Power Query showing Null but values exist?
    By taylorsm in forum Excel General
    Replies: 3
    Last Post: 04-10-2018, 10:19 AM
  6. IF then LOOP power query
    By 97hills in forum Excel General
    Replies: 1
    Last Post: 08-24-2017, 10:29 AM
  7. How to remove null values from several columns with Power Query?
    By toblju in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-12-2016, 11:08 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1