The internet hasn't been much help in answering this question, nor has a couple of power query classes I've taken. Is it possible to set up a power query, to take summarized data and manipulate it into a format to upload back into my system for Journal Entries. I would need it to dynamically filter by the current fiscal period I'm in, but also pull previous months, to verify the month total is 0, and if the next month has any data that would need to be posted.
So the story is, my company uses 2 different systems to keep track of all their data. I have to pull data from E1 sort out a bunch of object account numbers, manipulate it to give me a not only each account balance, but the total balance of all the accounts. I then bring that into a different excel file so I can put all the data into a a format that can be copy and pasted and posted into E1, to make it all 0 in that system. And then reformat that data into another table for the Journal Entry balance transfer into PDI.
Normally this is done by manual filtering, vlookups, text to columns, and lots and lots of copy and paste.
I know I might need a date query thing, for the whole dynamic filtering by current period. Also note, as we have no software to speed our processes, we do everything manually, so sometimes I'm making previous month transfers in the middle of the next month. So the query would need to take that into account, Hence I would need to be able to get 3 months of data to look at before the Journal Entries are created. And even after trimming an example down to no more than 20 rows the templates we have are way to big to actually upload. I can post a picture though of what the templates look like for reference.
Bookmarks