Merge query

[Dicken]

Hi,
I've merged two tables in the power query editor, (not as new ) as I just want to add data from the sencond table to the first.
This works fine and bring in what I want. This may sound stupid but I don't seem to be able to 'Close and load' I just end up with the table unalterd.
I've tried using 'refresh all'. I could just creat 'as new' but I'd like to know where I'm going wrong.

RD

[AliGW]

Your title could have been more detailed, but I'll let it go ... this time.

You cannot merge back to a source table. If you want hands on guidance, then please upload a sample workbook.

There are instructions at the top of the page explaining how to attach your sample workbook.

[Dicken]

Hi,
I've attached the simple workbook tables I've been playing around with, the merged data model included, as I said I'm fine with 'merge queries as new', using merge I can get the data into power query and run some transformations, but how to I actually do anything with it. You said you can't merge back to a source table which from what i'm getting seems to be true. But what is the point of the 'merge' button? Anyway thank you for helping, I've been through all the MS pages, or at least think I have and though they acknowledge a difference between ' merge' and 'new' they're not too detailed in their use.
Thanks,
RD

[AliGW]

There's nothing attached ... yet.

You said you can't merge back to a source table which from what i'm getting seems to be true.

Yes - I know because I've been using PQ for a long time now.

[Dicken]

Sorry, I wasn't doubting you were right.
I'll try and re or attach the file, this is more because I'd like to get to the bottom of how to do this rather than some desperate practical need.

[AliGW]

OK - tell me what we are looking at, specifically what you want to do and where the results are meant to be.

I am afraid this has not clarified anything for me - sorry.

[Dicken]

Ok,
Sorry couldn't get back to you yesterday, I'm trying to merge the product ID from table 2 into the table 1, I can do this using the 'merge as new option' and have attached a workbook showing the result, but I'm trying to use the 'merge' option, which from the ms support pages seem to mean you don't create a new table, just add the the data from one to the other, as you would if using vlookup xlook etc.

Hope that helps,
And thanks,
RD

[AliGW]

OK - here's the point: in PQ, the only way to combine the two is to create a new table. You cannot merge one source table with another WITHOUT creating a third table. That's just the way it is, so if you really want to use PQ for this, then this is the work flow you have to accept.

Based on the sample data, I don't see the need for PQ: it's easy enough to do what you want ON the source table by adding a column and using INDEX MATCH and/or XLOOKUP.

[Dicken]

Right, well that's solved that. I know the data I sent was simple, it was just for testing things out.
I was beginning to come to the conclusion that it wasn't possible. I know you've been very patient but in that case what is the 'merge' for if you are going to have to create a new table why not just have the 'merge as new'? I've been playing around with append and that seems to be the same situation.
Still at least now I know I wasn't doing anything wrong or overlooking a step.
Thanks for that.
RD

[AliGW]

Because you can merge multiple tables into one resulting query. If you merge as new every time, you'll end up with a whole set of nested queries.

So your workflow would be to load your satellite (lookup) tables as connection only, then load your main source table and merge it with each of the connection only queries in turn from the source table's query interface. This is what is then closed and loaded to a new worksheet in your workbook (or wherever you want it).

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

[AliGW]

Thanks for the rep.