10.24.23 - Downloading and Working with QBO Data with Power Query

Michelle and Dan are joined by Tech Author and CPA David Ringstrom who has developed a process of downloading QuickBooks Online Data as local files, and, by using Power Queries in Excel to access and work with that data locally.

QB Power Hour is a free, biweekly webinar series for accountants, ProAdvisors, CPAs, bookkeepers and QuickBooks consultants presented by Michelle Long, CPA and Dan DeLong who are very passionate about the industry, QuickBooks and apps that integrate with QuickBooks.

Watch or listen to all of the QB Power Hours at https://www.qbpowerhour.com/blog

Register for upcoming webinars at https://www.qbpowerhour.com/

Transcript

Michelle Long: 0:30

Thanks everybody for joining us today and glad to have you all joining us. Welcome for downloading and working with QBO data with Power Query. Very glad to have you guys all joining us today. We got some great stuff for you today. We are going to be never mind. My name is Michelle Long, owner of Long for Success, speaker for Intuit, author of several different books. But that's enough about me. Go ahead, Dan.

Dan DeLong: 0:55

Hi, my name is Dan DeLong, owner at Danwidth, worked at Intuit for nearly 18 years, co hosting today also at Workshop Wednesdays over at School of Bookkeeping. And for the past few, four, four or five years, I've been doing the tech editing for the QBO for Dummies series and glad to having David joining us today, who's the author of the book. So it's really nice, full circle type of situation here. I was just throwing out the ideas, Hey, you should come on the QB power hour and talk about how weird it is to The right books about technical stuff, but he actually came up with a greater idea of talking about an idea that he discovered about downloading data from QBO and on top of that. So we'll chat a little bit about with David, but we'll also he, he has some Excel the wizardry of Excel to talk about.

David Ringstrom: 1:47

Hello. I'm David Ringstrom. I'm a CPA in Atlanta. I am a entirely non traditional CPA. I've never actually worked in public accounting back in the day. Maybe you still can, but back in the day in Georgia, if you worked under a CPA for five years, you could meet the experience requirements. And I had a boss who kept telling me I should sit for the exam and I've been eternally grateful to him for that. So My background is I've been a spreadsheet consultant since the early 90s, and over the years that parlayed into writing articles about Excel. I did that for about 20 years and kept my eye on the prize. I started doing technical editing, which Dan does as well. Dan has greatly improved my QuickBooks for Dummies series. I took over that four versions ago. And Dan has been the technical editor for all four of those. It's a blessing and a curse when you take over the texts from a previous author. It's nice to not start from zero, but looking back, I probably would have rather started with zero because it has taken me four versions to really fully get the material organized into a fashion that I think is. really now the way to talk about QuickBooks Online. So that fourth edition's coming out November 22nd. If you bought previous editions of QuickBooks Online, thank you for that. The, this newest edition is definitely my best work. I'm always working to improve things. I also wrote a book last year exploring Microsoft Excel's hidden treasures, which, so that was a bucket list goal for my career. For accountants, for college students and then have online classes. So That's me in a nutshell,

Dan DeLong: 3:29

When you took over the authorship of the QBO for Dummies the ability or the chapters that you included about Excel were like, Oh my goodness. I can't do this. This is the technical editor for that because one I'm using, Mac versus versus a PC to do that follow those steps. And it was like it was not a dummy level version of what you were doing in Excel. So I really appreciate you coming on and sharing your wisdom of the things that you can do in Excel. So if you do purchase the book. There are some great chapters in there of value adds of using Excel in QuickBooks Online directly, but you have some other nuggets to discuss about today so a little bit of the details about the QB Power Hour webinar series every other Tuesday at 12 Eastern assuming we start on time but not eligible for CPE credit. But check the website for upcoming events. We also have the PDFs, the slides, recording, podcasts, and other resources at QBPowerHour. com slash resource a little bit of the housekeeping if you have specific questions for David or Michelle or I myself as we go through this particular content. Of course, they're all going to be redirected to David because he's the expert on the material that he's delivering here today. Please put them in the Q& A because that will also Make it a lot easier for us to follow up if if it's something that we at the table or put in a parking lot. Any kind of comments please put those in the chat. But again, try to direct questions into the Q& A section. We also have the links as well as for the webinar archive as well. And we will post the the handouts link in the chat. So everybody has access to that if you haven't got to them already. So our agenda here today is to chat with David some of the challenges about authoring technical books and then an introduction to the power query. So we have a lot of power that we're talking here on the power hour, the power query and the QB power hour, and then linking QBO data to Excel by the way of how, of that power query. So David, we talked a little bit about your your history. How did you get, like, where did Excel come into the picture for you, or is it just because of the nature of accounting and that's really where things started for you?

David Ringstrom: 5:48

I feel like I sealed my fate back in the late 80s. The first spreadsheet I ever saw was Lotus 1 2 3, and it was the DOS version. And I will never forget that day. I sat down in front of the computer. I pulled up Lotus 1 2 3. And in the DOS version, you had to hit the slash key to get a menu. So all I saw on the screen was just this grid that you arrowed around. And I said, this is a stupid program. You can't do anything with it. And so Karma said, okay. And Ultimately, I've moved into working commercial real estate, and one of the things that really, sealed, my fate on wanting to become better adept at spreadsheets was, I was working as a bookkeeper back in the day when if we printed, if the computer system printed the tenant bills, if they were retype them on a typewriter. Which obviously is very tedious. So I figured out how to create a spreadsheet that I could put on the screen and check my work and then print it on that, that dot matrix printer that would do the carbon copies. And from there, I was hooked and I just became the in house spreadsheet guru. Parlayed that into a consulting career. I left my day job in 1998, so it's been 25 years being self employed

