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
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