This is what I am trying to do:
1. Delete rows where the Account column does not contain data with a colon ":"
2. In the Account column the Account name needs to be moved to a new column
so that the account name and the transaction date are not in the same column.
3. Copy down the Account names in the new column
This is all I am trying to do but I cannot figure out how.
There are 4 columns: Account Description Memo Amount
and 823 rows. Each row represents a different transaction for that account.
Each account will have varying number of entries and the number of accounts can
vary from month to month.
If I can get this working I am hoping I just do a Refresh for each succeeding month.
Hopefully you can give me some sense of direction on how I should proceed.
Example .xlsx file attached.
Hi Dale,
No file attached. Click Start Upload after selecting the file.
Phil
Example Excel file attached.
Not sure I got it right, but looking at the Expense Data, the attached file contains a query that I would want to create before analysing the data. If this is not what you had in mind, please clarify the end result you expect.
Oh my gosh Riny! That is exactly what I want. And to think you solved my problem in just four steps. That is so great! I will study your solution so that I can apply it to other problems I will run across.
I was a bit taken back when I saw the date on your post. Then I noticed that you are in Sweden.
Thanks again. Dale
Hi, I have another request regarding your solution to my problem. Your M code solution works very well but it would take me years to acquire the knowledge to be able to write M code to solve similar problems. My request is can the commands on the Power Query ribbon be used to arrive at a similar solution instead of writing M code? I will start another Post if you feel that that is appropriate.
Thanks. Dale
I was actually going on auto-pilot here, doing what I always do without much further thought about alternative solutions.
The attached file now includes a "click-in-the-ribbon-only" solution, but it requires some extra steps.
In stead of using Add Custom column, I now used Add Conditional column. It allows you to build the if-then-else code by clicking in boxes. And as you see, it will produce the same M-code for you. However, my initial query required the try ...... otherwise part because I hadn't cleaned up the Date column first.
Would this work for you?
Hi Riny, thank you. It is exactly what I was looking for. I will review it some more and study your v2 solution. I know I will learn much and be able to apply it to my future projects. I am an Excel junky having used spreadsheets and VBA for a few years. It's just been this last year that I have started exploring Power Query and Pivot Tables. I watch all the myOnlineTrainingHub YouTube videos on Excel that I can find. They are very helpful. Thank you again for being so willing to help. It is truly appreciated.
Dale
Glad I could help. Keep learning!!