Dan DeLong: 7:02

now. Yeah, I I remember a a specific coworker added to it. He was. He's if you can imagine it, you can do it in Excel. I'm like, Oh my goodness, that is too much power.

David Ringstrom: 7:17

It's a lot of power. And most folks barely get even a 1 percent of it. Decades in, I'm still learning stuff about Excel, which I love that. I love that there's always more new stuff to learn and do. And a lot of my career has spent a lot of time writing programming code to automate things. And what the beauty of Power Query is that you're creating automation without writing any code, and you can set up these set and forget connections to your QuickBooks reports because QuickBooks in particular does all kinds of jacked up things to the data when they send it to Excel. Fortunately, the new enhanced report format, I guess they're calling it, does let you export to a CSV format, and that eliminates all the weird formatting that they do. But for a while, they've since fixed that, but when you exported a report from that enhanced format, your dollar amounts were text fields. You can't do any math with them because they were text, you have to stop and clean them up, which you can do that with Power Query. When they send dates over, they are still treated as text, which means you can't then do date based things. And Power Query lets you set up these connections, you just keep overriding the same download file, and your cleaned up data pops into your spreadsheet.

Dan DeLong: 8:32

Nice. Now what what challenges have you seen? Besides the obvious of authoring books based on technical technical issues, like, where has that what has your been, what has your experiences been? And Michelle you could probably add in your your insights as well. But what have you what have you experienced about that? I always thought it was just weird when somebody approached me and said, Hey, would you like to technical edit a an online version of QuickBooks? I'm like it's going to be outdated before, before it's even published. How has that been? How has that experience been

David Ringstrom: 9:07

for you? It was particularly frustrating with this edition that I wrote over the summer because Intuit would turn features on and off. And I would write about it in an early draft and then came back around to the author review. It was completely different. And it's pick a lane, and Excel is somewhat getting to that space too, because if you're using Microsoft 365, it's a subscription platform so they can just stream stuff out. Those of us have been using computers for a long time. Back in the day, the software was the software. It didn't change. Now in the cloud, all bets are off. You log in a new day and ta da, here's particularly it was the invoicing, they had rolled out the new invoicing screens, and then they took them away and then they put them back. And that's a significant part of the book. And, then they also started relearning the menu which that needed to be changed that whole business view versus accountant view, trying to write a book about, guessing which menus it's going to be using.

Michelle Long: 10:15

And that was some of the challenge that we had back in the old days when I was writing with the desktop as well with toggling into the different versions. Trying to explain that you could be into the premier contractor version, the premier non profit version or manufacturing or, because you have the different versions of premier or enterprise. And, so it is a challenge, when you're dealing with different versions and additions and then especially with online. When you're getting these updates every six weeks or so, and that is extremely challenging.

David Ringstrom: 10:48

And that's where it took me four tries at the QuickBooks Nonline for Dummies to finally crack the code because the previous author used QuickBooks Advanced and basically started from the biggest version and wrote down. And so I continued that, but then this year I was like, but there was a lot of, reviews, like people were having a hard time finding what they needed, partly because they're having to read through stuff they can't do. And so my breakthrough this year was the first seven chapters are everything that you can do in any version of QuickBooks. From Simple Startup. I love that, yeah. And then two chapters on Essentials, what that adds, and then a couple chapters on Plus, what that adds, and then Advanced. And suddenly, it's oh, okay, you can go directly

Michelle Long: 11:31

to what you need. And that's a great approach so you don't overwhelm people with a bunch of stuff that they're not using. And I think, especially for a dummy book, that's perfect. Don't show me all this stuff that I'm not using, don't compare me with things that I'm not using. So I love that approach.

David Ringstrom: 11:46

Thank you. And the other author had one of the things that cleaned up in one of the early iterations, what I felt like this choose your adventure, she'd start talking on a topic and say, if you want to read more about it, go to chapter X. And I'm like that's, just bring it all together. So last year I brought it all together, but it wasn't version specific in the version specific that's going to change everything with the book.

Dan DeLong: 12:09

Yeah, how does that one of the things that I think is helpful about, books about technical stuff is that you can just, if you want to nugget, like, how do you go to just that area? And how do you plan that out in the book itself? Is that something that you've been, like, is that you weigh that, weigh those options when you're trying to think of what would what would the reader want to get out of this book, or is it really, I need you to read it cover to cover in order to get the most out of it?

David Ringstrom: 12:37

No, one, there's no way anybody's gonna read a book like that from cover to cover. They're just, they want to get in and get out. And so I tried to put myself in the reader's chair. What would I want to learn? That was new to the software. And it's through that. That's where I finally have an epiphany about let's segregate the content by subscription level. So that way, one, you're, if you have a lower level, you're not, you just want to read the chapters about plus in advance. Because it's not applicable and it just sometimes it just takes more iterations than you, the previous author, her writing style was different than mine she was fond of putting the second half of in front of the Senate in front of the first half, which, in my view, creates kind of some stilted writing and then it's already accounting software and when your Senate structure is. Difficult. So it took me a couple of versions to clean that up. And then last year, I kind of group things together, but that wasn't quite, the sweet spot. Now it's in the sweet spot

