Good morning. Thank you for coming after the parties last night. Very exciting to be here with you all. We have such a cool, not panel circle of speakers today, very excited to talk about ESG, efficient or not. Who knows what ESG, those three letters stand for in this context. Shout it out. Great. Thanks. Environmental, social and governance is what those three letters stand for in this session we're running today. And what that really means is we're looking as investors, as startups, at the environmental and social effects of what we do and the governance that underpins that. But it actually has even more nuance and each of our speakers here are going to share what it means for them in their specific investor context. So with that, I'm going to kick off and welcome Tava. So we start with you. What does ESG mean to you in your context? How is it different from another word that people in this room might be interested in? I know I am. Impact. We talk about ESG and impact a lot about startups and investments. What does that mean to you? And how does it, you know, name one decision it helps you make? Yeah. So maybe the first differentiate a little bit between the impact and ESG. We talked a lot about that backstage and it is super interesting, I think. The way we define it is the impact is the potential upside, the good we could do for people and planet. And that's where the real potential is. And we define the ESG more as the risk assessment, like what are the potential negative externalities that we need to take into account. And both those aspects are really critical when making investment decisions. So we invest for impact, but we also really need to make sure we understand. Let's remind, I know you guys were introduced, but yeah. So Norse can VC. We're an impact tech fund based out of Stockholm, Sweden. Amazing. Thanks. So one decision it helps you make? Yeah, it makes us help the actual investment decision. Are we put the money into a startup you want to see both ESG and impact? Definitely. Yeah. Thanks. Thanks. Heidi? Well, for us, it's very similar to what Tava mentioned as well. We do ESG assessment initially when we first kind of meet this startup and we try to figure out whether this is a company we want to invest in. And we do it both so that they do an ESG self-assessment themselves. So we ask them to think about these things. And then we do an internal assessment as well of where we think that they are and how we perceive them at that time. And really, the main thing here is about understanding how thoughtful they are around these issues, how much they've thought about what the environmental impact is, what the social impact is, and how governance looks in their company, for example. So we really is to kind of assess that. But in terms of what, and then same as with Tava, then impact is something that for us comes afterwards where we then figure out what are the KPIs that we measure. And we're a climate tech fund. So for us, it's all going to be climate KPIs that we measure. And then in terms of the decision that it helps us take, it's really about the founders and how value aligned they are with us and with our mission. So we actually really use this to figure out are these the type of founders that we want to work with, that we want to have an upper portfolio that we will be proud of when they are on stage and they're, you know, talking to thousands of people. And yeah, really try to understand that that is this the kind of company that we want to back based on how thoughtful they are around this and how core impact and the risks are for them and how much they take this into consideration when building the company. And remind us who you're representing today. And we're from Pale Blue Dot. Pale Blue Dot is a climate tech fund based in Sweden, but we invest around Europe and US. And how big is the gap lately between the questions you want to ask and the answers you're getting from entrepreneurs. I know we have a mix of investors and entrepreneurs in the audience, but you know, if they go, oh my gosh, I don't know the answer to that question. Is that more common or less common? Is that okay? I, in my experience, it's become a lot more common for people to be a lot more thoughtful around these issues. And actually, it becomes clear when you ask these questions that they've thought a lot about it and they've thought about what type of company they want to build. And of course, you know, in a climate space, they should have been pretty thoughtful on the environment and how this impacts the climate. So it is pretty common. But there are always things where they haven't maybe thought about the social impact of what they're doing. And you know, there's a lot of things that come up. And you do notice that some people are taking a back of it when you ask them to make this type of assessment at an early stage because they are so early that they haven't maybe been thoughtful yet. So you're on the journey together, right? I mean, I think this is what we're all learning as we go. Albert, let us know what you think about these topics, ESG impact and who you're here with today. So I'm a partner at Union Square Ventures. And historically, we have invested in all things digital. But since 2020, we also have a climate fund. But even predating the climate fund, we have always thought about the question of value creation. And for a lot of people, value creation has become the stock market value of a company. Instead of thinking about value creation broadly for society as a first step. So like Philip Morris is a very valuable company, but actually has probably destroyed a lot of value in the world. Purdue Pharma was a very valuable company that probably destroyed a lot of value in the world. And so we have always come at this through this question of, doesn't entrepreneur care first and foremost that there's value being created before they capture value? And even long before we came to climate, for instance, we invested in a lot of marketplaces. And we were one of the few investors who were very focused on what the take rate in the marketplace is. Like how do you have a 20% take rate, a 30% take rate, or maybe only a 5% take rate? So one of our early investments was Etsy. And a lot of other investors passed on Etsy because Etsy's take rate was 5% at the time.  And we're like, no, actually, we think marketplaces should have a low take rate. They should give a lot of the value to the participants in the marketplace who are creating that value in the first place. And so coming back to the original. So just on take rate for the audience. So Etsy has a platform. The market place volume that goes to the company. Got it. So they were sharing more with the people actually using the platform. Got it. And so while we don't have, even in our climate fund, we don't have any formal ESG criteria, we didn't raise from any investors that forced us to apply ESG criteria, that whole thought process of how does something create value in the world in the first place? I think it's something that's always been with us. So create more create more value than you destroy it. But you know, you're investing, hopefully investing early stage.  Well, Heidi, I think said something that really resonated with me, which is, which is that, oh, Mike is back, which is a lot is unknowable, but you can know whether the entrepreneur seems to care about it or not, authentically care about it or not. And so we do spend a lot of our time when we get to no entrepreneurs on, is this somebody who thinks climate is the latest way to get rich quick? Or is this somebody who actually genuinely cares about solving the climate crisis? So for any entrepreneurs in the audience here, that sometimes feels like to test that, are you genuine? A lot of questions, a lot of data to collect, a lot of reporting, and even for fund managers now working in climate and social impact, there's a lot more requirements, basically, of what data you need to disclose on your portfolio and how you engage. What does that look like? How are you helping entrepreneurs manage that? And what do you think needs to happen next? The title of this session is ESG efficient or not? That's a big question. And really depends on the reporting requirements, which can be helpful, but can be really time consuming. Who wants to jump in on that one? Well, maybe I'll just start by saying, we made a conscious decision not to raise an ESG fund. And we had some people who wanted to give us money for the climate fund, and they sent us their ESG survey, we're like, we're not filling that out. So for us, it was sort of a conscious decision that we didn't want to do that. Okay, so for these investors, any entrepreneurs who do get a survey, what do you think they should fill it out? We're probably on the other side of the spectrum. In general, we really think that transparency and comparability is a great thing, but also fully recognize the burden that it could potentially put on the entrepreneurs. So the way we do it is that our due diligence is primarily focused on the impact, so the potential upside. And they're together with the founders to find kind of what this long term impact vision for the company, what would the world look like when they have achieved their goals, and what are the shorter term KPIs that we could track to see how they get there. And we actually see it as, you know, something that they can really use as a competitive advantage to have that clear. It can help them recruit talent. It can help them communicate with customers and also too many investors actually. So we see it as a strength, even though it is a little bit of an exercise to do, but we're happy to do that together with them. Amazing partnering with them. We're actually kind of in between here, I would say that I think I really against having to put the burden on the startups and really don't like having to ask them to report things. And in fact, when we raised the fund, we actually didn't take some LPs that had really hard reporting requirements on ESG criteria, because we felt that it's not really fair on early stage founders. And all of us who started the fund, we've all been there ourselves. We've all built startups ourselves, and we know, you know, how many other things that you have to deal with. And then having very specific things they report on at a very early stage, it does create quite a burden. But saying that, we're still trying to find out the balance and trying to figure out what is what we can ask for. The one thing I do like, and that we're kind of testing right now is having some kind of a prompt, where we give them an idea of what they could be considering, like, oh, are you thinking about a bike to work scheme? Or are you thinking about a parental leave policy? And then hopefully we can create the resources to help them with that. So it's more of a prompt, rather than it is about you reporting to us and then us using those figures to report to our LPs. And that we don't really do. So yeah, we're trying to figure out what the balance still is, to be honest. And I think it's, it's tricky because I think it's so important for them to think about these things. But at the same time, they should be able to do it at their pace and they should be able to do what's reasonable for the stage they're at. And a lot of the reporting requirements we're getting from like SFDR and things like, you know, the new European regulations about reporting for especially companies in the environmental space, they're just, they're, they're not, in my opinion, there is just too much for early stage startups. And it just doesn't feel fair that if you're working within climate or you're working in the environmental space, you have so much harder reporting regulations if you're trying to do something right, compared to if you're kind of general fund or general startup. And that's something that I'm a little bit against. So it's a tough balance. Yeah, so what I hear is, you know, make sure that they're not starting with a clean, you know, that they have something to work with, not just an empty sheet of paper. And then, yeah, I mean, how is it, we should be incentivizing people to do more good and less bad, no matter what kind of company or investor. You wanted to jump in again. Yeah, just adding one thing to what Heidi said. So we're really, really looking for companies where the impact is at the very core of the business operations. So it's actually the KPIs that we're primarily looking at are things that is very natural for them to track. So it's not that we're adding things on the sides, it's actually very much incorporated into the business model, which I think is a good differentiation to make. So as the company grows, their impact will grow and therefore sort of like your indicators of success are matched. Exactly. But I know you were talking when we kicked this discussion off about you sort of bringing investors into the fund, but what's the relationship with startups you're looking at right now? How do you feel about this, you know, with the questions you ask and the level of detail you need? Yeah, I mean, I think it's important to embed this in sort of a larger context. So in 1970, famously, Milton Friedman wrote this op-ed in the New York Times saying the only responsibility of a company is to increase value for its shareholders, right? And so I think we were coming off a time period where things got ever more focused on financial success metrics as the sole metrics of a company. And I think it's very important to swing the pendulum back towards understanding that corporations have a broad responsibility to their employees, to their communities, to society at large. The question then becomes, and that's I think what we're discussing here is, how important is measurement of that versus genuinely caring and so, you know, and at what stage do you do how much, right? So if your TPG and you raise funds in like $3 billion increments, like to have some people do ESG reporting is like trivial, right? If your union square ventures and our climate funds $160 million, do we really want to spend a lot of money on ESG reporting? It's a climate fund, right? So almost by definition, the things we're investing in are companies that are going to have an impact on the climate.  And so do we then want those companies to have to fill out some form that, and those forms, I think have largely been thought up by bureaucrats, sorry for using that as a slightly derogatory term, I don't mean it that way, but it's not connected to the realities of startups. Yeah. We've been talking a lot about ESG and impact conceptually. I'd love to go around and just hear one deal you've done, one company that inspires you, one relationship where you've gone on a journey of ESG and impact. So just talk me through one company. So the room here is, what does this look like in practice? Anybody want to kick off? It could be good, bad, or ugly by the way. It doesn't have to be your favorite. It could be your least favorite you've ever come across. I just want to throw out an anecdote because I think it illustrates how hard it is to think about some of these things.    Were you ever smart in the middle? No, I don't think so. Well, maybe briefly when we had our IPO and I guess everyone was like, yeah that was really smart. So my point is, I think impact is actually really hard to understand. And we're dealing with climate, for example, a lot of us here and like there are plenty of people who are saying EVs are bad because you're going to need batteries. Electric vehicles. Electric vehicles and the batteries are going to require rare earths, which are being mined in China in really bad circumstances. And for example, a lot of ESG funds cannot invest in mining companies because mining companies often have bad labor practice, bad governance problems, and yet we need a lot more mining. So these problems are actually a lot harder, I think, than one might sort of just think at surface level. Well, you brought up a really important topic that we might get to in the other examples too, which is ESG, three letters, but with a lot of interdependencies, you know, good governance underpins everything in a company. Environmental success might have social implications, positive or negative, and vice versa. If you're trying to save lives, it might take up some more carbon. I mean, there's some, especially when we talk about infrastructure plays and emerging markets, anyone else want to jump in with an example and maybe even a feature of that tension that is there sometimes? I could highlight one company that I want to really highlight as a positive, which is one of our portfolio companies, N-Ride. They're providing- What is it, N-Ride? N-Ride. They're providing autonomous electric trucks that can really change the transformation, the transportation sector. And the one trade-off that you could look at there is, of course, sort of the electrification that you've already mentioned with the components going into that, but also that the autonomous part will, of course, make some people lose jobs, and we're very certain that there will be other more meaningful jobs, but that is, of course, a trade-off in the short-term perspective specifically. So that's something we thought about when we made that investment, obviously backers, so really, really positive, but something we took into consideration. Yeah, watch and mitigate as you go. Exactly. But you thought the climate benefits of this investment, based on what information were they able to give you? Basically, so the electrification part, of course, if you compare that to the diesel trucks, which is mainly the fuel that is used today. So, yeah, that was a trade-off. Thanks. In terms of the trade-offs, it is something that we're constantly discussing, and it's always, it's very complicated, because we are a climate tech fund, but then you do have to think about, I mean, as an example, this is not an investment we did, but we looked at COCO. We looked at doing a company that does, essentially, an artificially made COCO, and then we thought about the fact that, okay, but if we make this investment, it's great for the environment, because COCO farming isn't necessarily so great and sustainable, or, however, what is the impact when there's five, six million COCO farmers on certain countries where it's the biggest export? What is the impact if we kind of pull the plug very quickly, for example? And it's very hard questions, and it's not necessarily on our shoulders always to take that, but I think the considerations that we should have, and this goes with the kind of thoughtfulness around the decision-making that, you know, we do try to kind of dig into these things and think, oh, but what would happen? Is it good in the long run? What are the short term consequences? But are there better long-term consequences? And, yeah, these are constant discussions that we have at our fund to try to kind of think out and think bigger, and, you know, is this actually good for humanity, or is sugar good for humanity? Is it what is the social impact of us investing in sugar in general? Like, it's the very big questions. But to give an example from our portfolio, not on the trade-off, but more in terms of the kind of impact and impact measurement, one of our portfolio companies is Betta Fish that you can all taste here today as well. And they make a sandwich, they make a tuna sandwich out of seaweed. And for us, the impact here was the seaweed. We, as a fund, we were super excited about seaweed because seaweed de-acidifies oceans, it sequesters more carbon than land-based plants. Like, it was a no-brainer to use this ingredient. So for us, then, then making a product and a product that people love is amazing, and it's like an extra bonus. But from an impact's perspective, we are really thinking about the demand they're creating for seaweed and how that actually impacts the environment. So it's not really even that related to the end product in that sense, it's just an example how we kind of view impact. And we went really deep down into seaweed. And I think most people here trying the sandwich would not even know it's seaweed. But there you go. Now I gave it away. If you haven't tried it yet, go test out whether you think the tuna is seaweed. And is sugar good for humanity? It's going to be a takeaway from this. Definitely not. I think we all agree on that. Well, I wanted to say governance, too, is a complicated topic, right? So we're just in the middle of a deal, which may or may not happen, where the founders have asked for 10 to 1 super-voting shares on the theory that they feel like that they are better stewards of the environmental mission of the company than investors might be. And so, but does that make good governance?  So I think these are complicated questions. And maybe the key thing to take away is that there's a group of people in the world, and I think people represented here on stage who are very serious about these. And then there's a group of people in the world for whom this is like another check the box list. You check all the boxes and you're like, done. Give me ESG money now, please. You know, and I just think we got to avoid that. We got to avoid taking something that comes from a good place and turning into a check the box. Now I'm ready for ESG money. So you raised a really important point just to clarify, because I know there's been a lot of other sessions on we've talked a lot about the products we're investing in. But ESG looks at two things, right? It's about how a company runs, how it treats its employees, diversity, inclusion, right? There are a lot of elements of how a company does what it does and what it produces for the world. And often I think we don't we talk about one or the other, but really just important for everyone here to think, you know, it's about how you treat your employees and the environment through your operations, your practices and the products you create. So if I'm listening to this amazing panel so far, I might think it's a little complicated. This sounds like a treacherous journey. But there's a reason you were here. There's a reason that we are looking at this and asking these questions. What's the opportunity? Like I'm hearing a lot of open questions. Why what's what's the upside of thinking through this for an investor or for an entrepreneur? Yeah, I think I mean, not thinking about this, you will definitely miss out on things. I think if we look into the future, some of the biggest companies out there, we think will have impact at the very core. Those are businesses that are here to stay. They're addressing problems that are unfortunately here to stay in the foreseeable future. And they will have a competitive advantage in terms of attracting talent and consumers and the capital. So I think that's something you should look at into the on the upside part of things. And if you're not assessing the ESG related risk, I think you're also going to be in trouble because you will be building companies that may not be built in the best way and sooner or later that those risks will maternalize, materialize. So I think it's really about really assessing the upside and potential risks. And if you're not doing that, you should start. You should watch out. Excellent. And I can add that we are actually, we don't see ourselves as an impact fund. We don't necessarily see ourselves as an ESG fund either. We are a climate tech fund, but we must think climate tech because we believe this is also the biggest economic opportunity. The whole world has to shift to this in a very short time. We have regulations. We have all these countries saying they're going to be net zero in a very, very short time. And we have a very urgent crisis that we have to solve in a very, very limited time. There's a lot of urgency here at play. So we believe everything's kind of coming together. And we already, since we started the fund, we've already started seeing this on a much broader scale in terms of the rest of the investment landscape, essentially waking up to this. But this is a huge financial opportunity as well. It's for us, this is, it just, it just makes sense in every single aspect for you to think more about impacts. And everyone's going to have to put money into solving these problems because these are the biggest problems we're solving for humanity right now. And if we don't solve that we will not be here. So there really isn't a way to kind of try to get away from this and says, oh, I'm not going to think about impact. I'm not going to think about ESG and I'm not going to think about what I'm actually doing because, you know, if we all do that, we just won't have anything to save anymore. So for me, it's just, it's just such a given in every way that we are looking ahead as these are the biggest problems and therefore the biggest opportunities. Therefore the big, where we're going to be putting most money. So whether you want to look at it from an economic standpoint or from an impact standpoint, the end result is going to be the same. Got it. Thanks. Albert? Couldn't agree more with both of what you said. Climate is the defining challenge for humanity today. I think the other really huge issue out there in the world is the income and wealth distribution and how completely lopsided it has become. And in our core fund, our investment thesis has been to broaden access to knowledge, capital and well-being. The idea being that one of the biggest opportunities is to use digital technology to make the kind of things that have been accessible to wealthy people for a long time, like good education, good health care, good financing to make all those available to everybody in the world. And we think that's another huge problem that's also represents a huge opportunity. And then why should one care about some of these things early on? Well, you should care about diversity early on because if you build a company of 20 dudes, you're not going to be able to hire a great woman because who would want to work there? And so like sometimes founders are like, well, I want to focus on product market fit first. And so I'm not going to pay any attention to any of these other things until I have product market fit. But frankly, if you build a company a certain way and you get to a certain stage, it's too late. You're going to you've set the DNA of the company. And so I think certain things you can wait and others you've just got to deal with from day one. I'm really glad you raised. You know, we talked a lot about climate tech up here, but inequality by biodiversity. We have a bunch of things to deal with. There's a term flying around UK and Europe a bit, the just transition, right? So if we're going to build a world that is climate friendly, let's put it that way, you know, people are going to feel impacts as well. And we need to bring both along on the journey. I think inequality, especially in how we do our investments and in the companies that we back is a really under discussed in ESG and impact as a topic. So thank you for bringing that up. Albert, especially because USV does stuff other than climate, there might be entrepreneurs and investors in the room that wouldn't call themselves any of the tags. They're not just focused on climate opportunities. Maybe they're an entrepreneur who's building something amazing, but it doesn't have impact at its core. Is there still stuff that other that you also talked about how you build your team and all of that? But would you say, you know, pivot completely and just solve a problem that needs solving? Or would you say, what would you say to them, you know, about ESG and impact? The only thing I would say is, if you're going to be an entrepreneur, work on a problem that you genuinely care about. OK, I hope you're all doing that. And bonus points if it's saving the world at the same time. Any other tips for entrepreneurs out there who are, you know, I agree with that. And I think that the main thing here is that, you know, even though as a climate tech fund, it's very obvious that there'll be a positive impact on the climate. I think that every startup, every company we build has an impact.   Some of them are negative impact. Some of them are kind of neutral, but it's good for someone, but it's not a huge impact. And some of them are like, have an enormous impact on the world in some way. But I think the important thing is about being thoughtful around it. I would definitely, you know, if you are thoughtful around what you're building and you're realizing that it's definitely a lot more negative and I don't see a path that this is ever going to be positive, then that should be a red flag and that should be a wake-up call. And the same goes for investors. If investors are investing in these companies, I think the good thing is to do these type of assessments so that you get to a point that you actually feel like, will I be proud of what they build if they truly succeed? And if you've been thoughtful about it and you made that decision and you think it's going to be better in the long run, then I think that's completely fine. But yeah, I think impact is something that, you know, we should talk about and we should measure and we should figure out we should have kind of as part of every company's essentially mission or every company's purpose should be to know why do they exist and if you can't answer why you exist and if you can't justify that it's good for anyone, then maybe think about it a bit longer. When it comes to you and throw in a curveball question for everyone to think about, if you're new to this term, ESG and impact in the audience, what's your one favorite resource? Who would you follow on Twitter? Who would you, what newsletter would you sign up for? So I just want to, if start brainstorming that and did you have anything else to say on entrepreneur motivation here? Yeah, can I start with that one? Because I think one important point to make is that just looking back on one generation or even not that long, I think you were kind of first doing a career and then you were giving back. I think now more and more people realize that you don't really have to choose. You can make an excellent career and I say you've highlighted before, I mean, this is a great business to be and you can make a lot of money here. So you can have a great career, you can make a lot of money and you can make the world better. Like why would you choose another path? That's maybe a question I would have. That's how obvious you make this whole thing. Just like, come on guys. You're better, so, yeah, cool. All right. I think though, it's some of the potentially biggest impact things are not gonna be liked by everybody. So for instance, we've made an investment in a company in Geneva called Transmutics that's building a new type of nuclear reactor. And there are a lot of people in the environmental community who are like, who, why are you doing this? We don't need nuclear. We can get there on wind and solar alone. And I think some of the potentially most impactful things, it's entirely possible that Transmutics doesn't go anywhere, it's entirely possible that we can get there on renewables. But there's also a scenario where we can't get there on renewables and then we really only have two options which is geothermal and nuclear. And we haven't had a lot of innovation in nuclear in a very, very long time. And so I do think if you have conviction as the entrepreneur, you have to be willing to do things that might be seen as unpopular, including by people in the climate community. We're fully on board on that. We should talk nuclear afterwards. I wanted to just add that I 100% agree with that. And I would say that it really goes with the thoughtfulness of both as an investor to having been thoughtful of why are we doing nuclear, having done your research and really making a commitment to say that we believe it's a good investment, we believe it might be necessary in the future. And the same goes for the entrepreneurs. They have done their homework. They know why they're doing this and they believe that this might be necessary. And then it's not, you know, the whole world would judge whether they think that impact is positive or negative or whatever, but... Yeah, there's judgment involved in all of this, but as long as we can talk about it. But the important thing is that the thoughtfulness was there from both parties. So let's talk about it, right? And so we're coming to the end of this amazing panel. I hope you guys have learned something. I'm gonna do a rapid fire round. ESG efficient? Done right, yes. Yes, it can be. But it can also become a burden. Albert? I second what Heidi said. Okay, ESG, is it enough? No, it's definitely not enough. I think you have to start with, if you want to have a big impact, you have to build something that can self-sustain. By the way, that doesn't mean it's a for-profit. You can have a self-sustaining non-profit, but you have to do something that can scale in a way where it doesn't constantly need to go back to either investors or donors. I think that is the key to having big impact on the world. So ESG alone is not sufficient in any way, shape or form. Great tip, great tip. Is it enough? No, I don't think it's enough. It's a good starting point, it's good for assessment, but we need a lot more in terms of what impact is and thinking about these things in a longer term. And financial sustainability, all coming together. Yes. ESG, enough? Not enough. ESG won't save the world, but if we invest in positive impact, I think that could do the trick. Awesome. Did anyone think of resources or things that you would sign up to leaving this session to learn more? Inspired by anything? Follow us all on Twitter. Okay, all of that on Twitter. Or any other platform. He's not pitching Twitter. And final word of advice from all of you for people leaving this session. We've already given a bunch or warnings, words of warnings, anything else that you'd feel sad not saying to this amazing slash audience. Thank you guys for being here. Yeah, I think like try to move away from the ticking the box exercise and really think about, you know, how to assess the upside and potential risks. That would be my advice. Perfect. I'm gonna slightly repeat something I said earlier, but really. Reinforce. Yeah, really to reinforce the fact that everyone has an impact. And I think it's really important for both startups and investors to realize that and in some way to be thoughtful around what that is. Whatever they call themselves. Whatever they call themselves. Whatever industry. There isn't like an impact company that has an impact, but actually every single company out there. Final word? Nothing to add. That was perfect. Nothing to add. Thank you everyone. And thank you to the amazing panelists. Thank you.


