SOLVED

Power Query Understanding

Highlighted
Regular Contributor

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

11 Replies
Highlighted

@peteryac60 

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. 

Highlighted
Hello,

Kindly put all the three workbooks inside a folder. Then, open a blank Excel workbook and follow:
Data > Get Data > From Folder.. Then browse through the folder path and click on Combine.

The power query will be opened. Then you can perform required cleaning and transformation.

Cheers
Highlighted

@Sergei Baklan 

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!

 

peteryac60_1-1593026706892.png

 

 

Highlighted
Best Response confirmed by peteryac60 (Regular Contributor)
Solution

@peteryac60 

Peter, no, I mean Source step (very first one), usually that's default name for the first step.

image.png

If you click on it in formula bar will be something like

image.png

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.

Highlighted

@Sergei Baklan 

 

Sergei - Thanks for your help - I have managed to get this working!

 

Peter

Highlighted

@Abiola1 

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:

 

peteryac60_0-1593080168943.png

 

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:

 

peteryac60_1-1593080411981.png

 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

 

peteryac60_2-1593080524049.png

 

 

 

Highlighted

@peteryac60 

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

image.png

 

 

Highlighted

@Sergei Baklan 

 

Hi Sergei,

 

thanks very much for all this guidance. Much appreciated!

 

regards,

 

Peter

Highlighted
Power Query is a business intelligence tool available in Excel that allows you to import data from many different sources and then clean, transform and reshape your data as needed. It allows you to set up a query once and then reuse it with a simple refresh. It's also pretty powerful.
Highlighted

@peteryac60 , glad to help

Highlighted

@Lewis-H 

Based on questions I suspect Peter already knows that.