Dan DeLong: 13:42

and you've found a way to inject humor into an accounting technical book, which is which I applaud you for doing that. All right. So let's let's do a So I'm going to launch a poll and I'm going to stop sharing so that David can take over. And where did, oh, now the polls went somewhere. Where are they? There. All right. What version of QuickBooks do you use and support in the different flavors as we were talking about? That sort of thing of, the different flavors of QuickBooks online as well as desktop is something that you can put multiple answers to looks like plus is is leading the

David Ringstrom: 14:25

charge there. Yeah.

Michelle Long: 14:29

Go ahead. Swinging back around to Excel and talking about Excel and all that and, you were talking about how powerful it is and how much it can do and everything. I think one of the challenges, cause I was looking at it and I was looking at your slides and going over it and thank you for that and everything. And I was like, Oh yeah, I forgot about that. Oh yeah. And I think that's one of the things is a lot of us, that are of a certain experience level. I'm not going to say old, but then again, it a lot of us, we learned excel, and then what happened is they kept adding all these great new very powerful features onto it. We may not have ever learned that, and if we didn't have a need for it, come up. We just maybe it, we missed it. You know what I mean? Yes, completely. So I think there's a lot of very powerful features there that a lot of us just missed because we were busy working and just overlooked it. So I think there's a lot of powerful stuff that we just overlooked and missed throughout the years. You know what I mean?

David Ringstrom: 15:33

That's what I teach in my Excel webinars, and I'll be honest with Power Query. I knew it was out there for a few years and I was like, why do I need to learn that I have all these other ways of doing stuff. Yeah, there is. There's always a price of admission to these new features. My goal is to hopefully, demystify and at least give a little running start on Power Query. I can't get very deep today, but the automation that you can do with Power Query without writing any code and then it's just a sit and forget is truly amazing. Particularly for transforming reports, if we had more time, I'd show you because another thing that's frustrating about QuickBooks is there will be a piece of data that's on a different report and you just can't add that column, and so I have long described QuickBooks is being feeling like that proverbial monkey. There's a banana in the jar and he can reach in. He can feel the banana, but he can't get his hand in the banana at the same time. Data in QuickBooks feels like that. It's like it's, It's stuck.

Michelle Long: 16:36

No, it's there,

Dan DeLong: 16:40

but you just can't get it right. I want that banana. Yeah, so it looks like I'm sharing the results here. It looks like plus is is the most commonly used version of QuickBooks online. And then I with essentials and desktop. Now, some of the things that you can do in that you're going to talk about with power query, you can do the same thing in desktop. Yes. Or online, right?

David Ringstrom: 16:59

Correct. I do want to lead off with kind of a treat for QuickBooks Advanced users. This only works in the Advanced subscription, but there's a lot of us attending today that are using that. If you click the gear button at the top left hand, the settings button at the top left, right hand corner. Thank you. And then choose backup company. So you're not going to see backup company in the lesser subscriptions, but in advanced you will. And then step three, if you get a local backup, because one, QuickBooks Online Advanced lets you create backups that stay in the cloud. You can also create a local backup. And the first time that you click on local backup, it's going to ask you to link your Dropbox and or Google Drive account. Works with either one. And once you've done that linking, you then get a button that says I want a new local backup. And so you choose your advanced company. You choose whether you want to put it in Dropbox or Google drive, you click create new local backup. And what happens, I'm going to pull over a folder over here. After a few minutes, a zip file shows up on your computer.

Dan DeLong: 18:08

I'm not seeing that there. Yeah, I know it's not showing up. It's on the other screen. Yeah, come on. It's coming along. Pull it in.

David Ringstrom: 18:19

There we go. If I go to my Dropbox, which for me, a method to drive X just to make it easier. So in my Dropbox account, you're going to find That you're going to have an apps folder that gets added. So it gets added automatically. And then within apps, I guess Krono Books is the company, the that D that does this facilitation. So inside Krono books, you'll then see a folder for each company that you create a local backup for. So this is the company that I use to write the book from. So if I go in there, created a couple different backups. And these are zip files that when you open the zip file, you're going to see you basically get the keys to the kingdom. These are going to be CSV files of all your QuickBooks data, accounts, payments, bills, classes, departments, 22 different files, and you can. Use Power Query to connect to the zip file. So on this page here, talking about Mark White's BI blog, he shows how you can basically copy and paste some stuff into Power Query and you connect to the zip file. And then in Power Query, you see there's 22 different CSV files, and any of those you can then dump over into Excel. So for advanced users, rather than having to get data out one at a time. So this is going to be a snapshot, obviously you have new transactions, you have to create a new local backup, but that's just the glimpse of what's possible there.

Dan DeLong: 19:56

So that, that allows, people who, are focused primarily on, QuickBooks online to, to work with the work with their date, their clients data. Without the need for desktop or make some kind of closed, make some kind of local. Working in the, where they're not in the file itself, maybe making some changes or running some power queries as the name, or

David Ringstrom: 20:20

even you could assemble a dashboard based on that would, present all kinds of data. That, that,

Dan DeLong: 20:28

When you address this this was the, like the mind blowing, aspect of what you can really do with Excel and in, in conjunction with QuickBooks Online.

David Ringstrom: 20:38

What was that?

Dan DeLong: 20:40

When you when you presented this this topic this part was like the mind blowing part for me of what you can do with Excel and QuickBooks online.

