locked
Append ignoring column errors RRS feed

  • Question

  • Hi, I'm very new to using power queries.

    I've tried various searches and I'm piecing things together somewhat but I was hoping someone here might give me some 'customised' advice on how I should tackle this:

    I have and Excel file which tracks dividend results of stocks I own.

    Using one particular site I'd like to import div data from various stocks appending each web table into one query listing historical dividend data of all stocks in my portfolio.

    My main hurdles have been:

    1. Working out a way to handle the fluctuating list of stocks. It's easy enough for me to create a list of URLs in my spreadsheet but the number of stocks fluctuate as well as the stocks themselves as I buy and sell. My current approach is to run a set number of connection queries substituting empty fields with a known dummy stock and then removing duplicates from the final result but I'm sure there's a more eligant solution.
    2. Handling the stocks which do not pay dividends as this results in a 'missing column' error which makes the final append query throw an error. Not sure where to put the 'try' and 'otherwise' if that is indeed how to handle this.
    3. Future proofing results - the available data on the site I'm using only goes back so far. In future years the data I'd like to keep will drop off these web tables. I'm thinking I might need to 'store' the results statically in a table and then use an 'append only missing records' type approach perhaps? Not exactly sure how I'd tackle that.

    Thanks very much. Sorry to ask multiple things in one post but context often helps.

    Monday, December 16, 2019 7:47 AM

Answers

  • Hi Jon,

    re 3) using Excel, it's not too tricky actually: https://www.thebiccountant.com/2016/02/09/how-to-create-a-load-history-or-load-log-in-power-query-or-power-bi/


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Saturday, December 21, 2019 10:16 AM

All replies

  • Hi Jon. For #1, you probably just want to start with a list of URLs (or stock symbols, which can be used to generate the URLs) and then invoke a custom function over the list in order to pull the relevant info you're interested in. There are many examples online of doing this kind of thing.

    #2 is largely dependent on the page you're pulling from and what kinds of errors can/will occur.

    For #3, it's tricky/advanced. But community members (such as Imke) have provided some examples of doing this kind of thing in the past.

    Ehren

    Thursday, December 19, 2019 12:58 AM
  • Hi Jon,

    re 3) using Excel, it's not too tricky actually: https://www.thebiccountant.com/2016/02/09/how-to-create-a-load-history-or-load-log-in-power-query-or-power-bi/


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Saturday, December 21, 2019 10:16 AM