Can Power Query Use Its Own Name or Query Result Sheet Names To Build Dynamic Queries?

PizzaSpaghetti 1 Reputation point
2022-03-22T18:54:56.62+00:00

I did research for the question in the title. So far, all answers were negative or came up short.

I'm melding worksheets from 2 workbooks into a single workbook for comparison and further analysis. Both have many connections and queries. The 2 workbooks have identical structures and are different only in the data Item(s) on which they report (for example, they may report on different product numbers).

Because the structures are identical, they have the same worksheet names, same connection names, same named ranges, same query names, ... etc. Prior to melding the workbooks together, all these structural things are renamed with a suffix to avoid conflicts upon melding. Everything from the first workbook is renamed with "_1st", and everything from the second workbook is renamed with "_2nd".

The requirement is to support refreshing the melded queries for the given Item(s) which are listed in an array in a named range (of which there are 2 after melding ... ItemArray_1st and ItemArray_2nd).

The query needs to know which of the 2 ItemArrays to use, depending on which part of the melded workbook is being refreshed. If the literals "_1st" or "_2nd" can be obtained into a Power Query variable, from either the query name, the results worksheet name, or some other external means, that can be used in the PQ logic to dynamically build the queries.

Thanks in advance.

Not Monitored
Not Monitored
Tag not monitored by Microsoft.
36,409 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Dave Patrick 426.2K Reputation points MVP
    2022-03-22T18:58:21.217+00:00

    Power query is not currently supported here on Q&A. The product group for Power Query actively monitors questions over at
    https://community.powerbi.com/t5/Power-Query/bd-p/power-bi-services

    --please don't forget to Accept as answer if the reply is helpful--

    0 comments No comments