David Ringstrom: 20:48

Yes. No, it's, and when I took over the book, there was no chapters on Excel and I'm a firm believer that any accounting software book needs to have chapters on Excel because to me, otherwise it's like teaching forestry without talking about chainsaws. Excel is such a key tool because you're always going to need to pull that data out and do something with it. And so most folks are going through the file menu to access their files. Anytime you get through the file menu, that's a manual set of tasks. We're going to be going through the data menu and that's where the automation lies in Excel. But let's say you just let me show.

Michelle Long: 21:27

I'm sorry. I was just going to say people didn't understand this and you said something really quickly. That was really powerful. So I was going to say, can you explain again what you meant? And you said something. You said you could even create a dashboard and you said it really quickly. That was a very valuable nugget and I don't want it to get lost and a lot of people said, I'm not understanding. Can you explain this again? So could you, this is great information. So can you say it again slower for dummies that are listening to you? We, this is great stuff. So can you. Can you go over it again and explain that nugget that you said, the dashboard, because this is

22:11

great

David Ringstrom: 22:11

stuff. All right. So let me do this. So with Power Query, I could attach directly to the zip file, but we don't have time for me to get real deep. So I'm just going to cheat a little bit here and export.

Michelle Long: 22:25

We've backed up our QBO, and these are all the folders, or all the files that it made for us when we backed up

David Ringstrom: 22:31

QBO. Correct. So I'm just, I'm exporting the... Extracting

Michelle Long: 22:38

the compressed

David Ringstrom: 22:38

files. Exporting the files to my desktop, hopefully.

Michelle Long: 22:49

Yeah, and so some of the people who are asking the backup is included with the QBO advanced, but you can get those third party apps like rewind and some other ones that you can add on. You have to pay for those though, right? Available for the plus point.

David Ringstrom: 23:03

The beauty of this is that it normally QuickBooks is so stingy about letting you access your data. It has gotten a little bit better with spreadsheet sync. That again requires the advanced subscription, but To have kind of a way to just dump all your data out to CSV files. So now that I've created those files, I'm going to go over to Excel. And how long does it

Dan DeLong: 23:29

usually typically take for a backup to complete? Of course, it's going to depend on how much data needs that needs to happen. But in my

David Ringstrom: 23:37

case, it was a few minutes that Excel load. Okay, so here's Excel. So when we want to get data. into Power Query. We're going to go to our data menu. If we're using Excel 2019 or later, we have a Get Data command. Just for benefit of folks using Excel 2016, to the right a little bit, there's a New Query command that's used instead, so they change the name of the button. So it's either New Query or Get Data, but here I'm going to choose Get Data, From File, and then because these are CSV files, I'm going to say From Text slash CSV. And it's going to be an import data screen where I'll be able to pick my text file through that on my desktop. And so we're going to grab the there was an invoice file. So I'm going to grab invoices. And what's going to launch on the screen here is what's called the Power Query Editor. Actually it gives you a preview first. So if I were to click load, that would just take this data and just load it to the spreadsheet directly. But I want to, we can see there's a whole bunch of files here, a whole bunch of columns. I don't need all those columns. So I'm going to click transform data, because one thing power query lets you do is pick and choose the data that comes in. And you can do this with Excel reports that you dump out of QuickBooks. So sometimes the reports don't give you an option, they're like you're getting these 20 columns, whether you want them or not. Power query can go in and go into choose columns. And you can turn the fields on and off that you want. So like I don't need invoice ID or updated app.

Dan DeLong: 25:50

So is there any columns or fields that are, that, that are missing that you found in when it does this mass export through the backup of the data, or is there things that you can't do? Cause somebody was mentioning that spreadsheets stink. Misses so many fields, right? Like it, you can, and that's like the challenge of using the spreadsheet thing is that you're limited to what they're able to grab from right,

David Ringstrom: 26:13

you get to a point and then they, you stop I haven't worked real deeply with these fields, but we can see them going through this invoice file. It's got a lot of different columns here. This is far more information than we get on a report in QuickBooks. And so I'm just using the arrow keys and the spacebar to turn these fields off because most of these fields I don't want. You want the description, the amount. Maybe the item

Dan DeLong: 26:40

ID, and this is only an Excel function, right? It is not something that is in Google Sheets

David Ringstrom: 26:46

anywhere. Yeah, Google Sheets does not have Power Query, so this is a Microsoft product. Alright, so now here's a great example where it shows you the customer ID and their numbers. And then you're like, Oh what do I do with that? I can go to new source and go to file and go to CSV. And I'm going to pick the customer file.

Dan DeLong: 27:16

And that customer ID is actually the number of that QuickBooks assigns to the list.

David Ringstrom: 27:22

