none
Refresh Fail when I change source path or make any modifications to the source RRS feed

  • Question

  • I created an awesome Power Pivot report in Excel 2013 for a client.  They’re thrilled to pieces that they can use Excel – which they already own to analyze a fairly large data set and don’t have to buy any additional software.  Yay. The data comes from a proprietary industry specific database as a csv.  I can refresh the data with newer results any time I like by replacing the CSV file, but if I modify the file location in advanced properties or in the gui dialog box, one of the 2 power queries ALWAYS fails. So I can’t turn over the reins to them so they can update the data as needed. 

    Ideally, I need to write instructions that will enable them to change the data source from a file path on my local computer to their local computer(s) so they can refresh the data as necessary and use all the pivot tables, charts and calculations I already built.

    This is the error I get on refresh:  Exception from HRESULT: 0x800A03EC

    Thanks

    E

    Thursday, October 1, 2015 5:03 PM

Answers

  • I'd recommend to make this more transparent: Pass the path as a parameter into your query instead of modifying the query. Thereby you avoid changes in the query itself which can cause problems with Power Pivot.

    Pass the path where the csv file shall be fetched from as a cell into your Excel sheet and name that cell "Path".

    You can then pass it to your query like this:

    let

    // Fetches the content in your current workbook with the name "Path"

    Path = Excel.CurrentWorkbook(){[Name="Path"]}[Content]{0}[Column1],

    // replaces the hardcoded path by the content of your parameter

    Source = Csv.Document(File.Contents(Path),[Delimiter="...",Encoding=...]),


    Imke Feldmann TheBIccountant.com


    Friday, October 2, 2015 5:46 AM
    Moderator
  • Hi Elli. This sounds like the "read-only connection issue". You can read more about how to avoid it here:

    http://blog.crossjoin.co.uk/2014/09/08/power-pivot-power-query-read-only-connection-problems-in-excel-2013-and-what-to-do-about-them/

    Ehren

    Tuesday, October 6, 2015 10:55 PM
    Owner

All replies

  • I'd recommend to make this more transparent: Pass the path as a parameter into your query instead of modifying the query. Thereby you avoid changes in the query itself which can cause problems with Power Pivot.

    Pass the path where the csv file shall be fetched from as a cell into your Excel sheet and name that cell "Path".

    You can then pass it to your query like this:

    let

    // Fetches the content in your current workbook with the name "Path"

    Path = Excel.CurrentWorkbook(){[Name="Path"]}[Content]{0}[Column1],

    // replaces the hardcoded path by the content of your parameter

    Source = Csv.Document(File.Contents(Path),[Delimiter="...",Encoding=...]),


    Imke Feldmann TheBIccountant.com


    Friday, October 2, 2015 5:46 AM
    Moderator
  • Hi Elli. This sounds like the "read-only connection issue". You can read more about how to avoid it here:

    http://blog.crossjoin.co.uk/2014/09/08/power-pivot-power-query-read-only-connection-problems-in-excel-2013-and-what-to-do-about-them/

    Ehren

    Tuesday, October 6, 2015 10:55 PM
    Owner