locked
Power Query - Initialization of the data source failed RRS feed

  • Question

  • I have excel 2013 with Power Query installed.

    My co-workers have the same version of excel 2013, but do not have Power Query installed.

    I exported a SharePoint list to an excel file, which resides on a network drive. When you open excel, it connects to SharePoint & download the latest data. So far this works for everyone.

    Next I used Power Query to build some queries & saved the file. When I open it, it works fine. When other people open it, they receive the error "Initialization of the data source failed". If I have 3 queries, they get this error 3 times. This error is preventing the data from refreshing.

    1. How can I get rid of this error for them? I can't expect everyone to install Power Query.
    2. I've read that this issue may be caused by old versions of excel 2010. However, this is not the case.
    3. I've read that this issue may be caused by combining an external data source with another query. I don't believe this is the case. To avoid this I made a master query of the SharePoint list. Then based subsequent queries off of the master query. With either method, users still receive the same error, which brings me back to #1

    The goal is to have anyone open this file up & get the latest reports/metrics/queries (even if they don't have power query).


    • Edited by csblake Wednesday, August 24, 2016 7:08 PM
    Wednesday, August 24, 2016 4:59 PM

Answers

  • Hello,

    How should it work to refresh the data if the tools to load the data = Power Query isn't installed?

    Turn of the auto refresh option on workbook load to "Off", this should prevent the error message; but of course the other still can't refresh the data on there own.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, August 24, 2016 5:40 PM

All replies

  • Hello,

    How should it work to refresh the data if the tools to load the data = Power Query isn't installed?

    Turn of the auto refresh option on workbook load to "Off", this should prevent the error message; but of course the other still can't refresh the data on there own.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, August 24, 2016 5:40 PM
  • A User, without Power Query installed, should be able to open up the file & see the latest data available.

    (Ex: Excel Opens, Updates the SharePoint Table, Updates the Queries based on that table, Updates the Pivot tables based on those queries)

    Based on your suggestion, I disabled the "Refresh data when opening the file" for each query. This does prevent the error message for non Power Query users. As you said, they can't refresh the data on their own. If they try, they get the error message.

    This means that I would have to update & save the file for them. I'd like to avoid this if possible. It would be better if they could refresh it themselves at any time.

    My query can be as simple as this and they still get the error:

    let
        Source = Excel.CurrentWorkbook(){[Name="Deliveries"]}[Content]
    in
        Source

    Wednesday, August 24, 2016 7:44 PM
  • As Olaf mentions, you cannot expect functionality of any software to work when the software isn't installed.

    The solution to your requirement "A User, without Power Query installed, should be able to open up the file & see the latest data available." is: don't use Power Query, then.

    Friday, August 26, 2016 8:35 AM
    Answerer
  • I've just started using Power Query and had been assuming that it's a glorified wizard for setting up a standard Excel data connection.

    From what you're saying, Olaf/Michiel, PQ is creating its own custom data connections which cannot be used at all by non-PQ users?

    If so - what a shame, is all I can say! As csblake said, if you've a large audience for a particular report then you cannot expect everyone to install PQ.

    And, whilst it's easy to offer the 'solution' don't use Power Query people such as myself, and presumably csblake, are using this solution as it's the only way to do relatively complex table joins and transformations without much more experienced with SQL. 

    So - yes - a shame.

    Thursday, April 20, 2017 2:01 PM
  • Power Query is called Get & Transform from Excel 2016 onwards (where it is fully included in Excel and no add-on needs to be installed manually any more). There it becomes the new standard Excel data connection: https://support.office.com/en-us/article/Unified-Get-Transform-ad78befd-eb1c-4ea7-a55d-79d1d67cf9b3?ui=en-US&rs=en-US&ad=US&fromAR=1

    So what you're experiencing in previous versions are "transition pains" that are probably to be expected with such a change unfortunately.


    Imke Feldmann
    MVP Data Platform
    TheBIccountant.com

    Thursday, April 20, 2017 8:45 PM
    Answerer
  • Very interesting - thank you Imke. I agree - it is currently 'painful'!
    Friday, April 21, 2017 9:45 AM