It's a record ID in QuickBooks. So there's those customer IDs. So I'm going to click OK. So the customer ID, if I go to the right. There is the company name there. So if I want the company name on my invoice file, I can use a merge query. Now there's two options here. If I click the merge queries button, I would be then modifying the invoices import that I did. But I'm going to click the dropdown button. I'm going to say merge queries is new because what I like to do is keep each data source intact. So I have an audit trail how I got here. So I'm going to merge the invoices table. And I want to merge it with the customers table. And what I have to do is pick the common field between the two tables, which is this customer ID. So when I click, okay, now, first thing that when, first time I did this in Power Query, I got over to the right and I saw that it just had this table, this column is just this table. I'm like it brought my data in, but how's, where's the data? This little button at the top, this is an expand button. So if I click that. I can then get access to all the fields that are in the other table. Now, for this little exercise for our dashboard where we're headed here, I'm just going to click on company name and click OK. And I'm going to take that and I'm going to drop it back over here. So notice I can pick it, I can move columns around in whatever order that I want. And so I don't need that customer ID anymore. So I can go back up to choose columns and I can turn off the customer ID because I now have the company name. I can double click at the top and I can rename this so I can just call this customer. But another thing about Power Query is. Because many times we pull data out of QuickBooks, we're manually doing this effort to try to massage the data to get the way we want it. And then one, if the data changes, you've got to go do all that again. But two, you can't really walk backwards in your process. Power Query lets you walk backwards because each thing I'm doing is adding an applied step over here. And... When I click on these applied steps, I get to see what the data looked like at that point. So when I go back up to the source step, I don't see where there's that customer table, that shows up because I did the merge, but I don't see the customer field. When I click on expanded customers, now that column is expanded. Now that column got moved back over to the left. Now I'm removed some of the other columns and then I renamed the column. And so you can walk through your transformation, but then, so if I take this data here, if I click close and load, it's going to put it in what's known as a table. And I can do a couple different things with it. One of my favorite features in Excel, strictly for data that I've gotten out of Power Query, is the slicer feature. So on the table design menu, if I click insert slicer, let's say I click on custom, customer, and then I click okay, this gives me a list of all the customers on the report. And if I click, then I see just the records. The other thing I can do with these results is because this is a table, I can turn on a total row. Now the last column is going to get a total. So it's adding up the item IDs, which is not applicable here. But if I clicked over here and chose some, then it would add up those values. Now notice there's some duplication in this report, but we're, assuming that this had real data, cause this is just made up data for the book. But I could also, if I went to insert slicer, if I chose the item ID, it's like I'd have to merge the item IDs but I'd be able to filter for that, so let's not do item ID. Let's do state. So I want to filter by state. So if I go to table design, insert slicer and pick the state, do I have the state? All right, we'll just do city. Maybe they called it, maybe this is it. So this shows me all the states. If I clicked on Florida, that's my one customer in Florida. If I clicked on Louisiana, that's my customer in Louisiana. So this is allowing me, so this alone could be a dashboard of sorts, but also if I go to the insert menu and I choose pivot table, pivot tables are a report writing feature. If I have a list of data, either in a table or just a regular list, I can click okay. And let's say, how do I want to summarize this? Maybe I want to summarize it by state. So there's a list of my states. If I click on amount, there's my amounts. I could then add the customer name there. And so notice there's these buttons here that on the analyze tab, it's in this active field group, there's a button that says collapse field. If I click that hides all the customers, but I can then drill in. And see who's in a given segment. And so with power group, we saw how I pulled in the invoices file and then the customer's file. I can pull in as many different data sources. I want, they all land in Excel. I can then create multiple pivot tables that I can control with slicers. And that's how I'm then getting a view on my terms with. From QuickBooks as they're trying they've made that dashboard screen that you can do some charts and dashboards, but it's never you never have complete control. So this kind of unlocks things and gives you more complete control.

Dan DeLong: 33:47

And then the power of Power Query is that once you've set up these. But once you set up the breadcrumb trail, you can always get back to the cottage, right? Because you've already set that in place. And then you would just run the power query. How do you go back to okay, next month I've created all this wonderful stuff with pivot tables and that, how do I, what do I do next month? When I, when it comes to doing that again.

David Ringstrom: 34:14

Next month, you would take the CSV files, you would export them over the previous files. And then in Excel, you would just go to refresh all, and it would refresh all the data. So the work that you do in the Power Query Editor initially, that's a one time event. It's not where you're exporting a report to Excel, and then every time, time to make the donuts, time to clean up my report, to get down to the format that I need. It's a set and forget transformation.

Dan DeLong: 34:44

So you basically save all that in the Excel workbook. And then it just you just get the overwrite the source and then refresh the data from the Excel spreadsheet and you're like you've done it in a few minutes as opposed to always needing to bake the cake.

David Ringstrom: 35:03

Exactly. And so just to show normally this would be catastrophic I've taken my name over every piece of data on the report, and the data will be gone but if I click refresh all. Power Query reaches out to the CSV file, brings in the data. It wouldn't matter if I, let's say I had gone in and deleted a bunch of rows. As long as I keep some of the connection, if I click refresh all, brings it right back. So it's going to give me some resilience. It's taking a second for it to bring it in. But we get this resilience that we don't normally have in Excel. Because normally we're like, our spreadsheets are brittle. I go, I can't type over that because then I won't have an audit trail of what it was before. Here, everything is connected to data that came out of QuickBooks. And you're cutting out the middleman. You're not having to do any kind of massaging or whatever. It's just, there's

Dan DeLong: 35:54

your data. Yeah. And that, that kind of, addresses some of the challenges that accountants and bookkeepers have because Intuit is constantly making changes, to the way that the navigation, the menus, everything that, that causes us to go bonkers, like, where did that go now? You can okay, you do your thing into it. I'm going to do my thing over here.

David Ringstrom: 36:23

