none
Is it possible to use Structured Cell Reference Formulas for Power Queries? RRS feed

  • Question

  • Just wondering if it is possible to retrieve data from a Power Query query (that hasn't been loaded anywhere) directly via Structured Cell Reference Formulas? (Instead of loading it to the PP model and use cubefunctions)

    Imke Feldmann TheBIccountant.com


    Tuesday, October 18, 2016 11:23 AM
    Moderator

Answers

  • Hi Imke,

    I don't see how this scenario is remotely possible.  It would be like trying to get different parts of a cube with no cube functions.

    (1) A Power Query query can be a table, a custom function, a single value, or (lately) a parameter.
    (2) Excel's name object has no visibility of Power Query query names.
    (3) There are no Excel functions that can reference a name it can't see, or that can differentiate among the various types of queries listed in (1).

    However, in the future, it should be possible to add new functions that can address the aforementioned issues.

    Tuesday, October 18, 2016 7:41 PM

All replies

  • Hi Imke,

    I don't see how this scenario is remotely possible.  It would be like trying to get different parts of a cube with no cube functions.

    (1) A Power Query query can be a table, a custom function, a single value, or (lately) a parameter.
    (2) Excel's name object has no visibility of Power Query query names.
    (3) There are no Excel functions that can reference a name it can't see, or that can differentiate among the various types of queries listed in (1).

    However, in the future, it should be possible to add new functions that can address the aforementioned issues.

    Tuesday, October 18, 2016 7:41 PM
  • Hi Colin,

    thx for the answer. How would this be possible in the future?

    (and how long do we have to wait for it ? :-) )


    Imke Feldmann TheBIccountant.com

    Tuesday, October 18, 2016 7:58 PM
    Moderator
  • Like the cube functions, there would have to be special Power Query functions built into Excel. Out of curiosity, what are the specific elements of a Power Query table that you would want to reference?

    Tuesday, October 18, 2016 9:21 PM
  • Hi Colin,

    This is about giving clients with no knowledge of the Power tools options to drag own reports from automated Excel solutions that have been developed with Power Query. Often a denormalized table will be fine that they can query with pivot tables. But when the time comes when cubefunctions would come into play, things don’t look sexy any more. And – if querying just one table – would actually be overkill.

    So actually ideal in that case would be an option, where the pivot table that has been created based on a Power Query table, could be converted into table-functions like SUMIFS or AGGREGATE for values and VLOOKUPS for attributes: Functions that they might even be familiar with but cell references expressed in STR.

    This is possible at the moment if you load the denormalized table into the workbook. But this option has also downsides and limitations.

    So I trust your “not possible at the moment” and will move that over to User voice.


    Imke Feldmann TheBIccountant.com

    Wednesday, October 19, 2016 6:17 AM
    Moderator
  • Hi Imke,

    I think that I understand where you're going with this. Suppose that we had a hypothetical new Excel function called

    QUERYTABLECOLUMN(<QueryName>, <ColumnName>, <optional AggregationType>),

    which returns an array of column values from a Power Query query, with the option to aggregate the array before returning the result to Excel.

    Now you want to use, say, the Excel function SUMIFS like so:

    SUMIFS(QUERYTABLECOLUMN(Table1, [Column1]),QUERYTABLECOLUMN(Table1, [Column2]),"=Apples")

    So that you would sum the values in [Column1] of the query "Table1", for only those values in [Column2] with apples.

    Am I on the right track? If so, the above example will fail :(, because unlike some other functions that can accept arrays as parameters, SUMIFS only takes cell ranges.

    If somehow Excel were able to recognize Power Query table names, then presumably one would be able to use structured referencing without having to resort to any special functions.

    Wednesday, October 19, 2016 10:03 PM
  • Hi Colin,

    yes, that goes into the direction I was thinking. Will need to prepare a better case description in order to mobilize some votes for this feature and post the link here so that you can vote as well ;-)


    Imke Feldmann TheBIccountant.com

    Thursday, October 20, 2016 7:29 AM
    Moderator