You’re most welcome! I have a few new videos coming out soon including a dashboard webinar and a pivot table P&L video. Any specific requests beyond those?
This was the most in depth Power Query video I've come across. Sadly, I came across a few problems. When formatting the Date column to Date, rows 26 to 54 and again from 79 to 111 resulted in "error" in the Power Query view (I tried twice). Am I the only one this happened to? I assume that when editing the Add Conditional Column fields, you identified the individual Operating Expenses fields one by one as part of the excercise. IRL, to save time, you would have just identified a handful of COGSs and added everything else together in the Else field as Other Operating Expenses to save time, yes? 32:12 When I tried to classify as a text value, I got a warning "Insert Step" - Are you sure you want to insert a step? Inserting an intermediate step may affect subsequent steps, which could cause your query to break." Already assuming the error values in the date column might already cause problems and not wanting to cause any more, I clicked Cancel and hoped for the best. I clicked on Close and Load just to see what happened and the Statement sheet opened (58 errors). I assume that would be the missing dates (errors in design). Everything in the Type column was present and correct despite not classifying as text in design. So far, just the problem with the dates so I returned to design to continue. 34:50 This time, I received no warning when attempting to classify the Classification column as Text so I went ahead. I was also able to classify the Type column as Text without a warning this time. Curiouser and curiouser! As if things couldn't get any worse, VISA items are showing as Revenue in Type but Other Operating Expenses in Classification both in the Spreadsheet and the Query. And yes, I did go back into Design to check my work to make sure I had made no mistake. After adding the extra statement PDF into the folder and refreshing, I now have 157 rows loaded and 88 errors (missing dates). I've followed a number of Power Query videos and not experienced any problems. Though this was the first time importing from PDF. When I've got some time on my hands, I'll try again from the begining to see if I can find where I went wrong. If you have any insight, please let me know. Edit I tried a number of times from scratch and continually failed to achieve error free Date conversions. My final fix was to leave the Date column as Text and format the dates once in Excel view. However, I received no "Insert Step" warnings. Could this be a one off for the very first time only?!? I noticed changing to Currency in Design doesn't actually result in Currency format in Excel (not even in your video) as shown by no decimal points followed by 2 digits in all records. For example, in Currency format, even ignoring the lack of $ sign, -170 should read as -170.00 unless manually changed afterwards in Excel. I've just tried using my own downloaded bank statements which, despite being able to achieve (mostly) error free date conversions, caused additional problems because of our different accounting system (1 column for debits (money out), 1 column for credits (money in) and 1 column for balance). For some reason, this resulted in alternate groups of debits in the credits column and credits in the debits column. I think I'll stick to downloading my statements as csv and saving them as .xls adding a formula for the balance as a check that I downloaded them correctly.
Damn dude that was such a nice video. I've been building cashflow reports similar to what you presented, but not is such a smooth way. Specially my classification process, has been a bit manual through index match with a classification list on another table, so is not fully automated yet. Couldnt figure a way to automate it but u helped me a lot sir. Ty so much. Hope to see more videos
Thanks for the kind words and thanks for watching! I still have a lot of love for Index/Match but I’ll admit, I use it a lot less since learning about Power Query. Hope to see you at a future webinar!
The M in the M Formula language stays not for Microsoft, but rather “m”ashup language (hence the letter M / designed to create queries that mix together data).
We have an upcoming webinar on 2/29 where I'll be teaching how to build financial dashboards in combination with Power Query - register for free at www.analyzefinance.com/finance-dashboards-2/29