So just to give a vision of a dashboard, I'm going to create another pivot table here. So we're going to insert pivot table. Now this one, I'm going to put it on my worksheet over here. And so let's say that this one, I don't have a whole lot of data to work with here, but I'm just going to choose city and then amount. So that shows me all the cities. So let's say that I add a slicer to this and let's say that I want to filter, I don't know, we'll just do description. So either description's blank or it says opening balance. On this, if I right click on this slicer, there's a report connections button that if I click that, I can link the first pivot table to it and control both of them at once. If I want to see all my opening balance data, I click. Now, these totals represent just opening balance transactions. If I click this blank one there I guess I, then the transactions must be in there twice or something. That's why the numbers are the same again. I don't have a clean, this is an ad hoc thing, but basically if we had real descriptions here, if you different item IDs, if you clicked on that, you would see both pivot tables adjust, and then you'd be able to see, who bought what and which state, and then what cities that represented in those amounts. But this expands to any data that's in those CSV files. I just picked this just as something that's an easy reference point, but whatever it is that's important to you, that you want to track and see, because also, with any pivot table, if we go down to, let's say, value filters, and we say top 10 it's really top X, where X is whatever number you want it to be. If I want to see the top 5 cities that are selling my products, I can say top 5, click OK. So now, that's my top 5 cities. And I could right click on this and sort it, and now I've got it ranked in descending order. And if next month, I took next month's data, saved it up with CSV files, did refresh all, then this would just update.

Dan DeLong: 38:37

My mind is blown. Yeah. Miguel is you're quiet as well. Are you getting all this is this as exciting for you as as... That is for me.

Michelle Long: 38:49

It is. It is really amazing and mind blowing. And Edward was just asking. This is awesome. He's saying, can we get to the data set other than through the QBO advanced backup? And I was just answering. You could use the spreadsheet thing to get to some of the data. But in others, you could use the. Export the report to Excel and try to do it that way. Because any of those reports you would be able to do it. But the best way is going to be to the full backup so that you can have the customers and the data.

David Ringstrom: 39:26

Not everybody has that subscription level, so the slides do show. So let's go back to one of the slides because this might give a little frame of reference because folks saw a lot of things cold there. Let's say that you had a profit loss report. The one that I like to create in QuickBooks is I like to go to reports and choose profit loss. And it to this year so it shows me all the entire year but then the columns I change that two months, so it gives me 12 months, a lot of folks export like the January report, and then they'll do the February report they end up with 1212 reports. I try to group things together when I can and so having a 12 month report that will get populated as the year goes on. It gives me all that income state profit loss data. I like to turn off the headers, because that ends up being extra noise in Excel, and I turn off the footers as well. And so if you find this report helpful you can click save customization, you can give it a name, it will show up on your custom reports choice, you click save, you have to click run to actually get the report. Once you export it to Excel, that's when we would do, let's do our second poll question.

Dan DeLong: 40:38

Where'd it go? There it is. Okay. All right. What type of data do you export? I wanted to point something out, though, David, on the prior slide you had, where it could only have, export to Excel, export to PDF. Or the management reports we had Hector and Mark on the power hour for their right tool extension. And they have, that's one of their features is that you, it turns everything into just send it to a CSV. So regardless of what regardless of what report you're on what level of subscription that you're on, any report can be sent to a CSV. So it cleans off all that header stuff. Okay, right away, save a few clicks each

David Ringstrom: 41:28

time the CSV format is always much cleaner, and it will avoid a lot of frustration.

Michelle Long: 41:37

And that is a tip that I didn't know. So thank you guys very much for that. And here is another tip. So Jessica had asked the question, and I was answering and talk with her a little bit in the chat in the Q and A over there. She had a client that had a really big mess and it needed to be cleaned up and the client didn't want them to just start over, which, sometimes it's better when you've got a disaster to just start over and redo things since we can import transactions and everything. Anyhow, long story short, she wanted to know, should we just. Get the backup, pull it out of QBO, fix everything, and then re import and overwrite the data. And my comment was, no, we want the audit trail of what we've done and what we've corrected. But what are your thoughts on that, David and Dan? When you've got a disaster situation and you need to fix things, what are your thoughts on that? If you've got the backup situation, Should you pull it out of QBO, fix everything, and then upload the corrected file, or, my comment was, no, even if you just are doing even if you do figure out what all the corrections need to be made, and just upload some overall adjusting entries I think you need to have an audit trail of what you're correcting, not just upload an adjusted file. What are your thoughts?

David Ringstrom: 42:59

I would want to upload to a new company. I'd want to keep the old company as an archive and then upload into a new company. I would be okay with that. But I would not overwrite the existing company. That sounds like a recipe for disaster. One thing it's spreadsheet sync does allow you to do if you have an advanced subscription is it does allow you to edit transactions. You can pull, let's say all of the checks that you wrote out to Excel. You can make adjustments and you can post those adjustments back to QuickBooks. And that's a pretty safe approach. You do it to be careful because when you're working wholesale like that, you can wreak some havoc. You copy something down, you didn't realize you overwrote things you didn't mean to. So it's, you got to respect the feature. But if you're, take your time and do it right, then that can help those mitigate their situations.

Dan DeLong: 43:55

Yeah, definitely. One of those things where you, if you don't have an oops damage control plan in place that could be you could, jump from the frying pan right into the fire. With that type of situation, and I appreciate what you were saying, David, about, put it in another place, fresh snow, make your snow angels rather than trying to trying to build a snowman that somebody's already trampled over.

Michelle Long: 44:22

Yes. Yeah. And Jessica says she wanted to upload it to a new file. So I may have misspoken on saying upload it and overwrite it. So that was my bad. Yes. Great comments there. Great discussion. Okay. Sorry. Continue on.

