Hi

I've set up a query that combines actual and forecast data.  This is great as I can then use Power Pivot to put together a nice little report allowing users to view the forecast and actual data side by side.  Although this works fine in the workbook that contains the source data when I try to copy the report to another workbook (for public consumption) I get errors.

The first error was around referencing the source data.  When I went into the Advanced Editor I noticed that the source was still referencing Excel.CurrentWorkbook().  However, the new workbook doesn't contain the source data.  I managed to get around this by using Excel.Workbook(Web.Contents(<SharePoint path and file name>)).  Then I got the error, Expression.Error: The column 'Column1' of the table wasn't found.

I then added {[Name="Forecast"]} to reference the named range in the workbook hoping this would solve the problem, however I now have the following error message, Expression.Error: We cannot convert a value of type Record to type Table.

My M code experience is still rather limited, I can make small edits, however I'm completely out of my depth in this case.

Ideally I don't want to have the source data stored in more than 1 workbook.  I was hoping to keep the connection and just be able to do a "Refresh All".

I would really appreciate any guidance and I apologise if this topic has already been covered (I'm new to the forum).

Thanks

Maureen