So today, what we're going to do is talk about a tool that is in Power BI as well as in Excel, as well as in Azure when you're working directly in Azure. So there's something called data flows. Data flows, get and transform, all of them are the same. In fact, I'm going to open Excel, I'm going to open Power BI, so you can see all of them work. They're pretty similar, pretty the same. There's not much difference between the two. Five things you didn't know Power Query can do. Well, we're going to consolidate multiple sheets, and the nice thing about Power Query is that they've actually improved the interface. The interface is much easier than it used to be before. You had to write a little bit of M code, now you don't really need to. So what we're going to do is let me just get my system screen ready. So you should be able to see my screen right now. And in my screen, let me just pop out Excel for you to have a look. And then this is Power BI. So for those that don't know Power BI, let me start with Excel. So Excel, as we know, is the number one business intelligence tool out there. Everybody in the world uses Excel. Even those that don't like to use Excel and have a beautiful business analytic or visualization tool have one button called export to Excel. And they export their data to Excel when they can't do something elsewhere. Because Excel is extremely versatile. All these cells, every single cell is programmable, right? And you have more than 17 billion cells in a sheet, right? So what are we going to do? The power tools, those power tools, let me show you where they all come from. Now, the version of Excel you're using is very important. So Excel, as you know, we have home, we have insert. So by the way, I'm using Office 365 Pro Plus. How do you know?  Once you go to file and account, you will see the version you're using up here. Subscription product is what I'm using, Microsoft Office 365 Pro Plus. Now, this is like the latest, latest version of Microsoft Office. So if you're not using 365, then you probably will be using maybe Office 2016, Office 2019, Office 2010, Office 20. I hope you're not using anything below 2010. I mean, even 2010 is too old. So I'm using Office, let me type mine. I'm using Office 365 Pro Plus. I use Office 2019. Okay, you have to specify exactly what you use. Is it Pro Plus 2019? Is it business 2016? Is it? Yeah, because they all do different things. Why that is important is because if you're using 2019, you have all the tools that I'm going to talk about. But if you're using 2019, Professional Plus, right? You have to see this Pro Plus. It's very, very important. Pro Plus. Yeah, Pro Plus. What does Pro Plus do? So if I come to options, for example, say I'm file, if I go to options, you'll see what I mean. Under options, yeah, you go to the add-ins. There are certain add-ins, certain very professional add-ins that you need to make sure you have if you want to use all the Office tools well, right? So for example, if you look up here, I have data streamer, I have inquire, I have power pivot, I have weather, I mean, all sorts of tools. Now, most people will have certain things. So if you go to excel add-ins, once that, you know, I went to excel options, add-ins, can you see the add-ins here? I click on com add-ins. When I go to com add-ins, this com add-ins, I click on go, you will see those power tools here. So down here, you can see I have Microsoft Power Maps for Excel, Microsoft Power Pivot for Excel, Microsoft Power View for Excel, inquire, go to meetings, something, dark studio, these are other extras I've added. But these Microsoft ones, just check your system, go to file, options, add-ins, and then com add-ins and see if you have these ones. If you don't have them, maybe you don't have professional plus. You most likely have, maybe it's business, or maybe you have a user, maybe like student edition or something like that. So please, anytime you're buying Office, make sure you buy professional plus. And I advise you to get your Office 365, because that is the latest of the latest, right? Office 365 is the latest. Now, why do I mean the latest? Because Microsoft updates Office every single month. Every single month, it updates Office every month. Now, once it updates Office, what do I mean by updates? There's so many new things in Excel. Let me give you one. Yesterday. Now, this is yesterday, right? Yesterday a new function was added in Excel, just yesterday, right? And the only people that can see that new function is a very major new function. The only people that can see it are those that are in the Insider program. Now, you see, even if you have Microsoft Office 365 Pro Plus, there's something called Office Insider. So down here, Office Insider. And Office Insider means that any new thing Microsoft brings, they'll give it to you first.  And everything, once it's stable, they now give everybody else. So in my Insider Plus, there's a new function called Let. I'm not going to talk about it. We're going to do a video about Let very soon. Do a video later about Let. So Let is almost like SQL, right? Almost like a SQL, what people do in SQL. So we'll talk about Let. And Let, if you're in power, power pivot and use DAX, Let is a bit like variables, right? It's a bit like a variable. So in DAX, for those that are very advanced using DAX, you know, you know about variables. If I show you the syntax for Let, you have Let name one name, name value. So you're letting name one, you're kind of defining a variable and then using that variable in a calculation, right? But anyway, so Let's, let's, let's leave that as advanced. So let's jump into what we're talking about. We're talking about power query. Now, if you have Office 365, and you have 2016, or you have Excel, Office 2019, you have power query is on the data. So if you go to data, you will see a section here, a very important section called get and transform data. So get and transform data, you see it down here. This is power query. That's what is called in Excel. Get and transform is power query. If you have 2013 or 2010, you will not have get and transform. What you have is get external data, which is the old school one, right? So get and transform is power query. And that is a super, super powerful, powerful tool. And that's what we're going to work on today. So let's see what we're going to do. So let me show you, let me open some few files. I'm just going to open a few files on my desktop. And you'll see what we are referring to. So I want to consolidate data and various sheets. I want to consolidate data and various sheets. So data, well, sheets, data that's inside various sheets, right? So I'll just open one for you. Let me just say I open raw data. And how do we consolidate it in various, various sheets? Let's see how that works. So if you look at this file, so this file right now has like payroll information you have. So this is your typical data. I have employee number. Let me show you that I have employee number. So you can see that data, employee number, you have name, you have month, you have basic housing transport meal, gross tax pension, all these various data stuff. And then what I want to do is that, that if you look at the sheets, I have the January sheet, February sheet, March sheet, April sheet, all these various sheets, right? So I want to consolidate all these sheets into one. And anytime anyone adds a new sheet to just consolidate it regardless, right? So what do I do? Well, let me close this for now. I'm going to use, let me close this, I'm going to use this sheet here. I'm going to get data. I'm going to connect to that data. So I'm going to connect to data now. Power query can connect to more than almost 200 data, data, data, I mean, 200 different data sources, right? From database, from Azure, from online services, even a website, you can dump a website there to connect to a website, connect to all sorts of data, and through the old data feed, it connects to all sorts of data online. So old data is open data, open data feed, so much data, so many different data sources can be connected through the old data feed. And you can connect directly through an OLDBC, for those database people, you have an OLDBC connection. So, so hundreds and hundreds of data sources can be connected. I'm just connecting to Excel. So from file Excel, very simple. So I'm connecting to Excel. What I'll do is I'm going to navigate, I'm going to navigate to my desktop. That's where I have my file, I say PBI webinar. And these are my files, I'm going to connect to consolidation. And then that file that we just talked about, that 2010. So click on it and we connect to 2010. Right, so you see that connect 2010. Now you notice that all the files are listed, all the sheets are listed. So these ones with the blue, see these ones with blue, blue, blue. You can see them are very tiny. Sorry about that. Let me see if I can navigate, put a navigate or magnifier, magnifier. Let me activate my magnifier for you guys. Yeah. Okay. Maybe, so just, just trust me, tiny, tiny blue. Those are the tables. And then these are the sheets, right? Those are the tables and these are the sheets. So what we need is we're consolidating everything. So we're not just going to pick one at a time.  This is the sample data. What we're going to do is kind of consolidate from the folder directly. So you right click that folder, and you say, that's transform data. That's just transform data. So when you click on the folder and transform data, it's going to open an application. It's separate application, which is Power Query. Remember, Power Query is an application. So it's opening that application, right? So I need to magnify. Let me see if I can use it. I think there's a shortcut for me to use it. Let's see. Okay. It's not going to work as well. Let me just stop it. Let's escape. All right. So let's see. So this is us connecting to, connecting to a folder. Yeah. So anyway, we have your data. So what happens is you have your data there. You have your data. So this is all your data, your name, items, kind. So this is your metadata. This is the metadata of that file, right? So name, items, January, the name of the sheet is a sheet or a table. And if you look at this, we have sheets and tables. Now, there's something called table in Excel. We convert data to a table. And then if you don't convert it, then it just remains a sheet. So what we want are the tables. So it's a good idea to convert to table. I'll show you what that means shortly. So I don't need all these sheets. So I'm kind of deselecting sheets. Yep. So now I'm only left with table, right? So I'm only left with table and we have our table here. So now I can kind of remove all other, I'm going to remove all other columns, remove all the columns here. So I'm just left with this table. And then I'm going to expand the table itself. So when I expand the table, you see that all the list of names, all the list of sheet headings are there, just like we have it. Typically, you should remove this button, use original column name for prefix on ticket and then say, okay. So when you say, okay, it expands it. And this is technically your consolidated table. I mean, this is all the things in your various sheets. So you have your April, your August, these are all the sheets. And this is your table consolidated. It's as simple as that really. And then you load it into Excel, right? So I mean, if you look at the employee number, you see very important thing about data types. So your data types are the next thing you need to check, because all of this are currently ABC is very tiny, but this is ABC123. And that means the data type is any, you should never leave your data type as any. So what do you could do? I mean, they say the most efficient people are the laziest people, right? So they, because they have to look for faster ways to do stuff. So what you normally do is say, hey, this is a text data type, this is a text data type, this is a text data type, this is a number data type, this is a number data type, but you could just use under transform detect data type. So you click on detect data type, it goes through everything. But of course, Jolly went through housing, I need to highlight everything for it to detect for all. So I'm just going to do shortcut Ctrl A. If you click on one Ctrl A, that selects the whole thing. And then I can say detect data type. So it goes and checks for all. And what is done, it is written an M code here. So it's written an M code that's kind of checking everything and saying, hey, what is your data type? Like housing data type is number, employee number, data type is text, name, data type is text. So it's done all of that for you. Sometimes it doesn't work as well. So you better be careful. But I think it's fine for us right now. And once you are done, all we need to do is come to home and close and load. To close and load, let me say I'm going to close and load into Excel. So I'm just loading directly into Excel. Now if you don't want to close and load, you could probably just create a connection. But this is your consolidated data for the entire sheet, every sheet there. So this is where consolidating a sheet. Consolidate workbook. Consolidate sheets. So that's simple, pretty simple. What next do we do? So that's how you consolidate sheets. What do you think about that? Do you use this? So here we're talking about consolidating multiple sheets. So the next thing we're going to do in the next trick we're going to do in Power Query is consolidating workbooks containing multiple sheets. So what we just did before was consolidating sheets in one workbook. What about many workbooks that have many sheets? How do we do that one? Okay, so let's have a look and see. All right. So let's quickly check. How do we do that? Let's go back to Excel. So now this was consolidated sheets in a workbook. Let's go to sheet one. And we're going to now come again. Now we're going to go to data. And we're going to consolidate. Let me see if I can quickly change my resolution. So it's a bit bigger for you guys. All right. So I've changed my resolution. I think it's a little bit better. So now we're going to go to data again. This time, we want to consolidate from a folder. So I want to consolidate a lot of sheets, maybe a lot of these multiple sheets. You know, this was just one sheet, one workbook that had many sheets. Now what I want to do is consolidate many workbooks. So how do I do that? Obviously, I'm not going to connect to a workbook anymore. I'm going to connect to a folder. Right? So I'm going to connect to a folder that has all those many workbooks. How do I connect to a folder? Well, I have to browse and then I'll go to my desktop. I know I dropped it on the desktop. I think I called it RBI webinar and then consolidation. So under consolidation, we have all those files in there. So I click OK. So I'm going to click OK, click OK. So it's going to go look for those data and it's going to open power query, obviously. I'm going to try and open. This is pre-opening of power query. So these are all the data files. I need to combine them. So I'm going to combine and transform the data. So combining and transforming the data means that I want power query to work. So power query is going to open or Excel is going to open the power query application and then get us working. So before it even opens, it's asking us, OK, this is what I saw, right? These are the first file. This is the first file in my sample. If I come here, you can see there's 2010, 2011. These are the workbooks in my folder. So I have a workbook. Remember we did this first, 2010 employee payroll, 2011 approval by 2012. So I want to consolidate all of them. So everything I want, not just one by one. Everything, right? So if you check 2011, for example, so 2011, this is what is inside the 2011 workbook, right? If I click this, you see that this is the data under the 2011 workbook. But I really am not interested in that. I can go to first file, which doesn't really matter which one I pick because I want to do the whole thing. So I'm going to right click this and transform data from the folder itself, right? So I transform data. So I right click the folder and I want to transform the data. So it's kind of processing the query and everything trying to pull up power query editor. So it's pulling up power query. So this is the power query application. As I said, if you have 2013, you don't have this. You need to go download it from Microsoft for free. Even 2010, probably you can download it for free as well. 2016, you have it already. And it's 2019, you also have it already. It's installed. It's already in data. It's under data. You see there. Anyway, so here we're consolidating now because we went to a folder. You have something called helper queries, right? And then under helper queries, you have your transform file from consolidates. This is more like a transforming one sample file. And then this is the query where everything drops. So your consolidation. This is 2010 employee payroll. And you have your sample file here. This sample file is whatever you work on on this sample file. Just take this as maybe the first workbook in that folder. You whatever you do on it, it will replicate for everybody else. Right? So that's it. This was our old query that we did. This old query that we did. And then this is the consolidation that we're trying to now consolidate everything into. So we'll come to the sample file and let's work on the sample file. And on this sample file, all we need, you remember what we need? We only need the tables. We don't need sheets. So it's exactly what we did before. Yeah, we're going to do that on the sample file. So I only need the table and then I only need this. I'm going to right click this and remove other columns. Remember, I'm working on this sample file. This is the sample file. And then I expand the sample file like I did the other time. I remove this. I say, okay. So it now expands, right? So it expands everything. All the data is expanded. And then what I can do, I mean, you have various options, right? What I can do is actually twist all of these things. I could say, Hey, let's do auto detection. Maybe we should auto detect. Now, everything I'm doing here is going to do for all the files. You can see that this consolidation has one funny error. Do you know why? Because of change type. So if you see this sign, this means there's a problem. Remember, we're working on the sample file first. And as we're working on sample file is automatically working on all the files. So looking at this tiny error message is because of this change type. Data changing data type is very important. If you don't change data type properly, things can go bad. So I'm going to cancel that. So you see things are still fine. So I come back here. And what do we mean by change type? You can see ABC 123, ABC 123, ABC 123. It's really, it's not the best format to use. So let me finish my transforms. And after I finish my transforms, then I will now change the data type. So employee number, employee name, month. You have month there. And what else do we have? Yeah, I think those are the things we have. Employee number, employee name and month. Now I could twist this. I could highlight these three, right? Click it. And then I say on pivot other columns, right? On pivot other columns. What that will do is going to take leave these three columns there and then twist everything else so that these now become probably something like headings for our line items for apparel and then value. So I can say this is what I want to have first. And then all my sheets should transform to this. So if I come to consolidate, you see that this is, if you see what I just did, you say I twisted things and everything is fine. The reason I like to twist things like this is so that all the names are here and I can use this column to kind of maybe do almost like a V look up in Excel, right? A V look up in Excel to say, hey, if it's basic, you should call it basic salary. If it's housing, you should call it housing or house allowance. Transport should call it transport allowance. So we can standardize our tool by twisting this here. But anyway, coming here, this is the consolidated one. And you notice the difference. This column has source name. The sample, this is just a sample, no source name. Now consolidated has to list out all the different sources. If I say load more, you will see that all the different sheets are here. Look at them. All the workbooks I mean are here. See the workbooks. But this is quite, the first thing here makes more sense, 2010. I don't need employee payroll. So let me work on this a bit. I can click on this and I can go to transform and say, hey, I want to extract something. I want to extract only the figures, right? So if I want to extract just the figures, what would I do? What can, what demarcates the figures from everything else? If you notice there's a space in between, we call that a delimiter. So we're going to use that to extract this. Or you could just say extract the first four characters. But let's say extract, you see length, first character, you could say first four characters, or you could say last characters range text before delimiter, or text after delimiter, let's use text before delimiter. So the text before the delimiter called space is what we want to extract. So it's going to open a small tool, the space. So I say, hey, the delimiter is space. I just put a space and say, okay, look at what it does. It's going to wipe out everything before the delimiter, right? So it's going to give me the text before the delimiter, which is cool. I can come in here and rename this year. Sorry, can rename this year. And there we have it. So now my data is all nice and clean. I can now check my data types. I can just do a auto data type detection now. I can come to transform and say detect. Oh, no, you know, remember what do we need to do? I need to, I need to basically, let me undo. How do you undo? Undo control Z won't work. You have to come here and delete this step from this. So what I should have done is highlighted everything. So I can click this and do control A. And then I'll say auto data type, detect auto, detect data type. And then I want to change this. This is text, this is text, this is text, this is text, and this is value. Perfect. But this one, I don't want it to be text. I want this to be, I want it to be, I don't want it to be value. I want it to be text. So I need to go change my own data type for this myself, right? So how do I do that? You can go to home, come to data type here and change this to text, right? Replace current, add a new step. Maybe I can add a new step, replace current, replace current is fine. So what is done is the auto data type is modified the auto data type, right? Everything else is fine. It's just modified it a bit. Yeah. All right. So this is it. I can call this consolidated. And then I can come to home, come and close. And I have the option to close and load to close and load. Let me do close and load to slope. Remember, we're in Excel trying to do this. So I'm now bringing my answer here. So how do I do that? I can come here and say, Hey, I'm going to add to data type, data model. This is power pivot. We're not talking about that. I can say only create connection, which means I don't want to bring it into Excel. I just want to connect to it and do my reporting from the connection. But really, let's bring it in and see what it looks like. Let's bring it in. Let's bring it into this existing workbook. Let's put it from A1, right? And let's see how it looks. So it's trying to get data. It has got all the data. Let me close all of this. Let's see how it all looks. So this is our data, guys. 2010, all the way, I have like 7,000 and some empty subsets, about 19,000 rows of data, 2012. All I need to do now is if I go to my folder and I drop a new workbook in there, all I do is right click this and refresh and everything comes in. Yeah, perfect. So this is my Consolidate workbook. Consolidate workbook. No, Consolidate. Yeah, many workbooks, multiple workbooks, multiple workbooks with multiple sheets. I don't know if this can enter. I think this has the limits. We're multiple workbooks. All right, what's our next, what is the next thing we're going to do? Let me switch on to slides. So we've done building the next thing we're going to do is build a reconciliation tool. So what we did is, if I recall, the first thing we did is consolidating multiple sheets, then consolidating workbooks containing multiple sheets. Now we're going to do a recon tool. This is very, very, very useful. We're going to build a whole reconciliation tool. So let's see how that works. So we're going to consolidate multiple, we are going to do a reconciliation. So let me explain the kind of reconciliation I'm going to do. So this is my folder that has data. So if you look at it, I have a recon sheet. So the recon sheet we're going to do is like a payroll recon. Let's assume when you're doing a payroll reconciliation or any reconciliation doesn't have to be payroll. What are you reconciling? Let me open old, for example, right? So let me open old. So look at this old sheet. Let me show you all. So it's coming up here. So this is old. So again, we're using the payroll theme, right? So this is data. We have serial number, employee number, staff name, grade level, designation, start date, promote date, then the actual things, the actual salary figures, right? These are all the salary figures, right? Great. So what are we reconciling? When you're doing a reconciliation, you're just checking something that existed before. Does it now exist? Is this still in this new? So we have an old data and the new data. We're checking whether the data in the old is also equal in the same data in the new. And if it's the same data in the new, it has been reconciled. If it's different, you want to see the difference. What is the difference? So take this guy's salary, for example, 3262500.01. Is that the same salary in the new? Let's call this old. Let me open new. So we see what we're talking about. So if I go to recon, let's say payroll, the new, right? So let's open new and see how do we do this manually? Now remember, even if this thing had 10, maybe like 500,000 rows, it's the same technique. But it has 10 rows of 500,000 rows. We're trying to build a tool. So let's call this, this is new, right? So if this is new, you can see that this figure here, right, is different from, it's not different actually. It's actually the same. So it has reconciled. So this and this is reconciled. Let's see if there's any difference for this guy. 45, let's see three. Let's see. That seems to be very compliant. No difference. I can see 836, 836. This guy has no difference. No, it's not one of the differences. So let's see another person. 275118. Let me see which one is very glaringly different. Let's check. Yeah, I can see this guy down here. So see this Trant Paul. He has 186433. Yeah, this one. And in the old, he had 274330. Now, yes, you can do Excel. You can say equals to this minus this, right? Please don't do this. This is so manual. So what we're going to do is automate the reconciliation process. That's all we want to do, automate that process. So close this, don't save, close this, don't save. And here we're going to do a recon. So we're going to pull in a recon. So how do you do that? So data, we're going to get data. Now, if you remember that whole thing was inside a folder, right? So look at it, new and old. We're going to bring these two things into Excel, right? So we're going to bring it. So guess what? It's in a folder called payroll. So we have to go and look for this folder. In fact, let me copy the path. I'm just going to copy this path, right? And then I'm coming to get data from file, from folder. So I got data from folder, and I'm bringing it in where you just paste the path. That's the quick trick. So we can connect to that folder. So connected to that folder, come here, I do the same combine and transform data, combine and transform, and you will see those two files. So what I advise you do is any time you're building a reconciliation tool, you have the before and the after. Just have the before and after. Many have the before and after. Okay. So let's go, let's just try that again. So come here, from file, from folder. And let's, let me just paste this. And I'm just going to browse. And then on the recon, what I want in the recon is actually payroll, right? It's actually payroll. I want no recon. See payroll. So if I click on okay, so that would be reconciling the folder which has the folder and that works. So click okay. So now I've gone into payroll. And now I need to combine and transform, right? Combine and transform. It's going to combine and transform. By the way, guys, I know we'll be using Excel. Everything I've done here can be done in Power BI. This is the same thing. It's really the same thing. But I'm just using Excel. It's the same thing I can do in Power BI. Power BI needs data. This is how we get the data. Now obviously, you can see it's only clean showing, right? We have parameter, whatever first file. See there are two files, new and old. So we're going to work with the two files. So if I'm working with the two files, I'm not going to work with one. I have to work with a folder. So I'm going to right click this guy and transform data, right? So once I transform, it's going to get the data. Give it a few seconds. It's doing this thing, evaluating the query and opening Power Query. So that too, Power Query. Power Query is opening as Power Query is a different application. Now remember guys, we've been doing many things before. If you are done, if this one you're doing now is just the first thing you're doing, you won't see all this. Remember this was consolidation we did. And then that's how it transformed and stuff. So this one we're doing right now is down here. This is helper queries. This is transform file from payroll. So don't look at this one. We've done this already. This is what we're doing now. Transform file from payroll. These are the helper queries parameter, parameter two, maybe another webinar we'll talk about parameters. This is the sample data, the new. This is the actual transform that is going on. This is almost like a custom. Can you see let's, by the way? Can you see let's?  So many things. Anyway, let's move on. Transform sample. This is the sample. Whatever you do to this sample, we'll do to the two files, right? And what we want to do is do to the two files, isn't it? So we're just going to walk on this sample. So you can look at this is the data. This is the table. And this table, again, there's the kind here is sheets. We don't have, we didn't convert that data to a table. So it's just a sheet. So we're fine. We're just going to walk on this, right? So I can remove everything else, remove other columns. And then I can expand this. So when I expand this, you see this is what it is. Use the original column. Let me even leave this. So you know why we like on ticking this. If I say, okay, you will see that this kind of data dot column, data dot column two, data dot, that data dots, it really doesn't add too much value. That's why we remove that step. So if I close this and click here and I expand, right? So that's why we remove this so that it doesn't put that data dot data dot. We're not interested in that. Anyway, click, okay. So this is our data. We need to clean this up. Remember, if the two files, we're trying to consolidate. Now you have the option of actually just doing one file at a time and then now bringing them in. Maybe that would be better because the two files may not be exactly the same size, right? But all right, but this is the sample anyway. Do you remember? This is a sample. So whatever we do here is going to do for each and every file individually, right? So if you look at this, all these line here, they're kind of a waste. This first row, second row, third row, this is where our heading is on the fourth row. So we need to kind of remove those headings. So we need to kind of come up here. I see home, we remove rows, this first three rows. So click on remove rows and remove top rows. If I click on remove top rows, and for us, the way our data is structured is the top harmony, top three. So we're removing the top three rows, click, okay. Now this row is now the real heading. We need to promote it to become a heading instead of column one, column two, column three, right? Under home, you will see use first row as headers. So it's going to promote, right? So once it promotes it, now really what we can do is we can twist. Remember, whatever we're doing here is also doing for the consolidated. Remember, look up, can you see payroll error, right? Payroll. Can you see payroll? Why is there an error here? If you notice that change type, I told you, always come here, delete that change type. So everything, so this is like programming. Whatever we're doing here is doing for the consolidated, right? Whatever we're doing here. Ignore these two guys. This is what we did before, all right? This is why we're concentrating on payroll. So now transform sample. Let's continue the transformation. What I advise you do is, whenever you are doing this, you tell your people that, look, there are certain columns that uniquely identify a transaction. Look at them, employee number, employee name, prefer employee number actually, and employee name should be the same anyway. Then grade level, this uniquely identifies that person, right? Then we have job designation, uniquely identifies that person, start date, promotion date, all this uniquely identifies them. Now, do I need all that data? Maybe not, but I definitely need employee number. And then probably I need, what else do I need? I think employee number is the key identifier for me. The rest are not as necessary. Maybe employee number and employee name, right? So this and this. So what I should do is, I'm removing those things I don't need. I'm right, clicking this, I'm removing this. I'm leaving this to, let's even assume I don't need these ones, but it may be good to have it, but I'm going to remove them anyway. I'm going to remove all of this, right? So why am I removing that? Because what I want is just employee number and employee name. So these two, and then I'm going to highlight these two, holding my control key or shift key to highlight, right, click, and I'm on pivoting other columns. Where is that? On pivot other columns, right? So you see, I've unpivoted other columns. So we have employee number, staff name, attribute and value. Now attribute and value. What do we do attribute? We can rename attribute, right? Attribute is the line item, p line item. I can rename that. Value is the, well, the value. Now, how do we reconcile? Remember, I'm doing this for all. So all my sheets will now have employee number, staff name, p line item and value. Now, if I come down to payroll, remember, this was sample. I come down to payroll. It now has what? Source name. Remember that source name, new stuff. Employee, this just tells us what sheet or what workbook we're working on. It still has that employee number, staff name, p line item and value. So the trick for reconciliation, for this reconciliation, will be for us to write a code, just write a simple M code that says any value that is in our new will be a positive value and all values that are in our old will be a negative value. So we're going to do that now. Okay. So we're going to do that now. So if you look at this here. So if you look at it, let me just recap. This is our transform. This is a sample of what we're doing for one sheet, one workbook. This is the one that has done for both. So these things here, this column here, you can see is new and old, new and old, which is perfect, right? So we could just rename this as our files, right? Source files, right? And all this, we can even transform the data type, the data name now, we can do the data type transformation.  And then we say detect data type. And then what it does is detect all the data types on that. So the trick in reconciliation is you can say that all the new data would be whatever sign they have. But all the old data, we're going to transform the sign, we're going to, if it was positive, we make it negative, if it was negative, we make it positive. That's the key trick. For us to do that trick, we need a new column. We need to insert a column. How do we do that? We're going to insert it based on an example, but we're going to say, okay, anytime you have new, well, in fact, this XLX, XLSX, let's just remove it. It's anything after the dots. We're just going to kind of clean that up a little bit. Okay. So let's extract. Let's just extract based on, again, delimiter. And let's say our delimiter is dot, right? So a delimiter should just be dot. And then we say, okay, before the delimiter, so it removes that and we just have new and old. So that's clean, right? So now we're going to say anytime you have old, it should put a minus sign before it. So in XL, remember, this is not XL. This is power query. So we're going to do a new column. We're going to add a column. We're going to add a column, custom column. And this custom column, we're going to say, this is the real amount, right? This is a real amount. And this amount, we're just going to write an if statement. Let me see if I can expand it. I can't. So I'm just going to say if the file, right? If we click on this, if the file, so I'm just typing it directly, but I don't even need to type it directly. What I can do is I can just double click this, right? Source file, right? I'd say if my source file is equal to, if my source file is equal to new, well, let me say old, actually, if my source file is equal to old, right? My source file is equal to old, then, okay, I have to put old in double quotes, right? Remember that, you remember, if you look up here, guys, you remember that our source file were named it new and old, right? So if my source file is equal to old, then I close my bracket, right? So this is my if, this I write the if, if source file equals old, right? Then now in XL, you would have put a comma, but then you say then here, you say then what you should give me is what? Give me value minus one, or just say minus value or value times minus one. So you say this is equal to or give me value times minus one, right? If not, or else, now you say else in, in, you now have to type else, else give me the value. I can see that. So this is the simple logic.  So this has some right there, wondering why, but shouldn't be. I think it's called old, old and new. Maybe it's case and stiff. I think old was initial cups. Let me see if it was initial cups. Yeah, it should be fine. To control error, let me see what the error is, value, value times minus one. Let's check what the issue there is. Now value, I hope the data type was 1.2. Yes, it was. So this looks strange. So it's showing the error as this value. So maybe there's too much space here. Let's see. I know that's fine. So if then value, this is a strange data type, data issue. Let me just type it again. Let's say value times one minus one. Let's just delete that. Let me double click value again. If source file is called old, then value times minus one. There. Okay, I'm going to type this whole thing again. That's strange. So it shouldn't still give me an issue with value. So let's delete that. Let's just delete this section again and start that section again. If source file is called old, let me even do the opposite. If source file is called new, because it really shouldn't matter. If source file is called new, then let's just say then value, right? Just say if source file is called new, then value. No, I won't put then. If source goes to new, then value, else value times minus one. It's still giving me a funny error, which is so strange. So what I can do is let me show you another technique. Let me just delete that. Cancel. Yes, let's just do that again. Right. So what I can do, let me do it the old-fashioned way. I'm going to create a custom column. Let's break it one at a time. So what I would do is I would duplicate. It should work really. There's no reason why. Something's up with my system, custom column. Let's just try it again. Let's try it again. So if my source file is equal to new and now close my bracket, then give me the value else, give me the value times minus one. Right. So this should work. Let's just say okay. So now saying if it goes to new, give me the value. You know what we said? The guy gave me minus one. So let's check. If source file, I'm going to check my formula again. Extract data hard column. Let me click on this and check it. If source file is equal to new, then value. So this new, we have to, you know, it's case sensitive. So I guess we need to put initial capital letter. The guy is very, very sensitive. Let's just see. Can you see that? So guys, very, very careful. You mean the spelling? It has to be capital letters. We spell it exactly the same way, properly. So anyway, that's good. So this new, now all the old is going to have negatives. Right. Check down, down, down. You see the old have negatives. Why do we do that? You're going to see it very soon. Why we have to do that? So now we've done that recon. We don't need, we don't need this value line anymore. We need the amount line. So I'm going to right click and remove. Then this is our data. So this is our data here. And we're going to go to a group buy. We're going to now group buy. And that group buys now was going to really pull everything out for us. So group buy, we go to home, and then our transform. Then we group buy, we click on group buy. And what do we do with group buy? Wait a minute, this. So we're going to do an advanced group buy. We're going to say, do you know what? I want to group buy source file. Well, I'm going to group by employee number. I'm going to add, because I want to want to see employee name. But really, if I just group by employee number, I can do like almost a VLOOKUP and bring the name later. But anyway, let's just do staff name. And then I'm going to group by attribute. So attribute, which was now renamed as our pay line item. So I'm saying, hey, look at all of these things together. It's almost like concatenate. Concatenate this. And then once you concatenate it, then I want you to add a new column. The new column will be, let's say, recon. Let's just say the actual reconciliation, recon amount on difference. Let's just say recon difference. Let's say we're going to sum this our column that we have called. Okay, we remember we supposed to call it the amount. We didn't call it, we didn't rename it amount, but custom. So once it sums it, guess what? 10 plus minus 10 to be zero. That's what we want to do. So let's just see what comes up after that. So if you look at it, we have zero, zero, zero, zero. All these zero means they've reconciled. They've reconciled. And if I now filter this out and I filter out the zeros, that means I'm not interested in the ones that have reconciled. I'm interested in the ones that haven't. And where you're left with is the ones that haven't reconciled. And that is your reconciliation. Really, those are your reconciliation differences. So some of these values are supposed to be values, but I think they are anyway. But this, so you have employee number, employee name and everything. And this is your reconciled. What would be nice to check is look, was there an instruction for this? How did this guy's salary change? Did they send an instruction? We could actually add that to it, right? We could actually come here and add a new source and say, hey, new source, give me a file. I need an Excel file. And the Excel file, if I go to my desktop, you will see that there is under the recon, we have instructions. And this is the, let's just say, call these new instructions. Our advice, you change this to new instructions. And under new instructions, you would see that we're trying to see, okay, was there an instruction for this? So this is how you do a full reconciliation, looking for, so we'll just edit this directly. We just right click it, transform this data. And then we're going to merge it to this. You guess we're going to merge it to this so that you will see whether there was an instruction for us. Simply, we can just click on this, remove null and say, okay. And then promote headers, just the same thing we did before. And we can right click this and twist and all that. You remember all we needed was employee number, employee name. Maybe we can even leave all these guys here, right? Yeah, we don't really need that. We can just, we can just say, okay, do you know what? We're going to merge. Let's just merge straight. We don't really need any, any, any stuff. I'm going to merge instructions to payroll. So it depends on where you want to start from. If you start from instructions, you're saying, hey, instructions, and then payroll, but let's start from payroll. And then we're going to merge with instructions, right? So how do we do that? We come to merge queries, merge as new. Let's not spoil what we have. Let's merge as new. So let's leave what we have there as payroll and instructions and say, hey, take payroll and take instructions and use employee numbers, right? To merge the two. This is called a left out to join, which means you're taking this and taking this and everything else here is kind of joining this one here, right? So we say, okay, and let's see what that brings up. So what that brings up is going to bring a table to the right. And this table to the right are all the things that are linked to these employee numbers here. So if I click on this, right, I can just click everything and say, just give me the instructions. And remember, we didn't have grade grade level. So we can bring in grade level. Why not? Yeah. So, and you remove this and say, okay, and what happens? It now brings those values there. So this guy was in grade level eight. And this was the instruction removed from payroll, da, da, da, da. And then if there was no instruction, of course, you're not going to see anything there. And then what we can do is now load this into Excel, right? Close and load and guess what? That's us done our recon, right? We finished our reconciliation and it's going to load. This is quite a long case, but this you've automated recon. So all you ever need to do now is drop new and old folder, new and old data into a folder and play. And it just does your reconciliation for you. And anything that didn't have a recon actually probably put some things that didn't have any instruction. If I didn't have any instruction, for example, let me just click on recon, right? So let me come here. I click on recon payroll. No, instruction August. So let me pretend I'm not give people instructions. So let's say, for example, these two people didn't have any instructions, right? So I come here and I refresh. So he's going to go and do the whole thing again. And guess what? See, these guys didn't have instructions. Why did you remove his salary? People go up in arms, especially during this crisis. I mean, we need money. So guys, what do you think about that? That was a long one. But how did that make sense? You're automating recon reconciliation, any reconciliation to work with this technique. Next, move to the next one. We're going to consolidate PDFs. How do you do that? So you do that by looking at the data. Let me go to the data. Let's say these are PDF data. So I just quickly downloaded this and these are stock exchange data. Any PDF would work. Any PDF that has tables inside the PDF, what we're going to tell Power Query to do is one Power Query to go and extract those PDF files. So look at it. This is daily office list of bond prices on Nigerian stock exchange. So this is already in a table form and it's inside a PDF. And you don't want to start copying and pasting, copying and pasting. That is ridiculously slow. So what you can do is tell Power, I mean Power Query to go into this file and pull out all this information for you and into Excel, clean it up and then use it for your analysis. So that's what we're going to do very quickly. So how do we do that? So let's say we're going to use one sheet. Let's console it. Let's pull out this data. Let's check. So you come to get data. How do you get data? Well, if you check it, you say from file, where's PDF? Where's PDF? Where's PDF? PDF, PDF, PDF. This looks like PDF, doesn't it? No, it's not. No PDF, no PDF. So sorry, bad news, guys. There's no extraction of PDF in Power Query in Excel. No, because Power Query in Excel is not as advanced as Power Query in Power BI. So there are certain things in Power Query that have been added to Power BI that have not been added to Excel. So remember, good old Power BI, everything we've done up to now can be done in Power BI. In fact, it can just copy and paste the code and it will work. But Power Query in Power BI is more advanced than Power Query in Excel. So get data. This is exactly almost similar to Excel, right? But it has more features. So if I click on more, so I click on more. So what we can do, guys, is you download Power BI Desktop for free, do your consolidation on PDF extract, and then you can take it to Excel. So look at it. See all Excel currency PDF. Hopefully this will come to Power Office 365 soon. Hopefully, I have to ask the guys at Microsoft to hopefully get this into Excel's Power Query. So if I click on PDF and connect, I now need to connect to the PDF file and go to Desktop and then this is this. And let's just minimize this. So PDF data, that's this, right? So PDF data, let's consolidate one. So it's going to go in and parse the PDF and kind of pull out information from the PDF and check to detect tables. So tables have a certain structure. So it's going through the PDF to look for tables. And guess what? It found tables. See, it found this table. It found this table. It found this table. It found this table. It found this table. And like that, right? So what you can do is just click on this again, just the same way we did transform data, exactly the same steps we did. It's just that the file right now is a PDF. Now Power BI will also open Power Query, which is a different application, isn't it? And it's a different application. We're interested in tables. So we're going to come to kind. And we're going to say, hey, we're not interested in pages. We're interested in tables. These are our tables. These are the things we want to consolidate to right click. We remove all the columns, right? I remove all the columns. And then we expand. And when we expand, as I said, we remove this button here and we say, okay, right? Now we have a table. What we need to do is maybe the headings of this bond description and stop this of correct heading. The first row, really, we don't need it. So you can either remove the null itself, because we also, if you check to the next table, null on the first row. So null on the first row. So we really don't need nulls, right? Nulls, we don't need nulls. So nulls identifies the first row. Or you could basically say, hey, just remove the first row. So we can just say, hey, remove nulls, right? We're saying remove nulls. And then we can say, hey, pull this up. So how do we pull it up? We say use first row columns, where are we? Use first row columns here, right? Use first row columns. So many ways to do that. We could even click here and say use first row columns, so many places. So once we do that, now remember, this is table one. To scroll, you see table two also has a heading. See table three, somewhere down here also has a heading. So we also need to say, hey, remove anything that has symbol. Now, by the way, when you want to do that, go to a column that doesn't have too many things, like this first one. Let's say, oh, they all have many things, but bond description, right? See that? So I say, hey, remove bond description. So this is the only bond description. So the other tables will just kind of join everything together. And what you can do next is come to home or transform and say, hey, let's detect data type or detect data type, but let's highlight everything first, right? So control A and then detect data type. And you see it's now one, two, three, ABC and stuff. And really, that's it. You're done. So this is your data one, right? And that's it. So what do you do with the data in Power BI where you can just close and apply. And that now comes into your data model, right? It comes into your data model as a data. And your data model is there. And Excel can now connect to Power Query, I mean, Power BI as a data source. And then that way can now bring it into Excel. But that's another story. So if you've done this, that means you can consolidate the whole thing. Well, I guess, well, I'm not going to do that. I'll just show you one last thing which we had in Power Query, which is a bit of sorting. But let's see. So if instead of coming to what's it called a PDF, we can actually connect to a folder. So you can connect to a folder that has all the PDFs, right? So if I click on more, I connect instead of connecting to just one PDF, we connect to a folder, we connect. If I connect to a folder, I then I browse, I go to the desktop. Remember, it's inside our webinar, we have a PDF folder, I would say, okay. So remember, this had all the PDFs inside. So you can imagine, you have all the data for the last one here from the Nigerian Stock Exchange, dump all of them there and say, okay. And this thing now goes to that folder. And we're going to be now, remember this is data one, data two, data three, I'm going to click here, combine and transform. And then it's going into that folder. But it will know that, hey, what's inside that folder are PDF files. So it's going to go and parse that PDF file and pull out all the tables. Remember, this is a sample, this is first file, all the files, you have a long list of all your files. But now we need to come to here and say transform. And remember, when you go to the folder and transform, it's going to say it's going to create a sample. And then you're going to be transforming that sample and it's to be like doing whatever transforms you're doing to the sample, if you do that for all the individual files. So this is Power Query. Remember, you see, you have a transform for the sample, this is your sample data. And then this is the whole PDF data. This is what we did before, right? So you're transform, you come to the transform. Remember, we only need tables. So we remove page, we this table only need this column, we remove the rest. And then we expand this column. And then we say, Hey, I don't want this. So remember, whatever we're doing to one is doing to all remember what we did the same step we did. And now we said, it's okay. Then we promoted our headers, use first row as headers. So this is our first row. And then we said, Hey, anywhere else that has symbol, let's remove it. Let's see if symbol is at the bottom. It's not there. Is it there symbol symbol SSS symbol? Yeah, click, right. So let me show you something else. This was one of our other tricks. How do you let's assume you want to sort this alphabetically? How do you even this heading? So you have bonds as would be then s then n. You can actually sort all your headers alphabetically.  Then there's one new thing here called choose columns. And then when you have choose columns, you come on that choose columns. So various choose columns here, there's a sort thing here, they just added. So if you click on a to z, right, you say sort by name. And then this whole thing gets sorted. Maybe this doesn't make sense to you. But really, it's just a nice trick, right? When you say, Okay, it has sorted all your columns. Now, let me undo that because maybe we don't need that, right? But that's a nice trick. But what this has done guys, is down here, can you see the error? You see that change type always always a problem that chain type. So don't don't don't do that chain type thing. When you remove that chain type, then you can come here and actually change the type. So your source name, this will become your files. Yeah. And then what I suggest is you remove all those pdf.pdf. Well, you can leave it doesn't matter. Then what do you do? You kind of detect your data type. So you click on this control a and tell it to detect on the solution to come here and detect data type, where we detect data type. So it detects all the data type. And then guess what this is your console a data. This one is your sample. So if you want something done on everybody, you do it here. If you want something done on the sample, before it becomes everybody, you do it here. And now your data is done. You come to home, you close and load, close and apply. And this has been added to your data model. And now you're ready to do analysis on the stock exchange. When you get the new pdf from the stock exchange, you dump it into the folder, come here, refresh, everything is done. And you're ready to work. So productivity is significantly improved when you walk with Power BI, Excel, and especially Power Query.


