Raw Data By P3 Adaptive
Power Query Saves Christmas w/ Gus MirandaListen Now:
Today’s guest, Gustavo Miranda, started his career in a research adjacent Program Evaluation position using Power Pivot Slicers in a non-Power Pivot table before he was introduced and lured by the power of the Power Platform. He shares his story of how reducing friction and creating automation introduced even non-data people to the concept that data quality matters. Most importantly, though, today you will learn the underdog story of how Power Query saved Christmas during the Kronos Hack of 2021. Get the inside scoop from crisis meeting to crisis meeting until the moment when Gus stood up to save the day. While Gus did the heavy lifting, a task force soon formed to collectively finish the project. While other multi-million-dollar companies were paralyzed, shocked, and attempting to ad-hoc calculate payroll and vacations, often incorrectly, Gus’s task force used Power Query to create an effective solution in time to prevent employees from missing Christmas with their families. Even Tom must agree that tales like this really show the power of Power Query’s ETL abilities.
Also in today’s episode, we learn the origin story of slicers in Excel, how Rob and Amir were behind their creation by seducing the Excel team, trading resources, and creating the opportunity for Microsoft to back into needing a separate application to maximize the impact of those slicers. That application? Power BI desktop. In an exciting tale of what could have been, we also hear the story of the Power BI canvas in Excel that was never meant to be as well as the lessons learned: never try to add truly new capabilities to Excel or you end up with Power View. As our friend Scott Esti used to say there are two kinds of people in the world, people who hate Power View and those who haven’t used it!
All this and more on today’s episode! Be sure to leave us a review on your favorite podcast platform to help new listeners find us.
Also on this episode:
Rob Collie (00:00:00): Hello, old friends. Today, we welcome Gus Miranda to the podcast because he once saved Christmas with Power Query. Not many people can claim such a thing. In fact, we just recorded an episode with our own Justin Mannhardt, hasn't been released yet, where we were talking about a use of Power Query that had really nothing to do with feeding a data model. It's like an operational use of Power Query, more of a workflow oriented type of thing. And this example from Gus is also very much in that vein, but Justin didn't save Christmas, only Gus has. He's like the anti-Grinch of Power BI.
(00:00:36): I do wish, however, that Power Query could be used to fix podcast audio quality, because I'll show you my cards here. We've used a service called SquadCast for more than two years now to record these podcasts, and it has been pretty good. It's been pretty amazing for a long time. But again, in this episode, we had that audio quality problem where our guest's audio never actually uploaded. And so we're stuck with the generic mix down from the Cloud, and we don't like that at all. One of the things we like to preach is that resilience, adaptivity, it's in our name as a company, is more of a positive goal and a life philosophy than attempting to be perfect. So we've already adjusted our workflow around this. We might even be abandoning SquadCast for future episodes and going with another Cloud service. It can't be worse at this point.
(00:01:29): But even while we're using SquadCast, we've been making changes to how we record it and check along the way, make sure that we catch this problem if it's going to be happening again in the future. This aggression will not stand, man. The audio quality really ties the room together, dude. I'm just going to load up on the Big Lebowski references here today. All right, well, let me get out of the way because Christmas needs saving and Gus is here to tell us how it's done. So let's get into it.
Speaker 2 (00:01:56): Ladies and gentlemen, may I have your attention, please?
Speaker 3 (00:02:00): This is the Raw Data by P3 Adaptive Podcast with your host, Rob Collie and your co-host Thomas LaRock. Find out what the experts at P3 Adaptive can do for your business. Just go to p3adaptive.com. Raw data by P3 Adaptive is data with the human element.
Rob Collie (00:02:25): Welcome to the show, Gus Miranda, how are you today?
Gus Miranda (00:02:30): I'm pretty good. Thanks for having me.
Rob Collie (00:02:32): Oh, thanks for being here. How did we come to know each other? Not through Twitter. We've established backstage that it is not through Twitter because you do not Twitter.
Gus Miranda (00:02:41): At the start, it seemed like a bad idea, 144 characters. What am I really going to be able to express through this? And over time, as the social impact of Twitter became more and more known, I did not regret my decision.
Rob Collie (00:02:52): But imagine if you'd been on Twitter the whole time helping stem the tide. Maybe you not being on Twitter is why it became so toxic.
Gus Miranda (00:03:00): If you've ever been at the beach when the tide comes in or goes out, you realize one individual doesn't have much of an effect.
Rob Collie (00:03:05): No, it's such a weird thing, right? This prisoner's dilemma. We recycle at our house fanatically. I'm positive no one else does this. Oh, you got a glass jar, you going to recycle it? Oh, no, no, no. You got to peel off the label. No one peels off the label. But even then, there's this little adhesive band, the paper is still kind of stuck there. That's contaminant and it's going into the system. You take the lid off of a peanut butter jar and it's mostly metal. But then if you think about it, there's also this rubbery coating on the inside of the... That's not good, so do you recycle that or not recycle that? And I'm always trying to convince my wife that this tremendous investment of our time and energy isn't going to make any difference. And she says, "Well, what if everybody thinks that way?" And she's got a point. We both have a point. You're right, but what if everyone thought that way though, Gus?
Gus Miranda (00:03:59): Then maybe no one would be on Twitter. We actually have a site in Toronto that tells you exactly what you can recycle and what you can't, and it's actually very disappointing. You can't recycle black plastic, you can't recycle the plastic on toys. The promise of recycling that we can consume the way we do and not have any consequences ended up being a bit of false promise.
Rob Collie (00:04:20): It was the false promise engineered by the plastics industry.
Gus Miranda (00:04:23): Exactly, yeah. Sometimes I do alumni events at U of T and they have these one hour lectures you can go to, and they pulled out the stack of advertising and all the magazines where plastics companies, oil companies, really, because plastics are made out of oil. All the articles that they subsidized to talk about this great future where we would be able to just buy everything in plastic and it would all get recycled. It was a good idea at the time, and then it got co-opted.
Rob Collie (00:04:47): Well, I mean, I've read even more cynical things about plastic recycling. The plastics industry recognized that there was a rising tide of pushback against all this plastic, and so they engineered the entire thing, the little number symbols that go on plastic indicating what kind of plastic, what kind of recycling they are, they deliberately use the circular arrow symbol around it to resemble the recycling. But there's nothing about these numbers that actually mean it's recyclable. It's just telling you what kind of plastic it is.
Gus Miranda (00:05:13): What kind of plastic.
Rob Collie (00:05:14): That's it. That's all it is. And then they just put the little arrows around it, making you think that means it's recyclable. The most damning thing I read about it was written by someone who actually had helped the plastic industry execute this plan later on coming clean and saying, "Look, plastic industry doesn't want you to recycle. They want to sell you new plastic." That's what they want to do. They want to sell you new plastic over and over and over again. That's their goal. Recycling is not even good for their business. They just wanted the appearance of recycling.
(00:05:45): It is disappointing, isn't it? The whole thing. But we have this special garbage can in our kitchen for the two halves, but even aluminum cans that are the most recyclable thing, apparently, the average beer can these days has really fancy graphic sticker on it and sometimes the sticker's easy to remove, sometimes it's not. If you don't peel that a thing off though, aren't you contaminating the whole batch? I have no idea. It's just... Well, okay. So we've really started on an upbeat note here. We've trashed Twitter and recycling. What else can we take down on our rampage? Okay, so let's redirect.
Gus Miranda (00:06:22): Sure.
Rob Collie (00:06:23): Tell us what you do for a living today.
Gus Miranda (00:06:27): Today, I'm a BI developer. I came from a discipline called program evaluation, which is basically for social programs and things like that. Take measurements about how they're working and you tell the agency that's putting them on what's working, what's not working, are you meeting the goals that you wanted to meet? Are you maybe achieving things that you didn't think you were achieving? Maybe it's worth keeping going. Are you within budget? Are you meeting your targets? Things like that.
Rob Collie (00:06:53): Program evaluation. I mean, when you say programs, are we specifically talking about government programs?
Gus Miranda (00:06:58): It doesn't have to be a government program. In my case, they were usually government funded to some degree, either funded by governments or funded by maybe big charities like the Red Cross or something. But yeah, usually, it's some kind of social program.
Rob Collie (00:07:11): This is a field?
Gus Miranda (00:07:12): Yeah. Yeah, it's a field. You can get a masters in it or maybe even a PhD.
Rob Collie (00:07:18): Here's where I would make, by saying that I would make, I'm actually making a snarky joke about this must be something we just don't know about in the US. Canada's a little bit more socially minded than the US. Although every now and then I see a news story from Canada, I'm like, "Oh, okay, that's what I'm talking about. That's more like us." I do hold Canada up as this example of a little bit more of a collaborative society than the US. So it wouldn't shock me that this is a field that is known about in Canada and is not so known about in the US.
Gus Miranda (00:07:50): I don't know if it's very well known about, because when I graduated from college, I didn't think, "I'm going to go into program evaluation." I knew I probably wasn't going to go to grad school, but I'd taken all these stats courses and I'd learned some stats software like SPSS, and I thought, "This has to be good for something." I started volunteering at a kid's mental health agency for what was called the research department, but really the department that did the dust and bones scientific research was adjacent to this one. What this one did was more program evaluation.
(00:08:22): It was based on satisfaction surveys. It was based on service data from a client information system, which is like an ERP but more clinical. And it was based on a psychometric measures, so you would give people some kind of measure ahead of time that measures a psychometric construct, like anger, depression, style of discipline, and then you would give them the same measure after an intervention and you would see if you're making a difference and then run T-tests and ANOVAs and stuff on that and write up reports and people wouldn't read those reports.
Rob Collie (00:08:53): Yeah, like the specifications that we used to write for software at Microsoft, the really, really long specifications. We used to joke, these are the things that no one reads. It would take a long time to write, but one of the job requirements of everyone else was to not read it, apparently. I have to ask, someone comes into this mental health program and they get an entry evaluation and then after some interventions, some treatment, counseling, whatever, they get an after the fact evaluation, but the person administering the post knows that it's a post, right?
Gus Miranda (00:09:25): Well, a lot of times they're just something that the person fills out themselves.
Rob Collie (00:09:29): I see.
Gus Miranda (00:09:29): I think with these tests, they can be paper and pencil. There was one that was mandated where it was, who gave the treatment would actually do the rating afterwards, and they were expected to be completely unbiased.
Rob Collie (00:09:41): You can't double blind this, can you? Although, probably could with a little effort, but certainly not with self-report. You're asking the person who's gone through the program, I've got all kinds of complicated feelings about this. The placebo effect is still real. If someones feels better [inaudible 00:09:56]. I like to describe the placebo effect as the impact on your body if you count all of the organs and all of the systems in your body, except for the brain organ as if that organ doesn't matter, so placebo's still good.
Gus Miranda (00:10:12): What would be nice is if you could follow up six months after the treatment to see if the person still feels that way, but really none of these agencies are super well funded.
Rob Collie (00:10:21): Of course not.
Gus Miranda (00:10:22): So the logistics it takes to, first of all remember to have some kind of notification that tells you to send something to these people six months down the line or even the resources to do it. Really back in the day before we had all the automation and Power Query and stuff like that, they just weren't there.
Rob Collie (00:10:37): Of course, they're not that well funded, because there's no profit. There's this coffee table book on our coffee table downstairs that contains all of these posters that were painted in the 1950s in the United States, and a lot of them are just advertising for National Parks and things like that. There's one poster on the cover of this book that shows a mom taking her son to an eye doctor or something like that, and the caption on the poster says, "He may only need his eyes checked." And that's the whole poster. And thinking to myself, so reflects a different culture and a different mindset than what we experienced today.
(00:11:20): We used to view the citizens of the country as part of the infrastructure, as part of the assets that make things go. This was a federal government poster. You can see it's almost done in the same style as the Uncle Sam needs you World War II recruitment posters or buy war bonds. But it's talking about taking your kid to the doctor to have eyes checked. Wow, where did that go? That is gone. We don't really think that way anymore. It doesn't even have to be a touchy feely emotional thing. It could just be a purely calculated Machiavellian thing. Don't you want everyone to be in their best and most productive shape possible? Just kick them to the curb.
Gus Miranda (00:12:04): I understand what you're saying. I've worked in a few of these centers and Ontario, maybe Toronto now is actually the epicenter of this, but they started something called the What's Up Walk In, which was a Toronto wide, maybe even an Ontario wide initiative where they set up what's called a single session model where anyone could walk in off the street and just get a session with a counselor within half hour. And maybe in that half hour you would have another problem solved, but maybe you would get some tools to solve that problem. You could go out and try them out. Then if you wanted, you could have maybe three follow up sessions or something, or you could just stop right there.
(00:12:36): And the effect was great. For example, one of the things we found was before we implemented this, I think the split was like 70/30 or 60/40 in favor of boys who would come in seeking help. But afterwards, it kept getting closer and closer to 50% because I guess when you give girls a chance to come in on their own, some of their problems might be more internalizing problems. A parent isn't going to think, I'm going to bring my daughter in for counseling because she's not giving me enough problems because she's too quiet, for example. That effect that you get when you give the main stakeholder the initiative to seek the help they want and to access the resources that they need.
Rob Collie (00:13:10): That's one of those things where the percentages change. Intuitively, we might reach the conclusion that fewer boys came in once this policy was enacted and that's not what happened. You got an influx of girls, a significant increase in the number of girls coming in, and that's what drove the percentages to 50/50.
Gus Miranda (00:13:27): But it's also because of the model. It's one session, so you don't have people going months and months in service holding up someone who's coming in off a wait list.
Rob Collie (00:13:35): Yeah, okay. Without even thinking about it, I would've expected going in that more girls came in for counseling because they're more willing to engage with the idea of counseling in general. But of course the parents being the initiators makes all the difference in the world. I can totally get that parents are sending their boys to counseling more frequently because their problems are a little bit more, I don't know, above the waterline. What an interesting thing. So you said they ran that program for a while, so it sounds like clearly that program got canceled.
Gus Miranda (00:14:06): No, no. I just went to work somewhere else.
Rob Collie (00:14:09): I see. All right. So that's still going. Good. That's a good program. I like that. So I told a story just to Luke, in college, my best friend in college. The first time I was going to meet his girlfriend, this is after knowing him for a year, she drove in from out of town. I saw them walk by the window. We live on this ground floor apartment. They walk by the window. So they're like five feet from the door and I'm eating a bag of Fritos and I take another bite of Frito, and as the door opens, I inhale a fragment of Frito. I couldn't talk. No, I'm just trying to hand signal like, Fritos.
Gus Miranda (00:14:41): First impressions are overstated.
Rob Collie (00:14:43): Yeah. But she called me Frito guy. I'm just kidding. She was nice about it. Take a little shot of bourbon. Does that straighten you out?
Gus Miranda (00:14:50): My brother-in-law got me some Jameson Stout Edition. They do it in the stout casks.
Rob Collie (00:14:57): I thought it was-
Gus Miranda (00:14:57): It's a bad cop. It's a bad cop.
Rob Collie (00:14:58): I thought I was kidding. I was kidding when I said that, but apparently I was right.
Gus Miranda (00:15:02): Whiskey on the odd occasion.
Rob Collie (00:15:05): All right. What better occasion than being on-
Gus Miranda (00:15:08): In a coughing fit.
Rob Collie (00:15:09): On the Raw Data podcast and having a coughing fit? Yes. So we've had interesting backstage interruptions here, haven't we? Gus has been struggling with a terrible breathing condition, must have been tear gassed. And then we had my wife stick her head into my podcast layer with the towel wrapped around it in a robe from the shower to inform me that the car repair was going to be $7,000. And that was information that needed to be conveyed right then. So we took a 10 minute break. So let's take a turn anyway.
Gus Miranda (00:15:40): Sure.
Rob Collie (00:15:41): So how do you go from program evaluation to BI developer? I mean, this doesn't seem like a difficult leap. That seems like a much more gentle transition than most transitions into BI are. At what point would you say that journey began? When did you start making that switch over?
Gus Miranda (00:15:59): As soon as you leave college and you get a job, you realize that you don't use statistical software as much as you use Excel, right?
Rob Collie (00:16:05): Yep.
Gus Miranda (00:16:06): So right off the bat, I started using Excel. I made some huge mistakes. Every report we did was based on fiscal year and I had to ask every day, "So wait, it ends on March 30th and starts on April 1st?" It took me way longer than it should have to get my mind around it. Now it's like that's when my year starts, April 1st. But back then it was hard to get my head around it. Actually, one of the first big reports I wrote when I was doing the checks on it, I realized that all the numbers were exactly the same as the year before. And it was because when I was getting the data together, I'd actually done the wrong year.
(00:16:39): Instead of doing 21-22, I did 20-21. And that was when something clicked, there has to be a better way. Getting the manual data, copying and pasting. My supervisor taught me about pivot tables pretty early on. So what I like to do was put the data in there, but the data's never structured in the way it's supposed to be to go in a pivot table. The clients down the side, the questions on the top. And you have to un-pivot them. Luckily we had SPSS, which is the statistical software we used, and there was something in there called Columns to Rows or something. What we did is we would get the data we had in Excel, we copied into SPSS, we did the Columns to Rows, we put it back, and then we put it into the pivot table. And even so you still couldn't get a distinct count of clients.
Rob Collie (00:17:21): Oh, no.
Gus Miranda (00:17:21): Someone else on your podcast told this exact same story. Once a week or once a month, I would do a search for how to get distinct count in Excel. And one day an article, I'm not sure if it was by you or someone else called, "Get A Unique Count of Values Using Power Pivot." And luckily I was able to open up the Power Pivot data model or whatever and put the data in there and get distinct count. And that alone saved me tons of time, tons of stress. As I kept reading about it, I saw that what else you could do is you could connect tables and I'm like, "Oh, this guy buried the lead." Because our struggle in pro-evaluation was getting the data from the system because we always had to ask IT for it and IT they were nice, but they didn't really understand the structures we need for research and things like that.
(00:18:03): And so every single change had to come through us. We would ask IT, IT would give it to us, we would give it to the consumer. Once I was able to just get more or less raw tables and just connect them, that loop was closed so fast. Someone could ask me for something and I could just throw in a few filters because slicers had just come out around the same time, or I'd started it using a little bit before.
Rob Collie (00:18:23): Power Pivot was the reason that Slicers were added. Did you know that?
Gus Miranda (00:18:24): I did not know that. I thought they were actually there before Power Pivot.
Rob Collie (00:18:31): Well, it's just an opportunity for me to tell behind the scenes Microsoft story.
Gus Miranda (00:18:34): That's my favorite part of the podcast by the way.
Rob Collie (00:18:36): Is it?
Gus Miranda (00:18:36): I especially like it when you tell stories about people being mean to each other at Microsoft.
Rob Collie (00:18:41): There's a lot of that nerd bullying. You find out a lot about people's character when they grow up, they get into a position of power. So Amir, when I was working on Power Pivot with Amir and others, Amir was convinced that we needed a completely new visual canvas in Excel. I think he was already jumping way ahead to seeing sort of what Power BIs visual canvas looks like today. And he was envisioning that needing to be added to Excel. We've seen Power View added to Excel in the past from a similar perspective, but I knew that any effort to build a new visual canvas into Excel was going to be exorbitantly expensive and a failure. So I was pushing back heavily on this. "Oh no, no, no, no, no, no, we can't do this. We have to let Excel be the charge. Visualization layer needs to be Excel."
(00:19:33): So I sat down with him and said, "Okay, let's make a list of all of the things that Excel is missing. Let's get really crisp about it." I expected this meeting to come out with a list of like 15, 20, 25 things based on the way he was talking about it. But it's funny, at the end of the meeting, all we were missing was slicers. Fast forward to today, we see all of the things that Power BI's, visual canvas can do that Excel's visual canvas can't. My initial suspicion was correct. It's like dozens of things. Bookmarks, and drill through, and tool tips and all. It's just so many crazy things that are missing from Excel. And I'm grateful that we have them now, but at the time we just didn't have the budget to do anything like that. So slicers, okay, all we're missing is slicers.Okay great. We'll go and make a case to the Excel team that they need slicers. And so me and Amir went over and we pitched the Excel team on slicers and they're like, "Hot damn, yeah." I remember Terry, the dev manager going, "Oh, I would want that even for regular pivot tables." I'm like, "Oh, mission accomplished." Everyone's excited about this. So what happened was the SSAS team, the Power Pivot team lent a handful of developers to the Excel team to do this because Excel didn't have the resources to do it themselves. They had enough going on their own. And this was supposed to be kind of like Gilligan's Island, a three hour tour, oh send developers over there in and out in a few weeks. They didn't come back for two years to implement slicers.
(00:21:03): And so Power Pivot was the reason that those put into 2010 Excel. That was the motivation. That was where the funding came from. But of course it was also added to regular pivots as well. I don't know if slicers were connected to tables in the 2010 release or if that came in the next release. I don't remember. Might have been one or the other. Doesn't really matter. But yeah, slicers on tables is really how-
Gus Miranda (00:21:25): Nobody used that anyway.
Rob Collie (00:21:27): No, no. But it's super cool when you do. Then the lesson that was learned from that was like, okay, it took two years times seven resources. Because it wasn't just developers, it was also test engineers to put something as simple as slicers into Excel. The lesson learned there was never put it into Excel proper ever again. The Power BI team, the Power Pivot team talked the next Excel regime. There was some turnover. They talked the new Excel regime into allowing Power View to be a new sheet type. I think that's in a way, the way that they backed into needing a separate application, Power BI desktop.
Gus Miranda (00:22:01): Power View, a lot of people [inaudible 00:22:04] it, but it wasn't all that bad. I mean, obviously when you see compared to Power BI now it's not great, but compared to what was there before, you know could set up a 3D map and you had all these charts that could connect and stuff. It wasn't that bad.
Rob Collie (00:22:16): Bad. And then there was the Power View for SharePoint, which was actually different and had more features than the one that was in Excel. It was a strange time. The number one problem with Power View, especially when it was in Excel was, well A, it was an alien in Excel. It had nothing to do with the rest of Excel and it was so obviously a fish out of water. Also that it was just like demoware. It was built to the level where demos could be given and not built to the level where it was actually humane to use in a production environment. Our friend Scott [inaudible 00:22:47] used to say there's two kinds of people in the world, people who hate Power View and those who haven't used it. So yeah, there you go. There's one person. We have one Power View truther, one defender, and it's Gus and he's safe north of the border in Canada.
Gus Miranda (00:23:01): Well honestly, I used it for a few months once. That's the extent. I didn't do a lot. I know the [inaudible 00:23:08] BI guy. He's a big fan too.
Rob Collie (00:23:09): All right, well so we have a couple, but it sounds like you were already on your way from group two to group one, not used it group into the, I don't like it group. Maybe we just need to give it some more time.
Gus Miranda (00:23:19): I had Power BI, obviously Power BI blows it out of the water. There's no reason to use Power View.
Rob Collie (00:23:24): Power View is like this deep family shame that no one talks about it anymore. It's like you can't turn it on. But at the time that you read the distinct count article that pulled you into this world, do you remember whether you just had to go to the comm add-ons and turn on Power Pivot or do you remember if you had to go and download it?
Gus Miranda (00:23:45): It was really early on in Power Pivot.
Rob Collie (00:23:47): So it'd been like 2010.
Gus Miranda (00:23:48): Pretty sure I had to download it, yeah.
Rob Collie (00:23:49): Pretty cool.
Gus Miranda (00:23:49): I had to ask IT, but they were fine with me doing pretty much whatever I wanted.
Rob Collie (00:23:54): Really, yeah.
Gus Miranda (00:23:56): Except accessing their tables.
Rob Collie (00:23:58): Of course not. We put data in these databases, but we don't let anyone get any out.
Gus Miranda (00:24:03): It's funny because the IT director at the time, she built the thing 10 years before, a complete client information system basically from scratch, and yet it was so difficult for them to get the data out. But the way it was structured, the amount of information that was in there, it was an amazing accomplishment, what she'd done. It was just the second phase of it wasn't great and IT at the time wasn't so into the democratizing of information. I started using Power Query a little bit after that whenever Power Query came out. And that was really where we just saved so much time. Because at that point all that cutting and pasting we had to do into the restructure.
(00:24:39): Now we would get Survey Monkey surveys, we would come in, we would just pivot them, break them out into a relational table and we had the report done. The amount of labor we had to do in our department went from, we were packed wall to wall every quarter. We had our things coming in. We spent the whole quarter doing a big report. All of a sudden everything was built. All the tables and everything, all the charts and we just cut and paste them, write a little description and put them in. That story I told about messing up the dates, I started building little DAX, text formulas that would tell you the date goes from here to here. Wouldn't you know it, that never happened again.
Rob Collie (00:25:15): Something about having contextual readouts on a lot of our advertising reports that we use internally. I've gotten in the habit of putting just a single card visual on the report that basically reflects the date filter. To have to open up the filters pane or hover over a visual to decipher what the date filter that you're looking at is, uh-huh. No, no. It's got to be really, really, really surface level. Right now, most of our reports have this giant blue minus 60 displayed. Minus 60 days from today, 60 day moving window. The number of times that I was misinterpreting things because there was a filter applied completely visible right there on the filters pane, but who cares? It's got to be right there.
Gus Miranda (00:25:58): I'll you one better, what I do now is I use the calculation group where I have my year to day, same period last year preceding period. And I put a date range for each of those. So the date range gets filtered by the calculation group and you can see for each range how many days you have in the range and what the date range is.
Rob Collie (00:26:14): I'm not cool enough to have ever used calculation groups. Can you believe that?
Gus Miranda (00:26:18): Well you passed the day to day, right?
Rob Collie (00:26:20): Yeah, yeah. But back in my day, you just copied, pasted that formula over and over and over again and changed the filter and you liked it.
Gus Miranda (00:26:28): For five minutes when calculation groups came out, do you remember? It seemed like there were only going to be a premium option.
Rob Collie (00:26:34): I don't remember that. That sounds about right.
Gus Miranda (00:26:38): And so I was going to write an article, calculation groups for poor people where what I did was how even in Power Pivot you can do your table where you have your list of measures, right?
Rob Collie (00:26:48): Yeah.
Gus Miranda (00:26:49): So you can pump the result of that into another table where your list of measures is your time intelligence. So you could pump that into there and that one you could pump into your same period last year comparison, same period last year and everything. It would've been a super long article. And then we got the news that actually no, everybody gets calculation groups. It was sort of a moot point, but it would've made me look brilliant.
Rob Collie (00:27:09): Oh yeah, and that's the kind of thing that I respect all the way around. First of all, subversive workarounds for features that we've been deprived of. Okay, yeah. That's my jam. Super long article. Oh yeah, is there any other kind? Asks Rob. Alex Powers on a couple of occasions has mentioned how he's gotten away from blogging a little bit lately because he fell into the, and he calls it like the Rob Collie trap, which I completely admit to. You start off thinking, again, it's the three hour tour, it's the minnow going off on Gilligan's Island, oh this is an easy article. 45 minutes in and out done and you got six, seven, eight hours.
Gus Miranda (00:27:49): Well if it was just writing the article, it wouldn't be so bad. But you have to do all those pictures where you put the different layers and the arrows-
Rob Collie (00:27:56): Oh yeah, yeah. Then apply that to writing a book. The book that I was going to write was only going to be about 100 pages long and then it ended up being like 250 because you just keep discovering all these places where, I'm skipping something important here that I take for granted but the reader won't. So it's like a fractal. Every time you get closer there's just more detail and more detail. It just goes forever. And in theory, if you take that Xenos paradox, I'm still writing those blog articles. They're not done.
Gus Miranda (00:28:23): Actually one of the reasons I stopped too was I didn't do a lot, I wrote three or four. You guys published the first one by the way, which was a huge confidence boost for me.
Rob Collie (00:28:30): What was it on? I don't remember
Gus Miranda (00:28:32): Using Power Query to get data from Statistics Canada because they didn't have an API that you could get their age and sex data from. So if you need age and sex data broken down by postal code or-
Rob Collie (00:28:44): Demographic.
Gus Miranda (00:28:45): Postal code is what we call the zip code. It's super hard because their interface, you have to go postal code by postal code. What I found is that in the HTML of that page, they basically have a big table that they're pulling that from. It wasn't pulling it in from an API. So what was super interesting is that you could use Power Query to go into the HTML of the table and then transform those lines to pull out that section of the HTML and build a little table out of it.
Rob Collie (00:29:09): I could never do that. I believe that's doable, but I would get into that and pretty quickly go, "Okay uncle, I need help." That's cool though, HTML parsing.
Gus Miranda (00:29:18): When I first started getting into Power Query, I was so amazed because it saved me so much time and I had so much time to kill, you have so much free time. There was this video that I don't know if Matt Masson or Matt Mason, I think he was on the team that developed Power Query. He had a video called "Deep Dive into the Power Query Formula Language." I watched that thing 10 times. Because much you, my first impression of this thing is, what is this? It makes no sense, but I went deep into that video and I tried some of the things in there and it's not so much that I would ever write Power Query code from scratch, but when you start with a point and click and then you supplement that with the code, it's so powerful.
Rob Collie (00:29:56): I still feel like I'm standing on the beach not wanting to get my toes wet and that every time I go in I get a function or a little tiny fragment of M code off the internet. Once I copy it, now I'm like, "Okay, now where do I paste this?" I struggle with that. Okay, so you paste it into... You go to the advanced editor, the advanced view editor, you paste it in there, but that's not going to work because you've got to now wire it into the rest of your M code, the ins and the outs and just cognitively took me a lot of effort.
Gus Miranda (00:30:25): But it's not even the case that that's your destination. It could be the case that's something that you paste in the formula bar or it could be that you paste it in the new column dialogue or in the replace column dialogue. Even that part isn't clear. It's something that you always do it in context. You're doing this transformation and it's a story that the data's going through to get it into the structure you need, you might just need to fill in a level part of that story.
Rob Collie (00:30:51): So you're working in program evaluations still, now you're slinging DAX and M and what's the next career change that happens after that?
Gus Miranda (00:30:58): Well, what led to their career change was we had a problem with our wait list and that it wasn't very clear who was waiting or what they needed or whether they'd had services before or who was waiting how long and for service with who. And so I just pulled together a bunch of tables and I built in desktop an interface that people could use to click on a client's name, see how long they've been waiting. You could get the roll ups too, but this was more important for them, what their presenting problems were, who they were waiting for service with, so the name of the worker. Or because everything's tied together, you could click on the name of the worker and see who was on their wait list and what their presenting problems were.
(00:31:37): Or because sometimes we have group programs coming up, the group programs would be like for, I don't know, anger management or anxiety. And so you might want to see who are the kids on the wait list that are presenting with anxiety. So you click on anxiety and you would see those lists of clients and you would see the workers there waiting for service list. So you can call the worker and say, "Hey is it cool if I call this person to be in my group?" And people are pretty impressed with that.
Rob Collie (00:31:59): It's just magic.
Gus Miranda (00:32:01): It's amazing.
Rob Collie (00:32:02): I completely imagine that interface feeling like magic relative to the complete void of a solution that you had before.
Gus Miranda (00:32:09): Yeah, it was. Nonprofits can't do much in terms of bonuses or compensation, but they did what they could. They gave you free tickets to the fundraising gala do every year. And it's funny, I presented that at a conference. That PowerPoint presentation is still floating around the internet somewhere. And I take a look at it the other day and I look at that data model, it makes no sense whatsoever. I wouldn't understand how it works or why it works. But when I was at the conference, even though it was terrible, I got two job offers based on that.
Rob Collie (00:32:37): I think this is delicious. I love these moments. So you're saying that you look back today on the Power BI model that you built at that time and you don't even know how it works. Now, that could mean one of two different things and I think I know which one you're meaning because you've already kind of tipped your hand. But back when I used to do Excel extensively, sometimes I'd look back on my work from a couple of years ago, look at an Excel spreadsheet and go, "Oh my God, how smart was I two years ago? I was like way smarter two years ago than I am now, because this thing is next level. I don't even know how this works." So there's that, I'm impressed by my past self, but I had the opposite experience with Power Pivot and Power BI looking back on things I did before, I didn't even know what I was doing. I would do this so much better now, which flavor are we talking about?
Gus Miranda (00:33:21): I should say there were some models I built back then that were more the star schema, but because this was all data that was only available from it, I sort of had to piece together the tables they would give me. And also there were some things that were too sophisticated for me at the time. Now I understand when you have three dates in a table, you do the-
Rob Collie (00:33:40): Inactive relationships.
Gus Miranda (00:33:40): Inactive relationships and use relationship after that, but at the time I didn't. So I had to work my way around that. There was that, what I had to work with. It's more like, yeah, now it seems way easier now that I know what I'm doing. But still the fact that I managed to get something that works so well at the time was pretty impressive. Not looking back at it for me, but it was impressive for the people around me. And because the software was new, I think that was also what was blowing people away. And one amazing thing that happened is the intake workers who started using this, they would actually go back as they found out more about the client and put in the presenting problems because it would help them to do that. So the effect it has on people who aren't even necessarily into data.
Rob Collie (00:34:19): It's the spotlight on data quality. Now, it's nice that when you shine that spotlight, if people actually respond and actually improve data quality. Too often the spotlight goes on and the people who are responsible for the data quality go, "So?" Let's be clear also, it was impressive in respect to what you are able to accomplish at the time. I mean that in the absolutely most positive sense. Even if you look back on something, this is the dynamic that I love about the journey of getting better at Power BI is that you look back on the things you did before and you have this weird simultaneous dualism of the way you feel about it. One, is-
Gus Miranda (00:34:56): There's some nostalgia for sure.
Rob Collie (00:34:58): Yeah, totally. So one of them is like, "Oh I would do this so differently today. I know so much more today." And at the same time it still was super amazing. What you built back then was amazing. It blew people's minds how effective it was, how useful it was, and the fact that you were able to make it work even not knowing the things today is amazing. And it also highlights your growth curve and everything. It's one of the funniest and warmest feelings is to look back on some of your older work and go, "This was adorable and amazing at the same time."
Gus Miranda (00:35:41): People are still blown away by it who aren't used to getting... Because some people get Power BI stuff, but the data model isn't great. There's not that much you can do with it that you couldn't do with reports before. So there's still people that are impressed, but also other people, now that they know it's possible, the demands have caught up with the technology. So there's not as much free time, but for sure the stuff we can accomplish now and the time we can accomplish it is amazing.
Rob Collie (00:36:07): Working on software at Microsoft, and from a usability perspective, part of the time the problem that you were dealing with with a user interface in terms of making it usable was the user figuring out how to do something. That's the most fundamental usability problem is that, okay, they're trying to find that setting or the button or whatever that does the thing that they want. That's hard enough. But there was another kind of problem which was harder, which was, how to get the user to even form the goal of the thing that they wanted to do. That's far, far upstream from figuring out how to do the thing that they wanted to do. And so there's a certain amount of goal forming muscles that develop over time when you're in the presence of solutions to problems that you had before the art of the possible, the people's imaginations about what they could have that's different than what they have today. Those muscles tend to be atrophied and undeveloped in the initial state. They grow pretty rapidly, don't they?
Gus Miranda (00:37:07): For sure. And the internet existed, so you could go to a clothing store website or curtain website and you could filter about all kinds of different things. And if you can have that for buying curtains, why shouldn't you be able to have it for triaging clients when the ladder is arguably as important.
Rob Collie (00:37:23): Yeah, almost as important as buying curtains. Yes, 98%.
Gus Miranda (00:37:30): So yeah, I think some people come in and they think maybe there'll be a system that works any website and then when they don't get it and they get frustrated and there's some learned helplessness there. And that's when I think the people I worked for, they're child youth workers, counselors, they're stressed out enough without having to figure out why their stats are wrong or where their data entry went wrong or how to find clients for their groups or trying to work through their wait list without having to work on clunky technology and having to wait three or four weeks to get an answer to something that should be simple.
Rob Collie (00:38:01): So when we were first talking about doing this podcast, we talking about there was some Power Query you'd written that saves the world?
Gus Miranda (00:38:08): I'd say Christmas.
Rob Collie (00:38:08): Okay.
Gus Miranda (00:38:09): I don't know if you guys heard of the Kronos Hack. Kronos is an HR software company. They take care of the vacation time and time off requests.
Rob Collie (00:38:17): Yeah, we have one of those. It's not Kronos. I think they're called PEOS in the United States, but yes. So I've never heard of Kronos, but I understand the system that this is.
Gus Miranda (00:38:29): Yeah, and so their Cloud got hacked. So basically every company that used it lost access to their HR data. And this included Tesla too. There were a bunch of big companies that were using it. We were all holding out that Elon Musk would just pay the ransomware bill and save us all. They called me in a meeting and they're like, "Hey listen, Christmas might be canceled because of this and that. You have to figure out a way to pay everyone more or less accurately by Christmas time." What they had to do was, there's another system that takes care of the payroll per se, but they have to roll in the vacation requests and the vacation times and the time off and all that from Kronos. But they also have to get everybody's time cards in. I've gotten pretty good over the years at data entry forms in Excel where you use data validation stuff like that. So I was like, okay, I'll do something like that that'll be more or less consistent. Nonstop meetings about-
Rob Collie (00:39:16): Crisis meetings.
Gus Miranda (00:39:17): Yeah, exactly.
Rob Collie (00:39:18): We need to make this cinematic.
Gus Miranda (00:39:20): Even though it was all over Teams, there were still kettles going off and papers flying all over the place and alarms ringing for some reason.
Rob Collie (00:39:28): So do you think this movie we're recapping right now, do you think it's an animated movie? Is it like Dr. Suess, the Grinch Who Stole Christmas? Is it the Grinch sitting on Mount Crumpet that has hacked the Kronos servers to cancel Christmas? Is that what's going on? Or is it more of a modern techno thriller where some, can we say Russians again? Maybe it's the Russians. For a while there, we weren't using the Russians as the villain, but now I think that's back in Vogue.
Gus Miranda (00:39:54): I don't think it was Russians at the time.
Rob Collie (00:39:55): Which genre of movie is this? Because we need to think of it as a movie.
Gus Miranda (00:40:00): I'd say hybrid. You would have the live action, but then the steam flying out people's ears and stuff would be animated.
Rob Collie (00:40:05): So you're thinking maybe the live action Grinch, like the Jim Carrey Grinch?
Gus Miranda (00:40:10): But way better.
Rob Collie (00:40:11): Way better? Man, I don't know. That movie was funny.
Gus Miranda (00:40:15): I didn't see it, but it seems like something that wouldn't be good. I shouldn't pre-judge though. Maybe I'm thinking of Mike Myers Cat in the Hat. Was that one bad? Or was that-
Rob Collie (00:40:20): I didn't see that one. So maybe, yeah, but I don't want to over endorse it. It's not going to make my list of favorites or anything like that. But Jim Carrey was made to play the Grinch. I mean, he's pretty good and they stayed pretty true to it. Anyway, I don't want get too far off track. Okay, so the Grinch Hackers have endangered Christmas and down in Whoville, there's all these intense meetings going on and what are you doing? You're just sitting there calmly.
Gus Miranda (00:40:50): Like, I got this.
Rob Collie (00:40:51): Yeah.
Gus Miranda (00:40:52): Everybody's panicking for nothing. The hardest part was going to be how we got the timecards in, but luckily there was the software they used actually printed out a template where staff or managers or whatever could put in their hours or whatever, but it wasn't anything near a structure that you needed to actually pump it up to the payroll software. So luckily we had that template. The HR people who I think are used to getting requests like this, "Okay, well we'll just work 16 hours a day over Christmas to get everyone paid." And I'm like, "No, don't say that. Don't worry about it." You know how easy it is to build stuff out of Power Query. It's just pull these in and pivot them and publish them to Power BI. And it didn't have to be perfect, it wasn't exactly the format, but the HR person could just export the text file, change one little column or something and pump it up to the system and it would work.
(00:41:41): So that was good. We did that with one or two timecards and we're like, "All right, so now we're ready to start getting them all." There was one girl on our team got pretty good at using Power Automate. So she wrote a thing where people would email the timecards, they would get pulled into a SharePoint folder, they would pull those up, Power BI would pull those in, transform it, and go up. So I built this using just a couple. As more and more of them started coming in, it started getting slower and slower and all of a sudden, the model, because I was making changes all this time, doing tweaks, it wouldn't load at all. And I thought, "Well this has to be the number of files." So I tried it with just one file.
Rob Collie (00:42:14): So this is the moment in the movie where the hero, you've got a solution, but then there's a last minute glitch. Like in The Abyss, Bud makes it down to the ocean bottom, he's going to disarm this nuke. He made it. Everyone breathes a sigh of relief but then he doesn't know which wire to cut.
Gus Miranda (00:42:33): I'm going to have to stop you right there. I haven't seen The Abyss since I was eight years old and I swear to God, just yesterday I was thinking, "I should watch that movie again cause I don't remember anything about it."
Rob Collie (00:42:41): It's a solid movie.
Gus Miranda (00:42:43): I remember I was impressed about it. I remember the part where they dump them into the liquid oxygen and they would just inhale it in their lungs. I don't remember-
Rob Collie (00:42:49): So I won't spoil anymore, but this is that moment. The model stops running.
Gus Miranda (00:42:53): Okay, it's not working with one file. What if I just enter some data, one cell with a one and it's still not loading to the data model. For people who might not know, your data loads in Power Query, but then if you want to get to the data model, it loads Power Query and then it loads again into the data model. You probably know the technical details of that much better than me.
Rob Collie (00:43:13): Not really.
Gus Miranda (00:43:16): Because I was hoping someone could explain to me why you can't just pull those tables from Power Query into the data model without having to reload them. And so I start looking online, I find a a help page that says, "Help My Data Model Is Stuck on Evaluate." And they're like, "Oh you just have to go back to an older version." But no matter what version I went back to, nothing was loading. What I did, because I realized it was something on my computer. I would just send a PBIX file to my manager, she would load it and it worked in five minutes. It would go up to the service and the day was saved. So yeah, basically nobody had to work over Christmas. Nobody had to pull 16 hour shifts to enter data from timecards into a title or structure that they would upload through a payroll system and it kind of worked.But what really bothered me is during the crunch when everybody was waiting to see if this thing would work, somebody said, "Hey, if this thing's going to take three hours to load every single time, I don't know if it's going to be usable." And it just bugged me so much, because I wanted it to look easy. I wanted it to look like I had everything in control the whole time. That one little comment ruined that ruined the aesthetic. It was kind of like, you remember The Rumble in the Jungle? George Foreman was going down and Muhammad Ali had his hand cocked to throw in one more punch. He like, "No, no, I'm just going to let him fall."
Rob Collie (00:44:24): That's a good metaphor, yeah. He's winding up, winding up, winding up and just like, "I just don't even bother." So when this is all said and done, did the entire organization throw a parade for you? Were you carried around the hallways on people's shoulders? Everyone chanting Gus, Gus, Gus.
Gus Miranda (00:44:41): There're no hallways anymore, everything's digital. No, but for sure there was recognition. It wasn't just me that worked hard. Everyone like HR and payroll, they were with me the whole time, keeping everyone up to date, making sure that things got uploaded. And they would do a lot of the error checking too. So we would set up a list of what managers should submit and compare that with the managers for whose employees we had data. And so they would go out and they would hunt down all these people and say, hey, come on. Use the right template. Don't just write it on the napkin and stand it and send it to us or whatever they were doing.
Rob Collie (00:45:11): So there was a task force?
Gus Miranda (00:45:13): And no, we did get a little bit of recognition. There were gift baskets and the staff appreciation event was a few months after that. So we got little something.
Rob Collie (00:45:22): Power Query saved Christmas. I love it. Yeah, that needs to be a nerd movie. I think we could maybe pull it off.
Gus Miranda (00:45:29): There was some big companies who for months and months after that they couldn't get their payroll right. Because it actually it took more than a month to get it resolved where you could actually use Kronos again even after that. Because a lot of people had solutions that were probably more ad hoc than ours. They would just pay people whatever they got paid the week before and then they would figure out the vacation, try and deduct it, but it didn't add up right. So employees would be complaining and finance departments would be backed up for months and months after that. But because we already managed to catch it at the front end.
Rob Collie (00:46:04): Something about data people that I find both, and I count myself in this group, unfortunate and admirable at the same time, we're not really specialists in extracting max value for what it is that we do. So think about the solution that you had at that point in time for this problem. If you could have gone to market with that, you could have made jillions of dollars rescuing organizations. If you've been the smoke jumping secret operative that just parachutes into organizations and solves their payroll problem temporarily. We just don't really think that way. And I certainly wouldn't be able to execute on that plan even if the idea came to me.
Gus Miranda (00:46:41): It also depends, maybe a lot of people didn't have the option to do that upload to their payroll system. Maybe their payroll system was Kronos. And so I don't know if Kronos does payroll per se, but if everything is tied into Kronos-
Rob Collie (00:46:54): And there was no Christmas.
Gus Miranda (00:46:55): There was one consultant who worked either with Kronos stuff or with the payroll system who was on the team who just told everyone on the team, "Wow, that guy's amazing." Which is always nice to hear. But I'm not the best at monetizing it. I do only work four days a week on my regular job. So every now and then a little side gig where I can use my Power Query skills to bring a little bit extra.
Rob Collie (00:47:16): And bring peace and harmony. Because if Power Query can save Christmas, what can't it do?
Gus Miranda (00:47:21): Yeah. There was actually another example too that I was thinking about. The government here implemented at one point vaccination mandates. So we had to know for everyone in the organization who had one vaccine or two and after a certain day if they didn't have their second vaccine, we had to put them on leave and people had no idea how they were going to figure this out. And I jumped in because I'd never used row-level security before. I'd seen the video and I thought it would be hard, but it was so easy. It's all based on their emails. So HR just exported a file of everybody and their manager, I transformed that a little bit, and HR also had info about everybody and their vaccination status. So we set up the row-level security and every manager could go in and adjust their reports.
Rob Collie (00:48:07): Well, you just said it's so easy. I got the Guns and Roses song It's So Easy in my head.
Gus Miranda (00:48:12): It's not their best, but-
Rob Collie (00:48:14): It's definitely not their best. Imagine that scene, the sequence in the movie, the montage where you're building this solution to save Christmas and then you haven't reached the crisis point of it not refreshing. That comes later. It's So Easy, it might be the song that plays during that montage.
Gus Miranda (00:48:30): Interesting thing with Guns and Roses. There's a section of the song Coma, which is a 10 minute song they had at the end of one of their albums that reprises that. But it sounds better than It's So Easy, I think.
Rob Collie (00:48:39): Oh really? Was this on the Chinese Democracy album that took 100 years to release?
Gus Miranda (00:48:45): No, it was on the Use Your Illusion, it was like [inaudible 00:48:48].
Rob Collie (00:48:47): Really?
Gus Miranda (00:48:48): Yeah.
Rob Collie (00:48:48): Oh, okay.
Gus Miranda (00:48:50): I think it was the very last song on Use You Illusion two. So by the time you get there you're like, I'm not going to stick around for a a 10 minute song.
Rob Collie (00:48:56): Gosh, you've got impressive range, man. You hadn't seen the Jim Carrey Grinch. But other than that, I mean, you've kept up with my random sampling of really out of date references. Has there been a pop culture reference that isn't at least 20 years old? I think even the Grinch is 20 years old at this point.
Gus Miranda (00:49:16): Wow. Guns and Roses was 20 years old already. It's crazy to think that.
Rob Collie (00:49:22): Dude, Guns and Roses original album hit in 1988.
Gus Miranda (00:49:27): 1988. Yeah, I was seven at the time. The thing with that original album is it holds up so well, I think. You don't imagine it coming out in '88, you imagine it coming out in the nineties or something. But even Use Your Illusion is probably more than 20 years ago now, '92 I think.
Rob Collie (00:49:40): Yeah. We're talking about Terminator two, part of the soundtrack from Terminator two, kind of unbelievable.
Gus Miranda (00:49:45): When did The Abyss come out?
Rob Collie (00:49:46): Probably around the same time as the First Guns and Roses. Probably '88 or earlier, might have even been earlier. And I think The Abyss was the beginning of the end for James Cameron making real movies. Once he got into that underwater shit, he's like, "Oh God, this is way better than anything else. So let's just go make underwater, find the Bismarck and find the Titanic movies from now on. That's all I'm going to do."
Gus Miranda (00:50:09): A lot of people talk about American cultural imperialism, but three of the four references you've made, I think were Canadians. There's James Cameron, Jim Carrey. It was two out of three, I guess.
Rob Collie (00:50:19): And I've been impressed.
Gus Miranda (00:50:21): Ken Puls is Canadian.
Rob Collie (00:50:22): Yes he is. Yes he is. He continues the trend. He's right up there with Cameron. He's the James Cameron of-
Gus Miranda (00:50:29): Power Query.
Rob Collie (00:50:30): Of Power Query. Or is it you? Does Ken have a Hollywood level story and because it all took place, by the way, remote.
Gus Miranda (00:50:41): Oh yeah, we could record the whole thing on-
Rob Collie (00:50:41): We could do this movie, we could shoot it on Teams.
Gus Miranda (00:50:44): With a little budget.
Rob Collie (00:50:45): We're going to need some real actors though. We can have a couple people play themselves. Clearly, you need to play yourself in this movie, but the people that come in with the real seriousness who bring the problem to the forefront, the managers who are clearly crushed. We might need Tom Cruise.
Gus Miranda (00:51:01): Probably 80 to 90% of people I work with are women. So I think it would be mostly female cast.
Rob Collie (00:51:09): Okay. All right. So we got to get Tom Cruise in there as the villain maybe, or maybe it is Jim Carrey.
Gus Miranda (00:51:14): Yeah. He wouldn't make a bad hacker, I guess.
Rob Collie (00:51:17): Oh, I think he'd make an amazing hacker. Did you ever see the terrible movie Blown Away with Tommy Lee Jones?
Gus Miranda (00:51:21): Oh yeah.
Rob Collie (00:51:23): Oh see, I can't believe this. I can't believe you seen it. Him in Blown Away was the most caricature over the top dumb villain of all time. And then I think he played the same character when he was in the Batman movie. Tommy Lee Jones needs to just be Tommy Lee Jones. He doesn't have a range. Don't push him outside of playing himself.
Gus Miranda (00:51:43): I remember when I saw it, it was around the same time that Speed came out and there were all these comparisons. Speed is great and this movie sucks. I thought I would like Blown Away better because there was one line where Tom Lee Jones says something like, "We'll build a world out of anarchy." And I was super into anarchism at the time, so I thought it would be a deep philosophical movie, but not at all.
Rob Collie (00:52:02): It didn't deliver, did it?
Gus Miranda (00:52:03): No.
Rob Collie (00:52:03): He didn't have an alternative social construct to present.
Gus Miranda (00:52:06): He was more of a [inaudible 00:52:12], than an anarchist.
Rob Collie (00:52:12): All right. I'm not going to push my luck. If you've seen Blown Away, that's me, you, and five other people.
Gus Miranda (00:52:17): It's like with The Abyss, I can barely remember something I saw as a kid.
Rob Collie (00:52:21): Unlike Blown Away, The Abyss was worth rewatching. So I've seen it a bunch. I have not seen Blown away more than... I'm not even sure I paid attention for a hundred percent of the first viewing. It was so poor. And to have seen a movie that long ago and be that young and see a movie and think it sucked. Imagine how it would suck if you watched it today.
Gus Miranda (00:52:41): Yeah. When you're pretty young, a few explosions, some flashy camera work and you think it's great.
Rob Collie (00:52:45): Yeah, all right. So anything else we should talk about?
Gus Miranda (00:52:47): I did mention that around the time I got started with Power BI, I was super into sankey diagrams because I thought it would be a great way to show our client flow. Because one of the big questions was how many clients move from an intensive service to a less intensive service or from a regular service to a more intensive service. Because it could give us some clues about whether we were triaging them right. Were we really capturing the intensity of their problems at the start, or were we underestimating and they would end up having to go through a convoluted set of programs to get what they needed? And the sankey diagram does that. You have a list of things on one side and there are all these wisps.
Rob Collie (00:53:22): I think it was like river flows. Rivers splitting into tributaries and combining. And it's a really cool little flow diagram. Very often used to represent website traffic, for instance.
Gus Miranda (00:53:32): I figured out in Power Query that I could reference the same table. So I had the table with all the kids and what programs they were in. And then I could reference that same table and I could get every other program they were in for each row and I could do it backwards and forwards or I could filter it out after and do just the one that came after. So I did that. I had the two columns for program one, program two, and they built the sankey and it's all beautiful.
(00:53:57): And at first it makes no sense, it's like a million different things, but then you filter it down. If you want to look at just one program and see everyone went in another program, you could do that. If you want to just see everyone who went to a certain program and where they came from, it was great. And I'm looking at this really cool visualization. I'm like, no one's ever going to look at this thing except me, because it's not really what the decision makers want. Fancy visuals, they just want some hard numbers that they can build decisions on. Because of that obsession, I figured out this cool self-referential thing.
Rob Collie (00:54:29): Self-referential in Power Query. So I was wondering if this was a parent-child thing going on in DAX, the path function or whatever, which I've used once.
Gus Miranda (00:54:38): No, I have no idea what the path function is. Is that the one used for hierarchies?
Rob Collie (00:54:42): Yeah. Again, self-reference the same table. And sankey is that still only doable via custom visual?
Gus Miranda (00:54:49): I think so, but I never really thought there was a big difference between... I mean obviously there's the big difference in terms of compatibility of all the features, but in terms of just clicking a button and saying, I want to use this visual, getting it from a popup window instead of from the little chart of the side. I've never seen a huge difference.
Rob Collie (00:55:05): So a huge difference between built in visuals and custom visuals?
Gus Miranda (00:55:09): A big difference in how you access them, so it being a custom visual.
Rob Collie (00:55:12): To me the difference between the built in visuals and custom visuals is usually that the custom visual is built out just to the extent that you can get deep into it and get frustrated. Because it won't do exactly what you need it to do. It's like, "Oh come on, I can't change the left to right direction or whatever." But really what I'm really mostly interested in is just saying the word sankey as many times as I can.
Gus Miranda (00:55:35): It's very beautiful, but maybe not very utilitarian.
Rob Collie (00:55:38): There's something you said which was like, you know how it is, the higher level management decision making type. They don't want something pretty, I'm thinking, I don't know. Oftentimes aren't the ones that are pushing us to use dials and oil gauges and things like the famous scene and fight club where the manager keeps asking if you can get the icon and cornflower blue, are you up on that reference as well? I can't imagine that one would be one you've missed.
Gus Miranda (00:56:04): Oh no, no, for sure. I've watched it and I've read the books too. He actually put out Fight Club two and three in comic books.
Rob Collie (00:56:10): Have you seen his video, the humor video Fight Club for Kids?
Gus Miranda (00:56:14): No. No, I haven't seen it.
Rob Collie (00:56:15): Oh, you need to look up Fight Club for Kids. That's a funny video. He met a friend with spiky hair. He met a friend who really wasn't there.
Gus Miranda (00:56:29): It sounds very funny.
Rob Collie (00:56:30): Yeah, definitely look up Fight Club for Kids. It's a short video and it's hilarious. Yeah, sometimes aesthetics are the draw.
Gus Miranda (00:56:37): I find that they're the draw in terms of them maybe suggesting what product we go with or suggesting who we get a demo from, but they're not necessarily the draw in terms of what they end up using.
Rob Collie (00:56:49): So that's comforting to me. In the end. I want to work with managers who want to be impactful, want to be useful, want simplicity as opposed to want to be, "Can I get the icon in cornflower blue?"
Gus Miranda (00:57:03): I think when you're in a nonprofit it's probably a stripped down what you want. You don't necessarily go for the bells and whistles. You want the basics.
Rob Collie (00:57:14): I just get the image of the manager and Fight Club just stuck in my head now. So Power Query saves Christmas, Sankey, just sankey. And I think we should take the word diagram off the end of sankey, refer to it as a sankey
Gus Miranda (00:57:29): Because we're going to make it so popular that everybody's going to just know what it is.
Rob Collie (00:57:32): We're going to popularize it, yeah. So a sankey or two. We want sentences like that to start to proliferate. What if we threw a sankey in here?
Gus Miranda (00:57:38): Could I get that in the Sankey?
Rob Collie (00:57:41): Can I get that sankey in cornflower blue? Well listen man, I'm impressed. Who's saved a major holiday with Power BI? I mean-
Gus Miranda (00:57:51): Probably more people than you think.
Rob Collie (00:57:53): Probably. But we're only going to make one movie, starring Gus Miranda in his action packed debut
Gus Miranda (00:58:00): Starring Gus Miranda. Based on the story by Gus Miranda.
Rob Collie (00:58:02): Yes, that's right. That's right. Yeah.
Gus Miranda (00:58:04): Based on the [inaudible 00:58:05].
Rob Collie (00:58:05): Inspired by true events. Very loosely. Of course, to make it Hollywood there will be explosions.
Gus Miranda (00:58:13): Yeah, I don't know. Actually when started talking about Blown Away, I kind of thought maybe we won't go with Hollywood. We'll go with independent financing. We'll get a little bit from Japan, a little bit from Europe.
Rob Collie (00:58:23): Like a Swinger's movie. And I don't mean culture swingers, I mean the movie Swingers that was self-financed, filmed on a gorilla budget.
Gus Miranda (00:58:32): I'm probably thinking more of movies that even you and me wouldn't be able to reference.
Rob Collie (00:58:35): Okay. So it's going to be a French movie.
Gus Miranda (00:58:38): It's movies where the start, they mention all these French funders and Kavalan funders and Romanian funders, but then it's all in English for some weird reason.
Rob Collie (00:58:47): Yeah, you're right. I haven't seen this movie. We'll shoe string it together. Casting is going to be tough.
Gus Miranda (00:58:53): Especially with the crappy budget we're going to have.
Rob Collie (00:58:55): We're need people to do this pro bono-
Gus Miranda (00:58:58): On scale.
Rob Collie (00:58:58): Just because it's good for the world. Yeah. Power BI community I'm sure has a reasonable subsection of acting talent.
Gus Miranda (00:59:06): Probably, yeah.
Rob Collie (00:59:08): At least at the high school level. I mean, we need at least that. So we'll figure it out. We can get this thing made anyway. Anyway, it's been a lot of fun. It's no secret that oftentimes this is just the excuse to get to know people.
Gus Miranda (00:59:18): No, it was nice to meet you in person. It meant a lot to me when you guys published that article way back when. It made me feel like maybe I knew what I was doing. I was on the right path.
Rob Collie (00:59:25): We knew someday, someday you were going to do something great. You were going to save a major holiday. We just didn't know what it was yet.
Gus Miranda (00:59:32): You didn't know which one it was going to be. They were very clinical, these hackers, to pick the time just before Christmas to do this because they knew everyone's going to be desperate.
Rob Collie (00:59:41): You heard about this, the hack that where they stole billions of dollars from, I forget what government.
Gus Miranda (00:59:47): There have been tons of them and well I know for a lot of the ransomware ones, they just sent a very polite letter to people saying, "Hey, this isn't your fault. We didn't hack you, we hacked your provider because we found a weakness. But you want your data back, it's going to cost you this much." It's just the service they're providing.
Rob Collie (01:00:03): Did people get their data back if they paid?
Gus Miranda (01:00:06): I have no idea. I have no idea if anyone paid.
Rob Collie (01:00:08): When you've got multiple victims, I bet the probability that getting what you want when you pay goes up. Word needs to get around that if you pay, you get your shit back. When there's only one victim, you pay and they say, "Well anyone that would pay a million dollars for their data back would clearly pay two."
Gus Miranda (01:00:26): Right, yeah.
Rob Collie (01:00:28): The game theory is just to ask for more at that point.
Gus Miranda (01:00:30): That's why they go for the Cloud services because there are so many different parties invested. Somebody's going to pay.
Rob Collie (01:00:38): Yeah. So ironically it comes back around, even though the move for the hacker is to request more money after getting it the first time, if it's one victim. Once you realize that, then even the hackers want to be in a multi-victim environment because they know that that's how they're actually going to get paid. So you don't want to be in the situation where you have the upper hand because then no one pays. Well fortunately never been ransomwared, but I know who to call. If we get ransomwared, the Mission Impossible tune starts to play. And
Gus Miranda (01:01:11): I would've mention that P3 has to call some guy who works in a nonprofit to do power purgery.
Rob Collie (01:01:17): Well, I'm just talking about it on the general.
Gus Miranda (01:01:20): Universally, if the world gets ransomwared.
Rob Collie (01:01:23): That's right. Do you smoke? Okay. So for this movie you're going to have to pretend.
Gus Miranda (01:01:27): Should I get a eye patch and a cigar so I could be like the-
Rob Collie (01:01:30): No, no, no, no. You're sitting there all cool living your international man of mystery life. You're smoking and the call comes in and you go, "I'll be right there." Put the cigarette down and you stub it out and then the music rises and you're off. You're going to have to fake it. We have fake cigarettes. There's fake cigarettes like the guy that stars in Peaky Blinders, he doesn't smoke in real life, but he smokes like 5,000 onscreen cigarettes in the course of this show.
Gus Miranda (01:01:58): Yeah. We were watching that with my wife and we can't believe how much they smoke.
Rob Collie (01:02:02): Yes. I'm sitting there going, I don't smoke and now I want to, It looks so good.
Gus Miranda (01:02:09): For me it's the opposite of those weird stories that can't be true where your parents catch you smoking one, so they take you out and they make you smoke all of them. Just watching him smoke all of them makes me think it's too much. It's too much. I didn't want to see someone smoke another cigarette.
Rob Collie (01:02:22): Really? No, that's not the impact it has on me. I'm like, he does it so much. It must be so tasty.
Gus Miranda (01:02:26): It's true that with cigarettes, it's one of those things where it looks so cool but it smells so bad. I don't understand how both things could exist at the same.
Rob Collie (01:02:35): I think you're just going to have to try it out.
Gus Miranda (01:02:38): I tried it out secondhand for a while and I didn't like it.
Rob Collie (01:02:41): The first Rock joke, cigarettes are so dangerous, they kill people who don't even smoke. And those are legal, but the rest of all these other drugs, those have got to be illegal.
Gus Miranda (01:02:55): Not so much anymore away. It's getting chipped away little by little.
Rob Collie (01:03:00): It's getting chipped away. I know I can't handle weed. I can't touch it. You want to ruin my night? Peer pressure me into doing some weed.
Speaker 4 (01:03:10): I've been that peer pressure.
Rob Collie (01:03:11): Yeah, but I think in that era, Luke, I was okay with it. It turned on me at some point, and-
Gus Miranda (01:03:18): I wish you'd brought this up earlier on the podcast. I think that story might have been right up there with the ones about people being mean to each other at Microsoft.
Rob Collie (01:03:26): I wanted to see the band Tool live and I was really looking forward to it. I paid the exorbitant auction rate to get good seats and you can only buy two tickets. You can't buy one. You can't buy one, you got to buy two, unfortunately. I don't know if anyone want to wants to go to tool with me. So I was like, "Oh, I know." So I know someone. I invited him. He's like, "Yeah, I'll go to that. I've never heard him, but I'll, I'll go." so we get in the car, we're going down there and he goes, "Hey, we've talked about this, Rob, I know you don't do edibles. I know you don't do that stuff, because it's bad for you." And I go, "That's right." He goes, "However." And the story goes, "I made these microdose gummies on my own. I made these microdose gummies just for you. Just for this occasion."
(01:04:13): Feel the pressure building here. And I go, "Micro, huh?" He goes, "Yeah." I go, "I'll tell you what, I'll eat half of one. I'll eat half of one of your little tiny microdose gummies." Absolutely ruined the entire experience. We're in the second row or something and I'm just staring at my shoes for three hours in agony, reliving all of the terrible things that have happened in my life and all the things that were actually relatively normal, but now I'm seeing them in a darker, more sinister, twisted light.
(01:04:43): It's like a three hour trip to hell. And at one point there's this long guitar rift that's going on, it seems like for hours. And I'm looking at this guy next to me going, "Is this still the same song?" And we tallied it up afterwards that he had consumed 25 times the dose that night that I did, and I was wrecked. I don't really have a dog in this fight. I think this stuff should be legal. I don't see any point in criminalizing it and ruining people's lives over it, but I'm not exactly an advocate either. I appreciate you spending a couple hours with us. It's been a lot of fun.
Gus Miranda (01:05:22): It's great to meet you.
Speaker 3 (01:05:23): Thanks for listening to The Raw Data by P3 Adaptive Podcast. Let the experts at P3 Adaptive help your business. Just go to p3adaptive.com. Have a data day.
Sign up to receive email updates
Enter your name and email address below and I'll send you periodic updates about the podcast.