Power Query data input+changed columns in original file

[Villee]

Hi,

Im a beginner with Power Query. So I started to build my query without thinking ahead too much and made a small mess. I think the answer should be easy but after spending a day trying different options and reading help topics I couldn't figure it out.

Problem one:

I have an Excel file with calculations "File A" in location: Desktop\FolderA\FileA.xls

This has normal Excel links in formulas to another workbook/excelfile where I have data, "File B", which contains customer data in various columns and is located in Desktop\FolderB\FileB.xls.

I have created a Power Query using this File B and done 16 Power Steps.

When I get a new client I need to download a new File C run the query which is in File B.

As I understood the Query is saved in the original FileB so how do I "export" the query from the old file and import it to the new File C?

I have managed to "change source" in the File B Query to the new File C which does update the query but the data is then saved in the original File B and not in a new File C.

I would like to have both files saved for future use.

So after you helped me with problem 1 when the data is in File C and updated =>

Problem nr 2.

How do I update the normal excel links when I have done the query update, I do not want the links to point to File B anymore but the the new file? At the moment the excel links are using the whole file path to File B until I refresh the data, after I have refreshed the data it only shows up the filename and not the path.

So basically after I have managed to do my query in File C I want to point my calculation here.


Problem nr 3.

After building the query and being really happy about the results after 16 steps. I decided to make a small change in the inputfile.

I used to have a column "FirstAndLastName" which I decided to delete and insert two new columns "FirstName" and "LastName" so the column order and column names changed. As I have understood the column order/names have to be the same to refresh data in a Query and I also managed to use a new file as a source at some point and of course it gave an error.

So I have manipulated the original "FirstAndLastName" column multiple times in the first steps of my Query so I can't just back every step and do it all over again.

Is there any way to make the query work with the new column order? I tried to look at the M-Code and do some adjustments about names but it only resulted in error messages.

Also, everytime I get a new client the input file adds rows in the top with the new clients. Will also this mess up the query??


Thanks a lot with all the help, I really need it!
/Will

 

[teylyn]

Hi,

re Problem one
Power Query gets data from sources inside and outside the current workbook. It can store the data it has transformed, but only inside the current workbook.

So, FileA links to FileB. Fine. Now you want to transform data in FileC and save that data in FileC? Does that mean that this will not be linked to FileA then? Files A and B don't have any connection to File C?
If FileB and FileC are identical in their structure, you can copy the queries from one file to the other. If you have several different queries in FileB, that is quite tedious. I would use a different approach like this:

Create a new FileD. Write the path to the desired file (FileB or FileC) into a worksheet cell. Use that path and file name to load the data from the specified file, transform it, clean it up and store it in FileD.

re Problem two
Ahh, here comes FileA. Link FileA to FileD.

re Problem three
Edit the steps and correct the column names in each step. You don't need to write M code. Just click the actions, starting with the Source. If an action throws an error, click the settings wheel next to the action step or use the fomula bar (you ARE displaying the formula bar, right?) to correct what's wrong. It's a lot easier in the formula bar than in the Advanced Editor. If push comes to shove, delete the wrong step and add it again at the same position, using the same name for the step.

re this: "Also, everytime I get a new client the input file adds rows in the top with the new clients. Will also this mess up the query?? "

Not sure what you mean. If the file has some rows of stuff before the actual data starts, your first action step would be to delete these rows.

 

[sandy666]

duplicate query then change path in the second

post your m-code
or better post all excel files (zipped)

 

[Villee]

Thanks for great replies! I think I kind of solved it but not sure if in a smart way...

I opened File B and opened Power Query and gave File A as source for a Query. When I originally did the query, the results of the query were saved in a new Sheet1.

So when I did the above I didn't select all sheets "to import" but only "Sheet1".

I got all the info I wanted and refreshed but I lost all the query steps! So now I have my File B Queried in the way I wanted and the data is good but I have no Steps in File B.

Maybe this is not a problem but if I at some point want to see all the steps I have taken to reach to this point it would be difficult.

After I solved Problem 1 in this way I could just use "Edit link" in Excel and problem 2 was solved really quickly.

I haven't had the energy to validate the solution for Problem 3 also partly depending on the way first problem was solved (I don't have any steps to edit!).

Maybe it's just easier to do a VBA macro to once again merge the first/lastname columns before I do the above query or do a second query to merge them...

Feels like Power Query is a great tool but quite counter intuitive in some cases.

Can't really upload the files here since they hold customer data and it's a mess to try to anonymize it.

 

[teylyn]

I opened File B and opened Power Query and gave File A as source for a Query. When I originally did the query, the results of the query were saved in a new Sheet1.

So when I did the above I didn't select all sheets "to import" but only "Sheet1".

I got all the info I wanted and refreshed but I lost all the query steps! So now I have my File B Queried in the way I wanted and the data is good but I have no Steps in File B.

You've lost me. Is File A here the same as File A in your initial question? I though that File A has links to File B. Now you say it's the source. That's confusing.

And what import are you talking about that you do after the query? What do you import from where to where?

Power Query is really easy to use. The query stores the data in the same file as the query. If you save the file with the query, you will have the query steps, unless you manually delete the query and click OK on a few dialogs that prompt you if you really want to do this.

You can use the query again and again. If you take steps to parametrize the query, you can point to different sources for the data. All the time the query and the query results stay in that file.

You can then use other files that access the query file to pull information via formulas. Or even use the query file as a data source for other queries.

The modelling may take a bit of planning if you have several files in the mix, but it is waaaay easier to combine a dozen files with Power Query than with VBA.