Use Power Query to Load and Transform Excel File from K Drive

[justlearning4]

Hello,

What I am attempting to get help with is creating an automated power query (as in using a macro button to initiate process) that loads and transforms the source file into a usable and repeatable format. Below, I have attached a picture of the source file so that you can see what it looks like. Side note, I would like the macro/power query, I am still learning the difference, to be stored in the same excel file.

Ideally, this power query would be able to be repeated on a weekly basis, and produce a similar output consistently. To start, the file directory is "K:\Investments\Municipal Credit\IPREO Calendar" - this directory would have to be accessed in order to source the file. Secondly, as can be seen in the picture attached, there is data in columns A-T; however, like I mentioned in the beginning, not all of these are relevant to my analysis. So, in order to perform this analysis, the power query would need to remove the following columns: "Time", "BP", "Int Cost", "Status", "Type", and "ERP". In addition, the query should be able to organize the remaining columns in the following order: "Date", "Amt MM", "Issuer Description", "Security Type", "State", "Ultimate Borrower", "Moody's", "S&P", "Fitch", "Enhanced", and "Tax".

This is not necessary, but if someone is advantageous, I would greatly appreciate it. In the second picture attached is a specific color format that I would like the transformed data to look like. For reference, the blue column headers is the standard dark blue color that is in the dropdown menu for "fill color". In addition, the grey that is filled in the cells in an alternating format has a hex code of #F2F2F2. Like I said, not necessary but it would be really nice.

That is all.

For the person who does this, I will will be extremely grateful and impressed by the technological skills, as I am just a silly finance bro who is learning VBA, Python, and R for the very first time.

Thank you so much for your time.

 

[Skovgaard]

Hi,

If I understand correctly, it sounds like an easy job for Power Query.
A few questions:
- Have you used Power Query before?
- In few words what you want: Remove some columns an re-order them, correct?
- Is it always the same source file, is it overwritten time to time, a new name in that location, or several files in that directory?

/Skovgaard

 

[justlearning4]

Hello,

1. This is my first time really using Power Query - I would say I have two weeks of experience, but I am learning fast.
2. Removing and re-ordering columns is exactly what needs to be done.
3. It is technically overwritten each time with newer data, and a new excel workbook name of "CDRN MM.DD.YY" <- for example, "CDRN 11.14.22","CDRN 11.07.22"

a. However, the file directory remains the same​

Does that help?

 

[Skovgaard]

Hello,

1. This is my first time really using Power Query - I would say I have two weeks of experience, but I am learning fast.
2. Removing and re-ordering columns is exactly what needs to be done.
3. It is technically overwritten each time with newer data, and a new excel workbook name of "CDRN MM.DD.YY" <- for example, "CDRN 11.14.22","CDRN 11.07.22"

a. However, the file directory remains the same​

Does that help?

OK, if the filename changes I suggest you do the following:

1. Data -> Get Data -> From File -> From Folder (the folder should only have this one file in it)
2. Select the file folder location and press "Transform Data"
3. You should now have something like below picture
- Press the double-arrows from Content Column
- If you source has a table in, you can choose this of it should pick the data from a specific sheet. Choose and press OK
4. Now (hopefully) you see all the data in Power Query
5. Select all the columns you want to delete. Right-click and press "Remove Columns"
6. Re-arrange you columns by pulling them with your mouse
7. Press Ikon "Close & Load" -> Close and Load to...
8. Choose a table and where you want it placed
9. From now on, you just replace the file in your folder, and press "Data" -> Refresh all to update your table.

/Skovgaard

 

[justlearning4]

OK, if the filename changes I suggest you do the following:

1. Data -> Get Data -> From File -> From Folder (the folder should only have this one file in it)
2. Select the file folder location and press "Transform Data"
3. You should now have something like below picture
- Press the double-arrows from Content Column
- If you source has a table in, you can choose this of it should pick the data from a specific sheet. Choose and press OK
4. Now (hopefully) you see all the data in Power Query
5. Select all the columns you want to delete. Right-click and press "Remove Columns"
6. Re-arrange you columns by pulling them with your mouse
7. Press Ikon "Close & Load" -> Close and Load to...
8. Choose a table and where you want it placed
9. From now on, you just replace the file in your folder, and press "Data" -> Refresh all to update your table.


View attachment 78992

/Skovgaard

Great, thank you for the comment back, and for the explicit detail!

 

[justlearning4]

OK, if the filename changes I suggest you do the following:

1. Data -> Get Data -> From File -> From Folder (the folder should only have this one file in it)
2. Select the file folder location and press "Transform Data"
3. You should now have something like below picture
- Press the double-arrows from Content Column
- If you source has a table in, you can choose this of it should pick the data from a specific sheet. Choose and press OK
4. Now (hopefully) you see all the data in Power Query
5. Select all the columns you want to delete. Right-click and press "Remove Columns"
6. Re-arrange you columns by pulling them with your mouse
7. Press Ikon "Close & Load" -> Close and Load to...
8. Choose a table and where you want it placed
9. From now on, you just replace the file in your folder, and press "Data" -> Refresh all to update your table.


View attachment 78992

/Skovgaard

Will this work to have the same formatting each time?

 

[Skovgaard]

Will this work to have the same formatting each time?

Power Query will record your steps, like the macro recorder, and do all these steps each time you refresh.

/Skovgaard