Dan DeLong: 44:35

This is great. All right, we are hopefully what else you got for us, because I don't know if we can make more in here.

David Ringstrom: 44:45

The next thing I wanted to show is whenever by default when you download a file from QuickBooks, it launches in that protected view. And for some reason, this file is stuck. I don't know why it's taking so long. Let's just try downloading it again. So that protected view can hijack your report because it ends up being a speed bump that you have to deal with every time that you. Bring in, you open the file. I'm just going to improvise here. So file, open, browse. So we're going to do profit loss by class. And just to simulate, I'm going to click the little button. So on the open menu here, just a little aside, there is a choice that says open and repair. If you ever get a spreadsheet that says it's corrupted, And rather than panicking, you can click on that open and repair and click open repair and repair it. I'm going to actually choose the choice above it, which is open and protected view, just so we can see what that protected view bar looks like. And one of the weird things that protected view does is it can hide the amounts, particularly when we export reports and QuickBooks, if you're seeing the file and protected view, it's going to seem like it's all zeros until you click that enable content. button, but rather than having to click enable content every single time, there are settings that we can change. So here's what protected view looks like. Now on this report we're, we are able to see some amounts here, but we have to then stop and click enable editing until we can do anything with it. So rather than doing that over and over again, if you instead do a one time change in Excel, if you go to the file menu, if you go to options, we're going down to the trust center, going over to the trust center settings. When you get to protected view, which is step five, you can clear the first and third check boxes, leave the middle one alone, but the first and third ones that will turn off that protected view. And then you can just open your spreadsheet without having to have another, thing catching your sleeve as you're trying to get your work done.

Dan DeLong: 47:20

One less click is always a good thing.

David Ringstrom: 47:23

And one thing I was really, precious

Dan DeLong: 47:24

seconds are saved and then you add that up. That's the. That's going to be a lot of

David Ringstrom: 47:29

time. One of my previous favorite tricks to show about QuickBooks Online is that in Google Chrome, there used to be a setting where you could have it open the spreadsheet automatically. So then it became, if you turn off Protective View, then it became this seamless Excel became a seamless extension of QuickBooks. Over the summer, they took that away. And so now when you download something, it shows up in the downloads. You have to manually click on it. You can't just have it launch automatically. So that's disappointing because that was, every click we can save accumulates. But once we've exported that P& L, it's like I missed, I looked up, looked at a slide that exited the import. But another trap, if you will, is If your report has dates that are headings, there's an enticing little feature that says use first row as headings. We could take that first row and promote it to the heading bar. And just to show you, I can see my guide. We, if you click use first row as headers and that first row has dates that can change when 2024 and your report suddenly says January 24, February 24 and so on. Power Query will not let you import that file because it thinks that the field names are different. So rather than taking these rather than clicking use first row as headers. What I do is I manually change the heading on each column. So that way I can identify it, but then I can keep. Using this import year after year. But other things that we can do with Power Query, we could click on a column. So if I right clicked on the account column, I could say Transform and Trim. That would eliminate the spaces that QuickBooks adds. QuickBooks adds that spacing to the profit and loss report. If I right click on that column and say Transform and Trim takes those spaces out, when I click close and load, then the data shows up in Excel. An additional step I can do is I can, on the query tab, I can go to properties. And change a couple of properties about the query. So one thing I like to change is to turn off that enable background refresh. The intent of enable background refresh is that when it's refreshing and what we mean by refreshing is reaching out to that data source, that CSV file or Excel file, whatever we've attached to and bringing in that data. If we do it in the background, it's going to be, it's going to be slower and it's slow enough that sometimes I'm sitting there looking at the screen thinking, okay, is it actually doing what I want it to do? I like to turn that off because by turning it off, then when Excel is refreshing, I can't do anything, but it happens so much quicker that. It's a minor trade off, but if I click refresh data when opening the file and then click enable fast data load and click OK, then when I open my spreadsheet that has power query, it just brings in the data. So for instance, let's say you have a report that QuickBooks does something strange to it, you downloaded that file as an Excel file, let's say, you import it with power query, you then can You know, set up your transformations going forward. You just take that Excel report, save over that initial file. When you open your work with the power query, we're pulling the cleanup data. And so going forward, you're just working with the cleanup data. You're not having to reinvent the wheel to scrub your data, to get it down to what you

Dan DeLong: 51:02

want. That is so much of a time savers. And I think one thing that is not resonating is that power query and the power of power query is that you set this up once. Yes. And then once you have done like formatting the field or or anything, whatever you've done in Power Query, when you launch the workbook again. Yes. And download new data into the source. Then you don't have to go through all of those memorizations. No. All over again.

David Ringstrom: 51:37

Not at all. And that's what I liked about the CSV dump is that gives you the keys to the kingdom and really can create, a great setup there.

Dan DeLong: 51:51

All right. And what else do you have for us? Because we're coming on the top

David Ringstrom: 51:55

of the hour. So don't forget the refresh all command that refreshes the report brings in a new copy of it. Once you set up a connection to Power Query, another thing is going to show up on what's called the message bar in Excel is now you'll maybe you get rid of enable the enable editing from the protected view. But then this enable content is going to pop up. If you click that button, you're relegating yourself to yet another manual click every time you open the file. Instead, if you go to the file menu, go to info, you're Choose enable content and then enable all content. That will suppress that prompt. This one's on a workbook by workbook basis. Protected DU is for the whole, all your Excel files. But that way you don't end up with that extra prompt that you have to deal with each time. Another poll question. Back

