Can PQ have dynamic table names

[Kabous07]

Good day

Im looking for assistance with regards to a automated step in Power Queries, specifically the "append" function. I have many sheets created from a template sheet and each sheet will have a different name. Inside each sheet there is the exact same table, but unique data. I must create a summary table of all these sheets, but also each time i create a new sheet using a copy of the template and renaming it to, lets say PT####. This means i have many PT number sheets(ie project sheets) and i would like to summarize our projects into one.

I tried using Macro record, while creating the table connection(query) and then append it to the "Summary" sheet, but im limited to the table name and sheet name that changes each time.

Looking forward to some assistance please

Edit: I have a post as well on another forum...hoping that someone can assist me sooner, but a needy hand must be patient. Apologies.Cant post the link as i need to get more experience with this forum

[bluesky63]

Hi,

it will be good that you upload a mockup of your worksheets so that folks in this forum will have a clearer pictures of what sort of "Dynamic" solution that you would want to achieve, dynamic parameter is one of the way, or you are table about each table name that always changed by users, however when you bring into PQ, they will automatically name in as Table1, Table2, Table3 and so on.

Rgds

[MarvinP]

Hi Kabous,

It sounds like you want to import all the files in a single folder option in PQ. I'm only finding examples of .csv files but Excel files are also supported.
https://exceloffthegrid.com/power-qu...s-in-a-folder/
better example on:
https://support.microsoft.com/en-us/...en%20workbooks.

[Kabous07]

Hi Marvin

All of the tables and sheets would be in one workbook. It would be to difficult to explain to all my colleagues how change file paths if they have different folders. So we are trying to keep all the information on one workbook.

[AliGW]

Which other forum did you post on? You can post the link with gaps in it. Please do so.

[Kabous07]

Which other forum did you post on? You can post the link with gaps in it. Please do so.

I cant seem to post links, it give me a error saying i must first reply/post more to insert a link. The other Forum is MrExcel

[AliGW]

Thank you.

If all the tables are in one workbook, you can use the ExcelWorkbook.Current() command in a blank query to append them.

[Kabous07]

Do you think it would be possible to automate?

Its easy to create a connection manually and append manually. But to automate the query connections and append will variate on the table name and sheet name that gets added in the workbook. All of the information will be on one workbook.

I could probably do tsomething different like record a marco button where i copy and insert the data in the summary table(in summary sheet), but then the data wont update when edited in the project sheets.

[AliGW]

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

[Kabous07]

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

Thank you for replying. I think im frustrating the system here, because i have already posted in another forum. Would you rather recommend that i remove one of the threads. This way i dont waste anyone time.

[alansidman]

I would not recommend you delete any, but rather as Ali has suggested, type out the link manually so that all can see any progress and not duplicate the efforts. Additionally, upload a sample of your data after reading the yellow banner at the top of this page telling you how to do that. We are here to help, but you must assist us with these requests.

[Kabous07]

I would not recommend you delete any, but rather as Ali has suggested, type out the link manually so that all can see any progress and not duplicate the efforts. Additionally, upload a sample of your data after reading the yellow banner at the top of this page telling you how to do that. We are here to help, but you must assist us with these requests.

Sure thing and well noted thank you.

I would like to add a link but i get a error : "You are not allowed to post any kinds of links, images or videos until you post a few times."

I have finished the excel sample accordingly and think i might have uploaded it correctly, hopefully.

I have removed the first part of the link, the post is in MrExcel. /board/threads/vba-power-query-connection.1141588/

[alansidman]

I think that the tutorial below should do exactly what you need.

https://trumpexcel.com/combine-data-...ple-workbooks/

Once in place, any new files added to the folder will automatically append when you click on the refresh Icon.

[Kabous07]

I think that the tutorial below should do exactly what you need.



Once in place, any new files added to the folder will automatically append when you click on the refresh Icon.

Thank you Alan and AliGW. I will study this and will make it work.

[MarvinP]

Hi Kabous,

See if this YouTube video does what you are asking for:
https://www.youtube.com/watch?v=FI4HkCzMaIk

[Kabous07]

[QUOTE=MarvinP;5373502]Hi Kabous,

See if this YouTube video does what you are asking for:
w.youtube.com/watch?v=FI4HkCzMaIk

Thank you, what perfect timing of her! Was glad to come across the video on youtube and also seen you posting it. Just what I was looking for.