Powe query repeating merge and name handling

[NewtoPQ25]

Here is my task. I have created a calendar table then want to 1. merge with a only selected column a time from a number of large data set. 2. I already loaded each data set a query and have standard name like A B C. All data has column 0 listed as dates to be used for merge.
2. I created an excel parameter table. It contains many columns but 2 rows - 1. query name A or B or C ) and 2. Number of coloumn to merge ( 2, or 5 or 100 etc.).

3. I created a function that takes parameter name and query name and the number of column, then take that data to form a 2 column table {date one column data}. theses all tested working well when doing a single merge with my complete calendar table - left join. I even pull in old column name  added then reuse as new column name. Then select it only it when expanding. So far so good as I played within a single let in file.

4. Now is my challenge. How to do repeats and assign query names? Next I want to create a loop say 100 calls to do 100 merges. Each time it takes query name and column number, add to the updated table with a new column. 
5. my challange one is how to auto name the new query just merged, and it's name can be auto retrieved and do the same expanding as I only want to keep newly added coloumn. 
6. I have no problem it auto assigns a new name by adding a number. If with in a repeated let in code.

7. Can I learn a way how to repeatedly add queries (say 100 times) each time doing the same it takes last query name as reference find what to merge, do merge, assign new name or use same name,  expand correctly until end.

I hope I explained this clearly. I worked hard to cracked a portion of the problem but get stuck on how I can handle new query name and expand and let auto merge to continue. A bit puzzled wondering if I shall just go back to excel doing this by Vlookup? If you have a suggestion I will highly appreciate it!

[BA_Pete · Accepted solution]

Hi @NewtoPQ25 ,

If, when you refer to 'new queries' and 'new query names' you mean the actual Power Query queries i.e. tables/table queries, then I don't think you're going to be able to do what you want to do here. As far as I'm aware, M code in Power Query CAN NOT spawn new table queries dynamically, nor can it edit table query objects, such as renaming them etc. M code works only within the query to which it relates, it can not perform actions outside of that query (within the scope of your request, anyway).

What (I think) you're describing is some sort of robotic process automation task which, though technically feasible I guess, certainly would not be something I'd be aiming at Excel or Power BI Desktop query editors to be honest.

...or, if I've completely misunderstood that bit, then probably the merge operation in List.Accumulate? If so, I'll leave it to @jgeddes . They're a pro and they were also here first 🙂

Pete

[NewtoPQ25]

Hi@BA_Pete, thank you so much for insightful comments. I think I read something as you mentioned there is something PQ does not do like dynamically assigning a name for a new merge. I wish I have asked earlier but I did learn a lot of PQ by trying to advance as much as I can. 
Today after I posted this, I did try to fetch the first merge outcome using #section the Table.Last see if I can take that new table and as another merge, as this way I try to avoid assigning it a new nane. Still I found hard to navigate new table merged and what is in Last. I kind of get back to Excel to automate there. If needed I may use VBA. 

But thanks, in deed!

[BA_Pete]

Hi @NewtoPQ25 ,

No problem, happy to help.

I would say that you should use the intrinsic identifiers (#section, #shared etc.) with extreme caution in Power Query. They aren't really designed to be used as part of a production transformation query and will fail refresh if you attempt to use them within PBI Service model/dataflow queries.

As I alluded to in my previous post, try looking into the List.Accumulate function in Power Query. It will perform 'loops' over lists etc. so might be able to do what I think you're asking within a single query. You would essentially feed it your parameter list and have it 'loop' over each value pair, performing a merge function on each iteration using the updated parameter pairs each time. I can't say at this point whether this will 100% do what you need, but it's where I would start looking if it were me.

List.Accumulate - PowerQuery M | Microsoft Learn

Also, don't forget to give a thumbs-up on any posts that have helped you along the way 🙂👍

Pete

[jgeddes]

If you can share some example data to show your logic and the desired outcome you will likely get an answer pretty quickly. I am not 100% clear on your request, but it sure sounds like Power Query is going to be able to do it quite easily.