locked
How to reference a PowerQuery table, loaded as Connection only, from a formula within the excel workbook RRS feed

  • Question

  • I'm working with large amounts of data and for sanity purposes would like to keep the raw data out of my workbook. Is there a way to perform SUMIFS and other calculations referencing data ranges from the workbook query connection table?
    Wednesday, September 21, 2016 10:30 PM

Answers

  • The data must land somewhere in order to use it in a worksheet. If you do not want it in a worksheet, your other option is to load it into the data model (PowerPivot) and create pivot tables based on it.
    Thursday, September 22, 2016 12:17 PM

All replies

  • The data must land somewhere in order to use it in a worksheet. If you do not want it in a worksheet, your other option is to load it into the data model (PowerPivot) and create pivot tables based on it.
    Thursday, September 22, 2016 12:17 PM
  • What Bertrand says. Another option is to create a model in SSAS Tabular or in Power BI and have Excel connect directly to that model - no data in Excel, only output. But you would need to create all logic in the model, not in Excel formulas.
    • Proposed as answer by Michael Amadi Thursday, October 20, 2016 11:37 AM
    Thursday, September 22, 2016 6:18 PM
  • In addition to everything that has been said above you can use cubefunctions to retrieve data from the data model in single cells.

    Imke Feldmann TheBIccountant.com

    • Proposed as answer by Michael Amadi Thursday, October 20, 2016 11:37 AM
    Friday, September 23, 2016 2:18 PM