Dear All,
I am getting that data from a folder. Every month I will receive the latest file and I have applied few transformations and the output is in a table.
What I want is to change the month's name dynamically. Currently, the month's name appears in the below way.
"Apr-23" "May-23" and when I get June data, it will be "Jun-23" and so on.
What I want is to change these month's name dynamically upon refresh to the below:
"M1A" "M2A" and when I get June data, it should rename automatically to "M3A" and so on.
I have attached the picture for your reference.
I would really appreciate your help.
Perhaps the attached file helps you on your way. If not, please come back here.
Hi Riny,
Thank you very much for providing the solution. It is working for me. Now related to this I need one more favour.
Basically, I want to have 12 columns (M1A til M12A) in the power query. e.g.: please see the attached picture.
When I will receive the latest month's file, PQ will get the data for the folder and give the output as highlighted in green in the attachment but I want to also have blank columns in PQ for those months where I have not received the data. The way I highlighted in yellow in the attachment.
For example, June data I have not received it yet so there are blank columns for M3A until M12A. but when the data is received it should populate the M3A blank column with the data for June and keep (M4A til M12A) blank columns. It should do exactly the same for M4 onwards.
Also when there is no data it should have 12 blank columns only.
Your help would be appreciated.
You asked the same (or at least a very similar) question before and you mentioned that you had resolved the problem "in another way". What has changed?
Hi Riny,
Previously I manually added a row with these headings (M1A, M2A...so on) A2:L2. Via PQ I was loading the table under these headings in A3:B3 (due to having only two months of data). The problem was I now have two rows of headings, one at A2:L2 (added manually) and another at A3:B3 (added via PQ). That is causing a problem when I want to do another analysis through this data source. Yesterday when I changed the name from "Apr-23" to "M1A" (Thanks to you). I thought if blank columns can be created via PQ and data is loaded into relevant columns that would be a much better strategy and I would not have two different rows of headings.
I would appreciate if you could help.
OK but can you share a file? I find it hard to visualize what exactly you are doing.
Thank you Riny.
Please see the attached sample file. The first tab is the actual data file, which I will be receiving on a monthly basis. The second tab is the output I want via PQ as a table.
Your help would be appreciated.
Thanks for the file but I can't refresh the queries. Table1 seem not to be what PQ wants and Table2 does not exist.
Perhaps you can recreate a file where all data tables and queries are included in the file?
Hi Riny,
Please see the attahced update file.
See attached. Would that work for you?
Thank you so much, Riny. It is working now.
I really appreciate the time and effort you have put into resolving my queries.
Glad I could help!
