Power Query: Merge Query using Reference Query

Hi

In theory, is it possible to Merge Query against a Reference query back to the original data?

I have a data file, then creating a Reference Query (not Duplicating) using the data file, and then Grouping the data using the Reference. This is simply to total values on invoice numbers. This is then being merged back to the main data on each line. If the invoice has multiple lines then the same value is entered on each line.

So the issue is, when trying to merge, the 2nd query file (the Reference Group) is showing blank, and I can't continue. The Reference Query shows in the dropdown option, then the fields just don't show.

If I duplicate the data it works perfectly, but want to use Reference if possible to speed up refreshes.

Does Power Query dislike merging back to itself is using Reference?

I am using live business information so unable to post the file, so just asking if it should be doable, in theory. (If it is then I'll sort sample data and post)

thanks
Barry

I do it frequently.
You could try to use a index column..

Perhaps being thick, but how does an index column help?

Have attached a screenshot of what's happening.
Stg_Invoice303_RS is my main data. I've Referenced this into the query named Reference.
On Reference I have Grouped by Invoice number, with a Sum value.
In Stg_Invoice303_RS I am using Merge Query, and on the window that pops up, where I am selecting Reference, the field window shows No Preview Available. So I can't continue.

If I use Duplicate instead of Reference it works...

Originally Posted by rorya

It seems to me that you are essentially creating a circular reference. Your Reference query is a reference to the final result of the first query, which would depend on the merge you are doing.

Yeah, I did wonder if that would be the issue, given it works fine when Duplicated. Oh well, at least I know.

Cheers

In this file I did a self-reference in lines 'ToBeAppendedQuery' and 'ToAppend'.

Originally Posted by rorya

You'd need two reference queries, so you can merge those two as they don't depend on each other.

Ah good shout. This works. Thank you!

Well, if you do not see an example in which a solution can be applied, misinterpretations may occur.