Dan DeLong: 52:52

here. How often do you use Power Query? Go ahead.

Michelle Long: 52:59

This has been fabulous information, David. This has been great. Everybody in here is just loving it and Paul wants to know where can we buy Power Query? It's QB Power Query. Where can we buy QB Power Query?

David Ringstrom: 53:14

Power Query is built into Excel, so there's nothing to buy. 2010 or 2013, it's a free download for those versions. And but it's built into the Excel interface. Do you have to,

Dan DeLong: 53:31

can you use it in the online, like online 365 or does it have to be on the locally installed?

David Ringstrom: 53:39

Excel for the web, they call it Excel for the web. It has some rudimentary Power Query capabilities, but not like it does for Excel, but the Mac. Usually the Mac is the stepchild version and Mac has Power Query as well. So you're not out in the cold if you're using a Mac either. Good

Dan DeLong: 53:59

to know. Another... Do you have a few extra moments, David, to stay with us a little bit? Do you have a few extra moments to stay with it over the top of the hour? Yeah, I sure do. Okay. So the rest of you at what you had for

David Ringstrom: 54:14

just a couple things. One, let's say that you create a connection to power query and then you don't want it to be connected to that file anymore on the query mean you can click delete. That's not going to delete the data. This is going to break the connection. If you wanted to send the file to your client, but you didn't want it tied to anything on your computer, then that delete button lets you break the query connection. When we click the edit button on the query tab that puts us back in the power query editor. If on the applied steps, if there's a little gear to the right, that means you can edit that step. I can click on the source button and pick a different P& L. So let's say I find, I make this great P& L report for a certain client, and I want to share it with another client. I can make a copy of that Excel workbook, go into the Power Query Editor in the source button, link okay to this other file, but different data, but same format. And then it's good to go. Everything else just flows. So the other thing that I was going to do if we had more time, these slides just show how you could take the, because, so another thing that's frustrating about QuickBooks, I could go on, my things are frustrating, but the customer list takes the shipping address and puts it on two lines. And if I'm trying to do mailing labels, if I'm trying to break it out in the columns, then, we're standing on our head to deal with that. Just jumping ahead, Power Query lets you split a column by delimiter, which, Michelle, you might be familiar with the text to columns feature in Excel. It lets you break columns up in Excel, but this is that on steroids because I can, separate things by colon, which I can do with text to columns, but I can also break it into columns based upon a line feed. So suddenly that data that's on that report that's in two rows, if I break it into a line feed, it becomes a new column. Then all of a sudden my data is on one row. It's not on multiple rows. And I can take columns, I can merge them together, I can separate them back out. I, the sky's the limit what we can do. I can remove duplicates. So because, on a customer contact list, if you're using jobs that customer name is going to show up multiple times, I can remove the duplicate so I can then just have a nice clean. Version of my report, which it doesn't come out cleaning QuickBooks because the address is going in multiple rows. They might show up multiple times. All this stuff can just be can just go away. Wow.

Dan DeLong: 56:56

Alright, so last poll here. What takeaways. Did you take away from today besides the fact that you're Your brain is probably ready to ooze out your ears from the nuggets of information that David's been able to cram into this. Yeah, David, I thank you for taking the time to come on and talk about this, the the slides that that are available on the website. I love the way that you have each. Each step numbered there, so if you watch the replay as you're going through, because this is probably, if you glanced or looked away, you might have missed something missed the nugget, so there are a lot of this gives you a lot, a great way to re, re, rewatch the webinar if you need to and then follow along with the steps with

Michelle Long: 57:43

Can you go to the slide with his additional training? So you have a slide with the that shows about the training that David has?

David Ringstrom: 57:50

Yes, I think it was on your bio side to say there is a slide missing from the slide deck. Dan, I'm gonna fix that and send you the new slide. So like steps one through eight in this process they're not, and that's a mess. This goes from one to eight and this goes to nine. So there's a difference not connected to eight. That I'll got it. Plug that in and get that to you. Okay. Appreciate that. But places to find my training, I. Have a website. That's professionals, Excel. com. Those courses are not available for CPE. NASBA has all these requirements and I just haven't had enough demand yet to, to warrant that. But cpethink. com has on demand versions. I teach live classes for my CPE. Someone mentioned that cp, cchcplink. com, cch cpcredit. com. I'm teaching a lot of different live places. So if you want live training, let me know. My personal website is davidringstrom. com. So you can see a lot of my stuff there.

Dan DeLong: 58:56

I'm just going to share the takeaways. You had some wonderful takeaways for our group here. Appreciate you again, joining us and taking the time to join us today and look forward to working with you on many books. Thank

David Ringstrom: 59:08

you. They should be easier going forward. Yeah,

Dan DeLong: 59:15

Fantastic. So we are, we did run a little over. I, we appreciate everyone for sticking around that could, we are gonna have the replay and we'll also have those updated slides where, for those, for the right step one through eight so that you can check that out on the QB power Hour. Hopefully you join us again for the next time. And Michelle, always great to see you. And David, thanks again for joining us. Any closing thoughts Michelle? Just

Michelle Long: 59:39

great information. So thanks again, David. Thanks, Dan. Thanks, everybody. Have a great day. My

David Ringstrom: 59:44

pleasure. I love seeing all the feedback. Thanks, guys.