My name is Walter Rubrik. I'm a professor here at the Naval War College and director of the Arctic Studies Group and co-lead scholar of the Newport Arctic Scholars Initiative. It's the first initiative of its kind that brings together sailors and scholars from Arctic nations to conduct collaborative, multi-disciplinary research on security and defense-related issues. The sailors bring kind of the practical knowledge, the operational experience, the real-world experience that their navies are dealing with today and you pair that up with some of the brightest and best minds in the region and you get a pretty unique and strong force there. My name is Lars Sornes. I'm a former CNO of Norway. I'm co-leading this Newport Arctic Scholar Initiative. The Arctic is developing fast with the climate change and I say climate change and serenity issues in the Arctic is like ying and yang. So what's new in the Arctic? Well, the Arctic is opening up, establishing new frontier for nations to cope with. So if you look at from the Pacific, what does the Arctic mean for them? You can look at from the Russian side. They have a piece of it. You can look at from the U.S.-Canadian side. They have a third piece of it. Or you can look at from the Atlantic side, which is the blue, more high in order Arctic. So they all distinguish different regions, but no one has before taken whole Arctic approach to the problem in the Arctic. So that is what we're trying to do to see when you get new frontiers, the ice is diminishing, you open up for exploration. These frontiers may create security issues among those nations. So what we are going to study is what Arctic risks and opportunities as the Arctic opens. My name is Gary Vapishi. I'm a Strategic Analyst with Defence Research Development Canada, based in Ottawa, and I work with the Royal Canadian Navy. The objective of the Newport Arctic Sea Power Initiative is to enhance cooperation and deepen our knowledge of different strategic issues in the region, including awareness, readiness, as well as confidence-building measures in order to enhance cooperation and ensure a peaceful, stable and open Arctic. And so the objective is really to see how we can work together and to deepen our knowledge on these issues in order to inform our governments. The Arctic is changing and changing fast. There is a rapidly melting polar ice cap. There is the shortest trade route linking Asia, Europe and North America. We see an increased abundance and distribution of fish and minerals. Potentially one-third of the world's untapped hydrocarbons. And so these factors coupled with a new era of strategic competition among great powers brings new rise and new significance to the Arctic. And so it's important that our decision makers think about the Arctic as we think about our broader grand strategy. What's happening in the Arctic has a global impact. You can look at it from so many angles. And I'm so thrilled to have scholars and military professionals from all these countries coming together share their experience, knowledge, for the benefit of us all. That is really the key of success today.