Jun 24 2020 11:08 AM
Hi
I am excel 2013 user and trying to get to grips with Power Query.
I have 3 files - say Jan, Feb and Mar in the same format.
I import the Jan file and in power query apply transformations as required and then close & load to Excel.
I want to now import the Feb file and apply exactly the same PQ steps to it. But I do not see how to do this. If I import Feb PQ just thinks it is a new file.
The issue that i have is my lack of understanding and I can't see how to make this work - it must be simple!
Many thanks
Peter
Jun 24 2020 11:25 AM
Peter, the simplest is to duplicate Jan query (right click menu), rename it to Feb one and in Source step in formula bar (or in Advance editor) change name of the queried file.
Jun 24 2020 11:29 AM
Jun 24 2020 12:27 PM
Thanks for your response.
I have managed to duplicate the Jan query and renamed it to Feb.
When you talk about the source step do you mean the Data Source Settings - see below?
I clicked on that option and changed the source to Feb but the Jan query also changed to Feb file. If I changed the Jan query to the Jan file then the Feb query changes to Jan file as well? Maybe this is not the option that you meant? Help please? Thanks!
Jun 24 2020 12:40 PM
SolutionPeter, no, I mean Source step (very first one), usually that's default name for the first step.
If you click on it in formula bar will be something like
Change file name here and Enter.
If you'd like to combine all files into one table that's what @Abiola1 suggested. But even if that's the purpose I'd suggest to play before with queries one by one, just for training purposes.
Jun 25 2020 03:11 AM
Jun 25 2020 03:26 AM
Hi
I tried this and got it to work - to some extent!
I started with Jan & Feb files in the folder and went through the process as you described. But I have a couple of questions please.
I am using CSV files and the first message i get is shown below:
I am not sure what to select here. In the drop down the names of the Jan & Feb files are there. Should I just accept the default and click OK?
When I did click OK it opens the query and I see the following:
My query is the Ch03 CSV files - I assume the others are just provided for completeness and I can ignore? Or can these be deleted?
And final 2 questions - I get the following. I assume column 1 can just be deleted as it is for my information? The TranDate is in US format. I tried to change it using Change Type ->Using Locale -> Date -> English (United States) but it appeared to make no difference.
Anything you can advise would be most helpful!
kind regards.
Peter
Jun 25 2020 07:41 AM
1) If all files have exactly the same structure it doesn't matter which file to select and default First file is okay. It could be a case when, for example, you have different number of columns in the files. When better to have the template with max number of columns and no data and use it as a sample.
2) Yes, Ch03 CSV Files usually shall be the only with which you continue to transform data if necessary. Other file are automatically generated and you may forget about them. However, that could be the cases when better to adjust them, but you have to quite good to understand what these files do. Otherwise you may crash the model. In any case, DO NOT DELETE them.
3) You may delete in above final query any column you don't need and add new columns if required.
4) Dates are shown in your locale format. If the source data have dates in another locale, you may use option to convert as you tried, that's the only case to use it. In most cases Power Query recognizes dates correctly. Just in case check your OS short date format and Power Query locale you use
Jun 25 2020 07:57 AM
Jun 25 2020 08:24 AM
Jun 25 2020 08:28 AM
Based on questions I suspect Peter already knows that.
Jun 24 2020 12:40 PM
SolutionPeter, no, I mean Source step (very first one), usually that's default name for the first step.
If you click on it in formula bar will be something like
Change file name here and Enter.
If you'd like to combine all files into one table that's what @Abiola1 suggested. But even if that's the purpose I'd suggest to play before with queries one by one, just for training purposes.