Increment Variable in Power Query

[alsdkjfladjfgj12321]

I am using VBA to make connections in Power Query, put a file name + extension in a cell, and rename the cell (Query1, Query2, Query3....). The macro runs fine. The part that I can't figure out is how to increment the variable in Power Query. It remains Query1 for connections that are made. It is hard coded in. I want to run the VBA be able to have the M code in each connection have Query1, then loop again to get Query2.

I hope what I described is clear.

 

[alansidman]

Create a parameter query to enable a variable. Here is a link that will get you in the right direction.

There are others out there you may wish to google which are a bit more efficient, but this one will get you on the right track.

 

[alsdkjfladjfgj12321]

I watched the video. I'm not sure if that would work for what I am trying to do.

 

[alansidman]

Maybe this video will help you to understand what I believe you are trying to do. If this is not the case, then I urge you to post back with more explicit explanation of what you are attempting . By the way, sometimes guessing that it will or won't work will not get you a solution. Trying it sometimes gets you what you want. In the words of that great British poet. "If you try sometimes, you just might get what you need."

 

[alsdkjfladjfgj12321]

I am having multiple querys connected to an excel file. Those connections will combined into a large table. The issue that I have is that when I run my macro it hard codes come of the data like the location of file. I set up so I have the file name as a query.

What I have now is a variable so part of the file path changes if the folder is moved. The file name can change from one connection to another. I tried to make a list of the file names and have the cell names changed as an attempt, but can't seem to figure out how to have one connection have a variable of Query1, and the next be Query2 to pull from the cell.
There may be a better way of linking it to power query, but this way made sense.

 

[alansidman]

I am lost by your explanation. I have provided you with two ways to create parameter queries. If this is something you are unable to understand and apply or grasp, then I have nothing further to offer you. Good Luck with finding your way on this issue.

 

[peter789]

Maybe you need to look at the problem a different way. You seem to be importing the data from csv files. Have you tried placing all these files in a folder then using Import from folder rather than trying to bring them each in as separate queries then appending them?
Peter

 

[alsdkjfladjfgj12321]

Maybe you need to look at the problem a different way. You seem to be importing the data from csv files. Have you tried placing all these files in a folder then using Import from folder rather than trying to bring them each in as separate queries then appending them?
Peter

Would be able to append them if I do that?

 

[alsdkjfladjfgj12321]

I am lost by your explanation. I have provided you with two ways to create parameter queries. If this is something you are unable to understand and apply or grasp, then I have nothing further to offer you. Good Luck with finding your way on this issue.

Lost in what way?

 

[sandy666]

Would be able to append them if I do that?

I suggest to read

Power Query documentation - Power Query

Power Query is used to filter, combine, and mash up data from one or more supported data sources.

docs.microsoft.com