Who gets to decide what's normal and more importantly, why? Why even? When we work we often have to make the joke, shouldn't we include some non-disabled people? No, we should not. All the regulations for inclusion and for inclusive education should just be practiced. They are there, but they are mostly not practiced and it should be improved. And this is our experience, that you have to fight for a lot of things that make your life possible.  If I can sit in there for 18 hours maybe, or if I get pain after 2 hours it makes a difference. Yeah, but in that you need personal assistance, a lot, and I think politicians need a lot of personal assistance in order to get an emotional or rational touch with the real people. Would you consider like a political career in any field? No. Welcome to Standard Time. I'm Reiko Kingopop, your host and editor-in-chief at Eurazine, the force behind this show. Eurazine stands as a unique online publication weaving together over 100 cultural journals and reaching audiences worldwide. We're also proud to be one of the founding partners of the Display Europe platform, showcasing European content in 15 different languages. Today we talk about disability and disabled people's right to self-determination, but let me start by laying out the ground a little bit. What counts as an able body changes throughout history, and even more from place to place. For instance, with my eyesight in some countries I am considered legally blind twice over. That has never stopped me from driving a car. Well, not having a driver's license has. Not having a car even more so, but that's not my point. Disability, physical and mental, acquired or congenital have been with us literally forever. A growing pool of archaeological evidence, some dating back tens of thousands of years, show that amputees and people living with Down syndrome and many others have been integrated members of their societies across historical time. Some people were always born differently, April, and others were injured, hurt or otherwise affected. Disability is not an anomaly, it's simply a part of the human condition. But for disabled people to have self-determination and control of their lives, well that's a tougher topic. Eugenics was an incredibly successful movement which characterized people as either fit or unfit, thinking that any deviation from what they designated as unfit would lead to genetic degradation. At the height of its success, people as prominent as Alexander Graham Bell, a deaf educator himself, argued for preventing the creation of a deaf race by forbidding marriages between people with disabilities, and Bell was actually one of the milder ones. About 250,000 people were murdered in the Holocaust simply for being physically or mentally disabled. Up until a few decades ago, institutionalization and forced sterilizations were usual treatment across Europe. Today the situation in the European Union is vastly different. The EU guarantees a whole set of rights for disabled people and has initiatives like the EU disability strategy. Activists, self-advocates, NGOs and communities have played the biggest role in improving the situation, but it's still far from a bed of roses. Disabled people face a higher risk of poverty and social exclusion. In Bulgaria, up to 50% of people with disabilities remain in socially dysfunctional situations. Estonia, Latvia, Lithuania and Croatia also record figures up to 40%. Women with disabilities in Europe also face a range of tough challenges. Disabled survivors unite, a UK-based disability rights NGO revealed that women with disabilities in the UK are more than twice as likely to experience violence and abuse than others. They have a harder time finding a job and when they do, they tend to earn less, have fewer chances to study and struggle more to get the healthcare they need. Many live in households where work is scarce, especially in countries like Ireland and Belgium. And even those who do have jobs, like in Romania, still face a high risk of poverty compared to their peers without disabilities. Medical needs are another pressing concern. About 7% of women and 6% of men with disabilities in the EU report unmet medical needs, with even higher levels in some countries. A mere 2.3% of senior officials or managerial positions are held by women with disabilities. Today's guests lead the charge in this uphill battle and they do not apologize for wanting a fair treatment. Bernadette Feuerstein is an Austrian disability rights activist and public official, known for her contributions to disability rights here. She has been playing an important role in the Austrian disability movement, including her involvement, creating a barrier-free Austrian documentary on the topic. Balte Matis is an actor, art therapist and a cartoonist. He's also a community worker and theatre trainer who has worked with people with addictions and in penitentiary facilities. Maria Dinalt works on the integration of people with and without disabilities into cultural life and to promote social inclusion through artistic activity.  She is also the president of both the Austrian branch and the European Association of Adopted Physical Activity. So let's see what it takes for disabled people to take matters into their own hands. Very welcome and thank you for coming. The most spectacular part of disability integration are physical accommodations in buildings. These actually make everybody's life easier. I'm sure that it will be a benefit for everybody. The Vienna underground system had the old cars with a step. Now they have the new ones without step. And it's for everybody, it's easier to get out and get in. And so the Vienna Linea, the public transport systems, they save a lot of money with this. Is disability actually that rare as we sometimes talk about this? I think the quote of people who are born with disabilities is not so high, luckily. But there are so many other incidents that you can get disabled. But it is the society who is responsible to help. I think the important social thing is that the children are included and are seen as equal as other children and other persons. All the regulations for inclusion and for inclusive education should just be practiced. They are there, but they are mostly not practiced and it should be improved. Normalcy or what we consider an able body is an arbitrary thing. So it changes over time, the standard really changes. I wonder if this argument in your head rings true about it. I'm used to wearing glasses and wearing a prosthetic for a lifetime. I'm born this way and because my kind of disability, you cannot see it at the first glance. So very often there was no problem at all. I was kind of integrated at all. The prosthetic stuff, it improved I think every year and now it improves every month. New materials, new technologies, I have a sensor system. Many things are great, evolving very great, but I think in the conscience of the society, there's a lot to do and we have to present us as disabled people. We are here, we have to be visible. Bernadette, do you think disability is visible enough? Do you find representation enough? For my opinion it's not visible enough, but it's improved. When they built the underground system, there were only two stations who had an elevator and it wasn't allowed to use the underground with a wheelchair. So it was necessary that the community of disabled people may protest against this and we did strikes and we blocked buses and so on. And with this system people have the possibility to get out, to move around and so they have the possibility to be seen. The understanding of disability is more wide, it grows. For things to change meaningfully, you need it to protest, you need it activism. So it's not like the benevolence of the state. Activism in self-advocacy plays a crucial role in pushing things forward. The community has really a very powerful and important role. I recommend everybody to visit the parliament in Vienna. It's really a very good example of accessibility. There it was an international day for people with disabilities and we were thinking about that, I don't know, maybe 30 years ago there was a hunger strike in the parliament to get equal law for people with disabilities. If I may add to this, I think if you are a person with this consciousness of disability but there are people, children or people who cannot speak for themselves. That's why the society has the duty to acknowledge the rights and of course if you can do it yourself, it's more effective because then they see exactly. But often the politicians, they don't want to see, when I teach my students I always mention, okay there is no special needs, everybody has special needs because he is an individual and that's important I think. I used a wheelchair for about two months when I broke a leg and then you become very aware of the built environment around you. It was just such a harrowing experience and this can happen to anybody at any time but Maria you also work specifically with physical education and dance and creative movement and you work towards the integration of people with disabilities and very different abilities in culture so that it's not some peculiarity. Can you tell us about this, please? Yes, I think with our association, I've been okay, it was founded more than 40 years ago the founder was Catalinsanin, she was from Angkor, you know people with disabilities can also dance and they can express themselves. They have their special expression and they have their gifts and their need to express themselves and to move and to show what they can do to put them on stage and put them in the view of the society. We do have an actor here with us in Walter who is, you know, is human. You pass, you know, you don't show like very visible signs of disability having been born with one and you are a successful actor. Does your experience as a disabled person play a role in your art? I think maybe I have a special sense for justice, exclusion, inclusion. This is a topic, I think, I feel this all the time. I work with an acting company with disabled people also. They are now founding a kind of academy for an acting school, acting academy and I'm one of the teachers. When we work we often have to make the joke shouldn't we include some non-disabled people? No, we should not. And the needs are so normal. There are no special needs. But does this kind of social connection that you mentioned, does this come with anything special that you observe you pick up on? I read about children, asking children, staring children. I know this very well. I often go to the children, what's up? What do you want to ask? But when I was a child and other children asked me, I didn't really feel comfort. Yeah, and many people talk about inactivist communities and marginalized communities that they don't want to have to educate everyone in the street. You don't have that level of energy all the time. Bernadette, do you sense this? People expect you to explain everything to them? Like I am forcing you now? Yeah, that's correct. Sometimes I tell them interesting stories I just invented. I will not ask anyone how it's possible to have sex. That's the weird thing. People ask about such intimate things. It depends on my mood how I answer. For me it's one of the most important things that you let the children grow up together. I was in the luckiest situation that my parents take care that I go to a regular kindergarten and primary school and gymnasium. When I compare with my other colleagues, with my disabled colleagues, they had, sometimes when they came out from institutions, they had so much to do to overcome. We are very thankful to our partner, the Alte Schmide here in Vienna for their support hosting the show. Located in the city's heart, the Alte Schmide is more than just an art venue, it's a thriving center for culture, bringing together a lively community of artists and intellectuals. With its commitment to contemporary art and literature, the Alte Schmide offers a lively forum for exhibitions, readings and interactive workshops. To discover more about their exciting events, visit thealteschmide.at Bernadette, you are one of the important bull-bearers for this goal of self-determined living. In English it's called independent living, and we say it's easier in German, self-determined leben, self-determined. I do not need a doctor or a nurse or who else a physiotherapist to tell me what is good for me and what I have to do. I know by myself how I will arrange my life, how I will organize, and for example I'm here today with my personal assistants because I need a lot of assistants. I will not be live alone, but with the assistance of a personal assistant. Maria, when you worked with disabled dancers or not just dancers, anybody interested in movement, that means that they have a physical disability but they don't want to abandon physical activity. They want to have control and proficiency in this and they want to express themselves. I think the question is the difference is if you have a permanent disability, I think you cannot be healed. There is no therapy to get non-disabled, but you can also get a lot of improvement of quality of life. In our association we have many of these young adults, most of them then work in this sheltered workshop. They like to come to the dance and in the afternoon they are allowed or in the evening, but when we go away for taking them to Special Olympics or to some event, they have to get permission from the workshop and they don't allow more than a certain amount of days. Just yesterday I had a discussion with the mother. The child, the girl was ill and then there was a rehabilitation center and then she left a lot of time for the workshop and so they said, you have to pay for the other days where she is not here and this is stupid because these institutions, they get their money according to their persons who are there and if they lose their person, they lose their money, but the person also loses the place. We always try to be in contact with companies who are offered normal jobs or jobs for those who can work for 5 or 10 hours a day and they are very happy to have their work there. Maybe worth saying that a disabled person doing a job, they are not getting a favor for being able to do labor for somebody. When you enter a new situation, a new interaction and people don't know you in advance, do you have a strategy to introduce yourself or when you would introduce the fact that you live with a disability that people might not see or is that something that you just leave up to them discovering? Is that something you lead with? Oh, it depends on the situation, it depends on the surrounding, it depends on the person. Well, it depends, really, it's improvisation. I improvise. But does this hinder you in a new situation where people don't know you?  You like to talk to... No, different stories, as she said. Sometimes it's a shark, sometimes it was a chainsaw. Ah, I love it. You teach German as a second language to adults. Yes. Can you tell me about this experience because you work with people who come to Austria and start to learn German as adults, sometimes not necessarily on their own volition being here? You mentioned this? This has to do with my profession as an actor because the normal career of an actor goes this and now I'm here and so I have to find a way to pay the rent. Teaching German is another example that accessibility is a benefit for everyone. So if you use easy language or easy reading, it will not only help people with maybe intellectual disabilities but also people who are not speaking German as a first language. Definitely, it's like the ramps or the elevators for people with broken legs. The same man. So I am aware that there are initiatives specifically in terms of language to provide better readability. That becomes way more crucial when it comes to dealing with institutions. How is your experience in dealing with these institutions? Of course we have the mobility aspect, like the building has to be accessible. Benedett, you're already waving your hands so I think you have a lot to say. I think it's also getting better because authority is understanding that it's important to reach the people they are dealing with. For example, I know from the social ministry we are working there are some pages in easy reading. Those who are working in the healthcare system for and so in culture, it seems important to have the connection between the theory and the practice so that you have a lot more knowledge but you have to adapt and to apply what you learned in the way so that the persons are understanding and can act as they are not. Healthcare sometimes can be a conundrum in its own. So how is your experience with going through these systems? Do you experience any different kind of treatment? Walter, for instance, you? Well, the normal health insurance, they will pay me only the minimum, maybe just a stick, the insurance for pension. They will pay me some better advice for this system, for this sensor system, for this sock that gives me information about the contact to the floor. I had a lot of phone calls with health insurance that I want to have this, I need to have this. It's good for me so that they pay for it. This was a process, yes. But you have to fight for these things? I had to fight. Well, you have to really put effort in it. And this is our experience, that you have to fight for a lot of things that make your life possible. For example, wheelchair isn't, it's really a part of my day, of my life, and if I can use it or cannot use it, then if I can sit in there for 18 hours maybe, or if I get pain after two hours, it makes a difference, yes. Additional to the normal social welfare, there is some associations like Lichtenstunkel who allow the people to get more. There should be more responsibility of the regular social welfare to allow special equipment for those who need it and not to go and bid and have private sponsors for this, yes. I think Austria is one of the most richest countries in Europe, or I don't know, and it must be possible that everyone gets the maximum of support what is needed. Who should be the judge of this or what would be the appropriate procedure? People with disabilities should, on all steps, be involved in decisions. And I'm sure that it will change for independent living Austria. We try to be involved in important political process, but then sometimes it happens that we put a lot of work and energy and knowledge into a project and at the end the politicians or other authorities listen to us but they didn't change their way. And this is really frustrating. So it's kind of for show that they signal that they have given an audience and then that's it, kind of. Walter, when you say that you have had to fight for your accommodations and this is an ongoing thing, do you meet with people on the other side who understand your situation? Is it the institution more or is it the people more that keep you back or give you a hard time getting through to what you need? I think it's part of the institutional kind of life, not to move, not to change, not to support the people, so just to support the institution, that's my impression. So it's not individual, horrible people sitting there trying to give you a hard time? Hopefully not. But when you get through, do you... Is it a situation where you can actually find allies or is it just something to just march through whatever happens, what is your experience? A bit of this, a bit of that, yeah. I'm talking to people and I get some support and help and comfort and sometimes I have to go through alone, yeah. It would be very recommendable that those people who decide upon this have a broader view. They should look about Europe and the world, how it is managed and how it is possible. Difference between the regulations in Germany and in Austria so that people with disabilities can be employed. There is their regulations, how many in a company has to be employed, if not they have to pay something. One company in Austria pays $55,000 per month because they don't employ people with disabilities and in the same company in Germany, they have regulations more evident so that they overfill their duty. You thought that was all we had on disability? You couldn't be more wrong. We also have an extended conversation with the founder of the active amputee, Björn Ezer. Listen to it on the programme of Gogarin, the Eurazine podcast. When it came for me to take the decision to have my leg amputated against the advice of most medical personnel, I promised myself, as far as you can promise yourself something when it comes to health, that I would not let the amputation stop me from doing things if there's no medical reason for it. I think it would be amazing and something I would like to see is if globally mobility wouldn't be a luxury. In our conversation with Björn Ezer we discussed prosthetics and outdoor sports, policy and advocacy and why mobility mustn't be a luxury. I don't know how much attention you all pay to this but there has been a rise of influencers talking about disabilities and chronic illness in social media and some of them have huge followerships and a lot of people are learning about the intricate details of living with a disability or a chronic illness or any kind of health condition for the first time online. How do you feel about these people? Do you think it's something that helps the situation? Do you think this should happen on a different level? It might be helpful but it might also get into the direction of a freak show. For the young people who get a lot of influence by them I think it could be very helpful to understand the situation and to learn.  Is there a specific place that all would like to see more disabled people represented? In politics. In politics, they should be more visible. Our parties, they choose a special person who is responsible for the disability questions. Sometimes they just choose a wheelchair user. There should be a continuous education also to become politician and to stand for the case. I see, Bernadette, you need personal assistance. A lot. I think politicians need a lot of personal assistance in order to get an emotional or rational touch with the real people. Would you consider a political career in any field? No. No, no, no. It's an interesting field but sometimes it's too exhausting. You're doing a lot of work and nothing is changing. Bernadette, what is the political position that you would wish right now? I don't know if I want to take an important political role because you are working in a very complicated system and it's so hard to change anything. Maybe I think Chancellor will be good for me. Okay, I support you. I don't have the right to vote but if I did, you would have my vote. In the municipality we have a commission for their kind of councils for the authority of Vienna. I would like to be there to tell them the new streetcar is a catastrophe and as an actor I would really like to show them what's going on. Drag them on with you for politicians, for designers for people who decide for us how this kind of streetcar looks. But there is a specific streetcar that you have a problem with? Yes, the new ones there. All the ones there, the floor is leveled and now there is no even ground. Crazy. Okay, that should have occurred to someone quite soon because there are also accessibility requirements there. And not only pirates like me are complaining about, also older people. It's interesting you mention pirates because when we talk about how disability should be more visible there are these very classic characters, theater characters and characters in acting that are by default disabled that's how you recognize them and the pirate would be one of them the veteran would be another, right? Do you find these roles specifically suitable for a disabled actor or do you do you ever get cast in these, any of these? I did in the past, yeah. And how do you find them? Like when you have to sort of play into the character, is this comfortable for you? To be disabled actor is a kind of not disabled it's a special qualification kind of. On the other hand, why should a person with two legs not play a guy with one leg? I think in most of the TV shows there are two less disabled people. Talking about the actors I was thinking about the cultural adaption who is allowed to play what if a disabled person to play a not disabled person if, for instance, a person with Down syndrome can play the person with Down syndrome it's perfect. And also in music and in dance I think I really love to see performances with people who have no legs for instance. I think we have to see more artists with disability in the movies and also in TV. But I think it's more interesting to see Romeo, a blind Romeo I think disabled people can play not disabled people and not the other way around. Thank you so much. This show is presented by Eurazine a platform offering insightful articles from over a hundred partner journals in multiple languages. You can be part of this intellectual journey by visiting patreon.com to become a patron starting at just 3 euros per month. For this you'll get access to bonus materials early releases and future perks. Our show is a display Europe production it's an innovative platform dedicated to presenting content with a strong focus on data privacy and across 15 languages. We are very grateful to the Autismida for hosting us today. This program comes from both the creative Europe program of the EU and the European Cultural Foundation. The opinions and views expressed here are those of the authors and the speakers only and they do not necessarily reflect or mirror those of the EU or the EACEA.


Hello and welcome to another D Brown consulting webinar for the Excel and Power BI webinar series. This is the September 2018 edition, and we're going to talk about 10 things you didn't know Power Query can do. Right, so what are we going to talk about this month? It's all about Power Query, so we're going to do an introduction to Power Query for you that don't know Power Query, and then we'll look through 10 things you didn't know Power Query can do, or maybe you did, but interesting things about Power Query. First thing we talk about is call up function and help, then auto matching, headings, and consolidating sheets, data exploration, summarizing data, consolidating from a folder, and then looking up when you're trying to look up two tables when they don't have a unique reference. It's like a VLOOKUP without a unique reference, but VLOOKUP in Power Query. Creating columns from example, something called Flash Fill in Excel. You could actually do Flash Fill in Power Query now, very cool. Using the choose column button, so now I mean you could filter out rows, now you could filter out columns, very interesting. View relationships between queries, you want to see almost like a data model for the Power Query, something like that, we'll see that, and then consolidating worksheets in Power BI. It's a bit different than consolidating worksheets in Excel, see the slight difference between the two. That's what we're going to do this month. If you're following, just sit tight and enjoy. Let's move on. How many of us here have used Power Query? I'm jumping to the next step now. Power Query, introduction to Power Query. This is Power BI Desktop. This Power BI Desktop and Power Query, if you go to home, you see Power Query under your get data. Everything external data, enter data, so this get data is Power Query. If I open Excel, Power Query was born in Excel, really Power Query is an ETL, Extract and Transform and Load tool. It's an excellent, excellent tool. In fact, most people always have problems with their reporting because their data is not structured right. For the really very advanced users of Excel, they use VBA and use VBA to clean the data and stuff, but it's not as versatile and cool as Power Query. Power Query is excellent. Even in Excel, so it was born in Excel. If I show you Excel, this is Excel and Power Query is right there under data, get and transform. All of this is Power Query. It's exactly the same thing as I just showed you. Everything here is the same as the two Power BI Desktop. What we're going to do Power Query, let's see, what's the first thing we're going to do? We're doing 10 things you didn't know Power Query can do. And number one in our list is what? Let's see, hold the control and click. Well, guys, now clicking, let me just manually go to number one. All right, so number one is call up function, help and using M IntelliSense. Call up function, help. So let's assume you don't know the function you want to use. Let me just go to a blank query. How do I get a list of functions? I don't know the function I want to use.  Yeah, equals to hash key shared. Now I don't know why they called it shared. They should have just called it equals hash key Power Query functions or something shared. When I enter, you will see a list of everything Power Query. I mean, all the functions are there. So the nice list of functions, all sorts of stuff. So really, if you want to see this as a table, let's convert it into a table. Yeah, so I convert it into a table. And if I was in Excel, I could just load this into Excel and you see all the list of functions. But really why I did that is if you click on any one of this, you would see the explanation of that function, really. So let's pick something. Let's do, let's come here and do a list. We're going to look for a function called list dates. It's going to list dates. So I'm just typing list there dot maybe dates. So here, this is the function I want to see this one. So if okay. So this list days, I'm just going to click on the function button here. So this is the function. So I just click on the function button explains what the function does. You can read everything about it here. So let's create a list of dates, right? I want to create a list of dates.  When are you starting? What are you starting? So let's assume I want to start on the first. Now look at this, the input value one September did not pass using local English United Kingdom and short date format DMM MYYY. Hmm. Now, all this is telling you this is quite annoying. This is saying that the way I'm writing my date, the computer basically knows it as United Kingdom dates. But this system is looking for this button here is looking for an American date. So I just, I just have to manually type let's say one slash nine slash 2018. Now, for most of most of you to work fine, but for me it's not working fine because I'm using the UK dating system. My UK date, if I go to my control panel, you'll see that my date is UK, but I won't go into that. It's looking for a U.S. dating system. By the way, one nine 20 18 is my computer's dating system count. Let's say we want to start. Let's say I just want 30 days, right? 30 days. And then my step is one day each. So 30 days starting from first of January. Give me 30 days, one day per interval. Okay. So see what it does. It gives me first of September to the end. How did I know that? Because I went to look for a list of functions. I wanted a list of dates and we see now I have a list of dates. I can now convert this to a table by coming to the top left and saying to table. So now this can just ignore this and say, okay, so this is now a table. I can double click this heading and say this is my date. Enter. And then I can convert this to a data type of date instead of any I call party to date. And I'm in business. So this is my date and I can close. Let me close and load close and apply or just apply what I've just done. Let's call this a date list or well, I've converted to a table. So it's not really a list, but when we date table, right? So date table. So you get the idea. But really, we got this by using function we identified, we had a list of functions or something, list functions. Yeah. Now it's our first trick. So I enter. So list functions that we have it. Now if I go into the view section here and I go to the advanced editor, you will see all the M code in here. See all the M code. Now this is the new look M code for the September update. You have all these colors and everything because it's now has IntelliSense, right? So it has IntelliSense. Anything you do, I mean, it's kind of will help you really helps you move on. So look at this. I'm typing name. You can see all the different help here, date dot month dot name, anything to do with name in Parkway will come out. So this each, for example, I mean, this is, I could delete that just to show you. And if I type each, I'm starting to type is giving me some help. Well, not very smart help, but well, you still help. So each. So once you type your queries now, it gives you like Excel, it helps. So that's IntelliSense. Really cool. What you say, now you sure you want to cancel? Yes, I want to cancel. That's close. All right. So that's my first query, guys. Let's go back to Power BI and see what's the next thing on the agenda. So that's number one. Number two, not apply any changes. Let's say number two. The second thing is we want to auto match headings and consolidate sheets in Power BI. What do I mean for me to show you that I need to open something in Excel? So there's usually a problem in Excel when you're consolidating data, when you want to auto, auto match certain things. So let's say this is my spreadsheet. And right now I have my name, my department, my title, and then I have total gross, basic housing and leave, right? So G has leave, for example, if I go to August, August G for August is not leave. So let me zoom in a bit. So you see that. So if you check my August, the column G is housing. In my May, column G, what's column G? Let's go my May column. Column G is housing, okay, well, okay, it's housing for G, right? In June, column G is not housing, column G is transport. So column G is transport, see? Right. So here we have, if I go to March, let's even go to March. If I go to March, let's see what does March have to offer. So in this March sheet, March is even a little bit more confusing because if you look at March, it says the first column A is title, name, and then department name. If I go to April, column A, you know how very annoyed you are in your office when someone gives you a really hopelessly scattered table. Look at this. It's name, department, then title. But in March, it's title, department, and name. And then everything else is scattered. We know that the tables contain everything. It's just that it's all kind of messed up somehow. So power query will help solve this for us. So let me close this. By the way, if you want to do a query for an Excel workbook, or so you have to make sure it's closed, has to be closed first. So we're not doing the query from Excel. We're going to do it from here. So auto matching headings, that's what we're doing, consolidating sheets in Power BI. So two things in one, we're auto matching as well as consolidating sheets. So what do we do? Well, we need to connect to Excel because the data is in Excel. So get data from Excel. So where we're getting data from, I have all my data here. And this is its auto headings recon. So I click on this, and I can just double click on it. So it's going to open it. So I connect to the auto heading sheet, right? So all of this, we want to kind of consolidate it. So you click on the auto heading. Now, if you click on the auto heading, if you look here, I can't edit. Now, what's happening? I can't edit in Excel. I'll be able to edit. Really, there's a trick. All you need to do is right click on the folder, this folder, and that's the edit. So as you say, edit. All right. So it's up. There we go. So we've opened it. And now we need to kind of consolidate it a bit. Right. There's something about this trick. When you want to consolidate and keep the headings and let it, you look at the headings and everything, that's only works when you're using table. Now, for those that know Excel a lot, in Excel, when you have data, when you have data in Excel, if you convert it to a table by doing, by just clicking anywhere within the data and going to insert table, there's a special thing that table does. So this data is all the same, right? But this is a sheet. This is an Excel sheet. And then this is a table in Excel. So we need to filter out the sheet because it doesn't work with sheets. It walks with tables.  Just take note of that. So we are going to leave, we need our data. And I think we need our item. We need to know what month it is. We just want to have that. So I'm highlighting this holding my control key and clicking the next thing and then removing other columns, right? So remove other columns. And then we can now expand this. So we expand. And usually I like to remove this original name prefix. We don't need that. So we have all this data here. We're expanding and we'll say, okay. So I've expanded. And if you look at it, we have our name, department, title. Believe it or not, everything would have consolidated nicely. And then we have the item there telling us which month it is, right? So this thing is, you'll be wondering how possible is this, right? So if you remember, name, department, title, right? So this department police, but let me filter by March. You remember that March was really kind of hopeless. Let me load more. Let me filter by March. And March and June. Let's just see March and June, right? So let's go left. So it's in name, department, title is properly done. It's changed it and made it all look and feel correct, right? So let me remove my filter. I'm going to delete this step. So this is the query. This is the query for consolidating. Pretty simple and pretty cool. So that's auto headings recon. So that's our second query. Let's just apply. Let me close here, apply. So we'll move down to, let's get back to our Power BI to do the next one. And then we'll do the next one. So let's see the next one. So data exploration, understanding data table profile. So sometimes you just want to see the profile of whatever it is. What's this query? Let's have a look at what it is all about. Just a quick profile look. How will that work? So let's check. So let's use a query to determine, let's, the last query we did, let's see if we can check the profile of the query. Okay. So let me go into get data. Let's go to the query. How do I quickly get back to my query, edit queries. Let's view edit queries, data source, query, query, query. Where's view my queries? Have a view button. See, I'm used to the Excel bit where I can just view it. So anyway, I can view my queries. I have to just right click and edit, but there's a view query button. So here we are. I want to know the profile of this query. So let's duplicate this query. We're going to duplicate this query. Okay. So I've duplicated my query, right? And so this is my query. Well, I've, well, let me just find what profile is this query, this second query. Let's test it. So let me come to a new query. Let's go to, I'm going to add a new source, right? New source, I'm saying blank query. So I'm just going to write a blank query. And this query, when you want to test the profile for something, you just say equals to in the blank query equals to table, unspelled table, table dot profile. And then I open my bracket. And normally I open my bracket and then I just type the name of the query, right? So I test in auto headings recon XLSX. So if I do that, auto heading recon XLSX. Now close my bracket. Unfortunately, I don't think this will work. So let's look. See? Yep, it didn't work. And that's because our name has spaces. If our name didn't have any spaces, it will work fine. But because our name has spaces, we need to do some funny additions to this code. We have to put a hash here. Then we now need to put a double code. And then we go to the end of the code here and now put a double code. And by the way, you shouldn't put a dot in between this recon XLSX. So if I enter should work this time. So now it's given me analysis of this data saying that minimum April set, of course, we won't have that basics and stuff. It's not really useful, but this just identifies or gives you detailed analysis of that query data. So here, if you check, I mean, sales department, like education, the minimum is null, null, null, null. All of this is null. If you look up at the data, the one that is actually working here, look at it, the count 1.2, 1.2. This anytime you see ABC123, ABC123, it's very annoying. ABC123 is a very, very wicked data type. Let's go back, come here and check. Here we have ABC123, ABC, these are all you should always do when you do your queries, highlight and change the data type. Never leave any, any is terrible. Make this text. Now these are ABC123, ABC123, you can do maths with ABC123. ABC123 means any. Make sure that your data types are the end of your query. Once you finish your query, highlight everything and make sure the data types are correct. So a decimal, maybe this is a decimal number, right? All these are decimal numbers. This guy can be text, which is already text. So that's key. So I'm going to apply this, apply. And because this is my new query that I did depends on this. If I come back to this query, and then let me apply. So now, I mean, look at all this. No, no, no, no, no, no. That doesn't make sense. It doesn't make sense. And here we're talking about, if you look at this, this is auto-reheadings recon, reconXLSX. And I think where I went to do all those corrections was in also heading reconXLS2. So I didn't correct this one.  So we do that same thing here. Or I could actually change the query. So this query should be looking at the one that ends in two, right? So if I come here and type, type two, remember, we clean the data, I mean, the data types for two to enter. Yeah, but you see that's working now, right? So too much duplicates kind of gets people confused, right? But anyway, so this works fine. Let's call this one profile. Let's call this profile. All right, so moving on, let's do another one. So moving on, let's get back to Power BI. What's the next thing on the list? So we're going to summarize data. How do we summarize data? Let me go back to our query. Let's see if I can borrow a previous query we've used. How do we summarize? So, yes, if I look at this auto recon, we know that this auto recon is from January to December, right? If you remember this January, if I load more, lots of things, this is all this month. So what I want to do is just consolidate everything. I don't want to do it in Excel. I want to do it in Power Query. So if I want to consolidate, basically that means I don't want January, but I just want everything together. So, frankly, before I do that, I will need to delete this column. I don't need it by months. I'm going to remove this. It doesn't add any value. And if I go to the beginning, I'm going to consolidate everything. So the first thing you should do is all these numbers, all these values you see should be in a single column. How you do that is you highlight your text. This is your unique ID for the transaction. Highlight all three of them. You right click and then you on pivot other columns, whereas on pivot other columns. So you've unpivoted this. These are now your line items, your payroll line items, pay lines. And then these are your values. Now, can you see that ABC123? It's so annoying. If I leave the ABC123, it's going to mess us up. ABC123 means anything, any value. Never ever use data type of any. You will go very far in your Power Query analysis. And then you're like, what's going on? It's because you can't consolidate with any. You need to consolidate with decimal number. It's fine. Decimal number. So change that. Very important. This guy can see ABC123. In fact, all of these texts, all these are texts. So really, all this should be text. Right? So data types, just note one thing. Data types, data types, data types are extremely important. So how are we going to do this? We need to do something called group buy. Right? So here we are. Now, what you want to do is tell it to consolidate values. Right? So if I come to group buy, really, I'm going to group buy. So group buy is like consolidation. So group buy. Now, if you notice here, group buy, pay line, you choose which pay line. Oh, am I grouping by? The thing with group buy is you need a unique identifier. After uniquely identify each line so that when we're grouping, we know that look at this guy. All this Cabra Michael, Cabra Michael, Cabra Michael, we need to sum up all of this Cabra Michael. But it's not just Cabra Michael. Well, Cabra Michael, police, maybe there are two people called Cabra Michael. But hopefully there will be one Cabra Michael as the police department and is a captain. And we want his transport allowance. And guess what? All his transport allowance for all the various months we run that not just this is just one month. All the transport allowance for every single month. So we need to kind of join this and join this and join this and join this. Then we now group. Now, I think it's always better to use advance to go to advance. Then you're saying I want to group by, let me say name, they add, I want to group by department add. Now, what I see is this is kind of a waste of time. Before you group by just highlight all the columns, you know, you want to group by. So name to a line, you know, all of this should be grouped together. And then you're now consolidating the value, right? So once you do that, click on group by and it goes to advance immediately and types everything for you. So that saves you time. So right here, we're here. And what are we doing? We want the total or the summary. Really, this is the summary. Yeah. And here I go to my operation. I want to sum. What do I want to sum by? I want to sum by the values. So we're grouping all this, and then we're giving us a summary, which sums all the values, right? Say okay. What happens? It's basically going to give us, this is the consolidated gross pay. This is the consolidated basic, the consolidated transport. Everything is consolidated, not monthly anymore, fully consolidated. So that's how you consolidate, right?  We're going to call this consolidated or summary. Let's just call it summarized, summarized data. Summarized data. I also advise you don't use spaces in your names. Our query doesn't like that. Anytime you use spaces, the code just changes. The code you use in another query will change. So anyway, summarized data. That's cool. So what's next? What's the next trick? So trick number four or trick number five, down four, trick number five. Just to recap for you guys, the first trick was call up function help and use the M intelligence, the new M intelligence for September 2018 Power BI update. Second trick we did was auto match headings and consolidate sheets in Power BI. The third trick we did was data exploration, understanding data table profiles. The fourth trick was summarizing data. And now the fifth trick is the next one we're doing is consolidate from a folder. So we'll do this trick next. Okay. So consolidating from a folder. So we need to go to query and see how we can consolidate from a folder. So I come to home. I go to edit queries, edit query. You know, we've already done four nice tricks. If you are following on, this is trick number five. So number five, we're consolidating from a folder. So I'm going to add a new source. Now, if I was doing this from Power BI, it wouldn't be adding new source. I'll just do a fresh one. But anyway, let's add, I can add a new source. In fact, let me show you that. Or in Power BI from here directly, I can get data. So it's the same thing really, it's still going to go to the query. So get data and we're consolidating from a folder. Now in Excel, it looks a little bit different, but Power Query is just the way it looks. So I need to go to more here in Power BI. Go to more. And then I can check for a folder. By the way, in 2018, now you can consolidate from PDF. This is just crazily amazing. I mean, I have one file that has about a thousand pages for the government. And that's the government's budget. I'm going to go look for that file and see how I can extract everything from that file into Power Query. So you can consolidate in a folder. You have some tables in the folder. You use Power Query to pull it out of that PDF and then pull it out of the PDF, consolidate from PDF. Anyway, that's cool. Probably next webinar as we'll see how that works. We right now want to consolidate from a folder. So click on folder, click on connect. I need to browse to go and find where that folder is. Browse, I know it's on my desktop. Power BI, I have consolidated folder. So that's this one. So I click OK and say OK. So it's going to get ready to edit my folder. It's going to open Power Query. Now I can do combine, but really I don't advise you do combine. Just go straight to edit and let's type the code ourselves. Okay. So we're going to edit. Look at all this. The data I'm consolidating, payroll data, edit. So edit opens Power Query. I have everything. Really the key thing you need is this binary. All this is just extra, extra information. You could leave the name if you like, but really I just want the binary. So I'm just going to right click and remove all the columns. And then I expand this a bit and see what happens. We're trying to expand. Now look at this. This is the data here, but really I need to extract information from this. Expanding really won't help me. I need to extract data from inside this binary. So I need another column. So I'm going to go to add. I'm going to go to custom column. And I'm going to add a column. The name very doesn't matter. It's really, I want to get out the workbook. So we say we're extracting, right? Let's just say we're extracting. Let's type we're extracting something, right? Now there's a code, a function to extract your workbook. It's called Excel dot workbook. Then you open your bracket and then the context, right? Now here's where there's another trick. Now, if you followed good practice when you're creating your data and all the data that you're extracting are inside tables. If they're inside tables, well, that's fine. Or if they all start from row, really they all start from row one. So if they start from A, that's row A in your Excel, in your Excel, which I know all my data starts from row A, then row A will have a heading. So row A has a heading. And if row A has a heading, which means the very first row always has a heading. So the way to tell power query that look, row A has a heading is to add to this code and say, comma true. So if I say comma true, it just knows that, okay, that's the way of telling it that row A has a heading. So if I click okay, it's going to try and pull out the table here. This is really what I need to expand. I don't need binary anymore. So I'm removing that. Then now if I expand, you'll see that this is what's inside. And really, I think we should keep name and data or maybe item. Item, I think is better. So I'm going to on select or say, I want data and item. Item tells me a little bit more. The rest I really don't need. Well, let me just, if I cancel this and just show you what you have in here. So if I click on table, so this is really what you have. I have name April. I have a table. I have April. Again, this is the item April, then sheet, then false. I really don't need that. I'm going to kind of close this, come back to this. And then when I expand, I said I want just data and item. And then I say, okay, so it's going to expand. And then I know expand this table again. So it's going to extract all the columns in the table. And then I say, okay, so here we have it. This is consolidated everything in the folder. And all you now need to do is ensure that your data types are correct. So this data type is going to transform or form, make sure that this is text. Then from this cross pay all the way to the end, this should be decimal number. Then I can bring this guy back to just want to move it and moving it all the way to the left. So it's next to the text. So I'm bringing it here, just dragging it here. And here we have April and bring it here. And then I make this guy also text. So that's how you consolidate from a folder. It's pretty straightforward. Yeah. And then make sure you change your data types. That's very key. So consolidate folder. I think that's the name. Let us apply.  So I'm just applying the steps. If this was Excel, I could extract everything and take it to Excel immediately. And that's our consolidated data. All right. So that's consolidated folder. Let's see what the next trick is. So the next trick is fuzzy lookup. So we're going to do a lookup without a unique key. So I'll show you the Excel files first. And then we'll see how we're going to do this automatically in power query. So let me open. I have some Excel files. I'm interested in the lookup Excel files for referee and results. So if you're a football fan, you'll understand this data. This is, I have dates here. I have my home team, which is Manu. Yes, I support Manu. I'm sorry for you guys that don't. Anyway, yes, yes, we're in trouble nowadays, but we'll come out of it, you know, can be winning every time. Just, you have to give other people a chance, right? So anyway, our way team is Leicester. So we have home team, our way team, we have our home score and our way score. Now anytime we lose, I'm sure it's the referee's fault. So one thing we would like to have is the name of the referee that really manned this whole matches. We need to know the referee's name. I need to know the referee's name here. So where do I get that data? Well, I have another table called referees, which all this data is from the internet. We downloaded it. Ahmed downloaded it for us. Check Ahmed out. Really cool. He went and downloaded football data. I can't remember what team he supports, but anyway, that's fine. So date, home, away, and referee. So if you look at this data, you have your date, the match happened, home team, away team, and referee. So this is uniquely identified. These three uniquely identifies the data. Now if I was doing VLOOKUP, I mean all I do to get referee in here, if I was doing VLOOKUP, I want referee, referee. I would need to concatenate, if you guys don't concatenate, concatenate these three, have it as another column, then do a VLOOKUP of that to the other sheet and blah, blah, blah, blah, blah, blah, and then get the referee. I'm not going to do that. No, thank you very much. I want to do all this VLOOKUP without a unique reference in power query. So let's see how that works. I'm closing this. Don't save, and I'm closing this guy. And yeah, I can do it here in Excel. You know power query is in Excel, but I want to do it in Power BI. So I'm back here. So look up without unique key. How do we do that? Let's go to the query. Edit query. So we're going to bring in, let's just edit query. I'm going to bring in the data, obviously. So and then we're now going to do some interesting merging. So here I'm going to get data. New source is in Excel. I need to browse. This is the lookup. It's referee data and result data. So let's take the result data and I'll just bring that in. This is my results data. I'll just tick it and say, okay, bring the result data in. Let's call it. This is called results. This is fine. Let's bring in the other data. So get another data. This is my referee data. Right. So this is referee data and result data. So if in Excel, I could be probably managing this data and this data keeps growing. So that's fine. So here I want to combine the referee information. All I need from this is this referee. I just want to add this referee to my results. Yeah. Right. So how do I do that? Let's create a new query. So it's like I need to take results, starting from results, and I need to group. I need to merge. I need to merge my query. So I'm going to create a merge query, but instead of overriding this query, I want to create a new query, merge queries, but as new, as a completely new query. So I'm going to merge query and starting with my results. I want to merge it to my referee's data. And here I'm just going to do a left outer join. All these fields, see these fields here all the way here. I'm merging it to this fields here, all these fields. Yeah. One, two, three, one, two, three. And I'm doing a left outer join, which means this is what I want to join to this one. Right. So anyway, let's see how that works. Let's say, okay, so the selection has matched 790 out of 790. This looks cool, doesn't it? That means all the data here is all here. It's already matched it. So this is VLOOKUP, right? So click OK. And what happens?  Let's just call this the, I call it the lookup. We're not done yet, but I'll just call this the lookup. Okay. So I enter lookup. So this is the referee table. I mean, the referee data I brought in, if I expand this, all I need from this, I don't need the data. I have the data. I don't need home. I don't need a way. All I need is the referee's name. So click OK. And see the referee's name comes in. In fact, I can take this guy and take it to the left so that now I have eight home team, away team, referee, and then my data. And these two files are still there. See, these two files are there. They can keep on growing as they like. And then this is my third query, which is my lookup query. Perfect. And I can load this and do all my analysis I like. So that is how you do a lookup even without a reference. It just created a reference for yourself instead of doing all this in Excel. It's far better and more efficient to do it here in Power Query. So I'll apply the steps. As a result, this has applied all of them. So we've done our query. So in fact, all these three, let me create a folder. Let's be a bit neat here, because I needed three things here. So I'm going to create a group.  I'm just going to call it lookup, right? And how to do the lookup in Power Query without, even without a reference, right? Even without a reference. Right. So I click OK. So I create a folder. And I can now take these three guys. I'll just, unfortunately, I can't drag it. Maybe later we can drag. But for now, I just have to right click and say and move to group lookup. Right click and move to group lookup. Right click and move. Come on. Move to lookup. To this, I'm just being organized, right? So that's lookup. So what's our next, what's the next trick? We've done five now. Or is it six? I think we've done six. So number seven, also number seven, create columns from example, Flash Fill. Interesting. Let's show you how it's done in Excel. Let me open something in Excel so you understand what Flash Fill does in Excel. It's such an excellent tool in Excel. Many people don't even have forgotten about it or don't even know about Flash Fill. So look at these names, right? These common names we have. Let's assume I just want the surname. And then I want the first name. So I want the surname and first name. Now, if you're very good in Excel, right, you would do some really fancy formulas. Now, let me just, should I show off my Excel skills? No, not OK. Well, what is the delimiter? There are two delimiters here. What is stopping? How do you know the surname or how do you know the first name? Well, the surname has a comma after it and then has a space. So what you could do, you could search for, let's say you could search for comma within this guy, right? So when you search for it, it says a seven. So if it's seven, that means you have six characters for the surname, right? So if I drag this down a little bit, you see that this is seven, seven, nine, nine, nine. Oh, that's strange, all nine. And then this is seven, right? Now you have seven and nine. What kind of names do we have here? OK, 11 and five, better. So seven. So what we now need to do is come in here and then say, you know what, I want you to extract from the left, extract names from the left. And how many characters are extracting? This is seven, remember? Minus one. And we close our bracket and then you get his name. Right. But this is too much stress. And so what Excel did, which is so cool, you should just type, just type Cabral. So I'm just going to type Cabral. Then I type a second one and that would be like an example. I type Fallon. And guess what? Excel has an idea. It says, hey, I know what this guy's doing. And let me give him an example. OK, I will show him. If he's happy with what he wants, then he should just type Enter. So I just type Enter. I guess what? It just came out. Perfect. So first name, same thing, same trick, Michael, or Michael, sorry, Michael. I'll spell it the other way. Now, another trick, a shortcut for Flash Fill is actually Control E. So I don't need to give it another example. Like a sales teller, look, do you know what, use this guy and just fill it. Control E. So it did Control E and it filled out. So this is Excel. It's quite impressive in Excel, yes? But let's do all these tricks. Let's do the same trick in Power Query. You don't want to do Excel because Excel means you need to do this every month. In Power Query, it is automated. So just know that Excel is good, or Power Query is better for some things. So I'm going to close this. Don't save minimize. And let's do this, creating columns from example Flash Fill inside Power Query. So I go to Edit and Edit Queries. And then now I need to go and look for what file should we use. Let's see. See this. Yeah, we could use this guy, right? Yeah, so let's use this. I'm boring from this. I don't know who used this. Can you remember? Well, there's a trick that would tell you, okay, who is using what? So we don't mess things up. I'll show you that trick later, but I'm going to duplicate this just in case. Duplicate, right? And then I'm going to rename this guy. Let me call him Flash Fill. I can't spell Flash Fill. Okay, so Flash Fill. I need to fill this just like I did in Excel. So how do I do that? Well, there's a wonderful new tool. I can't remember when this was added, but columns from example, column from example. Column from example is a new tool. So I click on the dropdown and I say from all columns or from selection. Well, I've already selected this. This is the example I want to use. So I say from selection. So what it does is just gives me this box here. And inside this box, all I need to do is type. So I just start typing just like Flash Fill. Cabby cabral, right? And then I enter cabral. Enter. When I enter just like Flash Fill, if you remember how we did it in Excel, all I need to do is now say okay. Once I say okay, see it's filled it out. I can call this the surname, right? Then I can still go back and select the same column like this and then say I need another example from selection and then this time around, obviously, I'm doing the surname, right? Oh, the first name. So it's a my, my call and enter. And you can see it's already filled it out. You just needed one example and just say okay. And then this is the first name. Now remember, this is power. I don't really like space. So this is power query. So when your data grows, you never, ever need to do this again. It's just automated. That's why this is more superior than your Excel. So if I drag this to the left, let's just put the names close to the surnames, everything is recorded. And if you look to the right, you see that this is being recorded. And in fact, let's even look at the formula, the M code, you know, power queries language is M. So let's have a look at the M code it used to do this automatically. If I click on this, this is the M code up here not the M code. Let's click on where is that insert text delimiter. So look at the M code it used. Now, if I put on the magnifier, let's just see if you can see that.  So this is the M code looks ugly table dot add column rename columns with the hash, then text after delimiter each text dot after delimiter name comma space. So all that story just see the space, just that space now similar to how we did it in Excel. But that's power query. Then you can actually use this to learn how to write M code. Okay, so that is how we do it. I go to number eight is use the choose column button. What do we mean by that? Let's have a look at it as an extra choose column button. Right. Let's see how that works. Okay, so let's go to query edit query. I don't think I saved the last query. So let's come to home here and just apply any other steps that I did. I think I did some steps for flash fill in the last video. So flash fill. Here we have. So here we want to talk about choosing columns. Now, one big thing when power query was being developed, you could kind of highlight like this and delete. But what about if you had a hundred columns, then you need to kind of highlight a hundred columns and delete. So what they invented, which was cool the first time around was they kind of allowed you to highlight the columns and do something to other columns. So you could right click and say something like remove other columns or you could say on pivot other columns. Yeah. So that was a very cool move. But then sometimes, right, you actually want to remove just this guy and this guy and another one somewhere else. So what you could do instead of doing that is you choose your columns under home as a new button here called choose columns. I'm not too sure when it was added, but choose columns. So if I go to choose columns, I can actually, are you sure you want to insert a step? No, no, no, no, no, let's cancel. So look at this. I'm in a very different step here. So I have to be at the last step. So I was already in a mid step. So I do want to insert any steps. Let's just do it properly. So I come here to choose column. I say choose column. Then you can actually choose which columns you want to keep. I can remove this. I like this guy, I like this guy, I like this guy. And this guy, no, I don't want this guy. I want this one. And I want this one. And I want that one. So you are choosing your columns. And once you've chosen and you say, okay, it's basically written the code here. It says table dot select. Let's see how this looks. So look at what is writing here. It says table dot select columns, reorder column name, department name, title, leave, detail, blah, blah, blah. These are the columns you have chosen. Isn't it? So that's how choose column works. Pretty simple and very useful. All right. So what's next? Let's save this. I did this on Flash Fill, right? I shouldn't have done that. I should have just given it another name. I said choose columns. All right, Flash Fill. I'm going to duplicate you. Duplicate. Then I'm going to call this one choose columns. Choose columns. That's another trick, by the way, duplicating columns. And then I go back to Flash Fill and I rewind. So you can press rewind. I've deleted this. I press rewind, reorder columns, fine. So Flash Fill is fine. This is to choose columns. And then I apply all these nice queries I've done. I'm applying everything up to date. Yes. And then what's our next trick? So our penultimate trick, what's it? It is view relationships between queries. How do you view relationships? We've done so many queries. What's the relationship between them? Let's have a look. So view relationship within queries. How do we do that? So obviously I said view, right? So it should be somewhere in view. So if I go to view, then you can see query dependencies. So click on that and then you have this dialog box. Let me maximize it. This dialog box, I can zoom in a bit so you can see it. So this is a mapping of all the queries you have. So this is one source. If I hover over it, you see this is my source called Consolidate Folder. If you had watched all the videos from the beginning to the end, you would see that we did a Consolidate Folder and this is it here. So this is just the direct link. Then we had, I did a lot of stuff with one file called autoheadings recon. So autoheadings recon, we loaded, we did summarized data with it, we did autoheadings recon, we duplicated it, we did a flash fill with it, we did choose columns with it. This auto recon number two, we did profile with it, we created a table profile. So we use this quite a lot. We can see the mapping of how we used it. What else did we use? Here is lookup. So this was lookup. If you remember the referee stuff, we had results for our matches, we had the referees and then we did a lookup. So you see how nice and neat it is? So if your queries are nice and neat like this, you can know what is depending on what. So if I come and try and delete this, for example, it won't allow me to delete this query because lookup is dependent on this. So if I want to delete this, I must delete this first, right? So that's how you query dependencies. And that's how Power BI and Excel manage all these query dependencies. You can see the mapping. It almost looks like Power Pivot. Yeah, Power Pivot data model. Very cool. Right. What's our last and last tip? Let's see our last tip. So the last tip of the day is consolidate worksheets in Power BI. Consolidate worksheets. Well, we did consolidate in Excel and we found out something. So let me just quickly show you that. So if I go to Excel and you try and consolidate, we go to get data, you're consolidating from a folder or you're consolidating a workbook. Consolidating a workbook and I want to consolidate O2 headings recon, for example, right? So you get this dialog box. Yeah. Now, in this dialog box, I want to consolidate all these sheets. So definitely I'm going to select this. Once I select this, I edit. That's Excel. Let's do the same thing in Power BI. So if I'm in Power BI, I want to consolidate. I go to get data. I'm going to get data from Excel. And then I am going to come here and I want to consolidate based on O2 headings recon, right? Same thing. Exactly the same thing as I just did in Excel. Right. So same look and everything. But when I click on this, you see edit is not activated. Why is that? I think that's a bug. I'm not sure. But I think it's a bug. Look at this. This is the Excel version, right? April. See edit. But if I click on this where all the sheets are housed is the workbook, I can see edit, right? And I can click on edit. But I click on edit, this is Excel. But I click on edit in Excel, it brings up the query, which is fine, brings up Excel query and is ready to move, ready to roll. This is Excel. In Power BI, the same thing I did in Power BI, I can't click on edit. What I can do, though, is I can right click on this and then edit. So this is probably a bug. So this last one was really to just showcase a bug. Click on edit. And once I click on edit, yes, you can see that Power Query is working exactly the same way as I have it in Excel. So this is the Excel version, right? This is the Excel version. And this is the Power Query version, right? So I guess, guys, we are done with the demos. These are all the demos. Let's see what's the next on the next page. So about the sponsors. So all these webinars were sponsored by dbrown consulting. We do training, we do consulting, we do payroll, we have the analyst training hub, we provide tailored training for the modern analysts from zero to AI, we like to say. So provide Power BI training, financial modeling training in Excel and all the other kinds of training. You can check us out at www.dbrownconsulting.net. And if you want a free Excel course, just go to www.officetraininghub.com. We get a free Excel course there. We also do payroll. We do, we have a special software where we can guarantee confidentiality. We also use Power BI to kind of consolidate data and clean up data and automate reports for various clients. So that's what we do. We have various affiliates. We have some of our courses are CPD certified. And of course, some of our courses are also certified by the financial modeling institutes. We do the exams here in Nigeria for national modeling institutes. The new body go to FMinstitute.com. Excellent body that has certifications in financial modeling. We are also Microsoft partners and we're also affiliated with the ATD Association for Talent Development. One of our cool tools we have in Dbrown Consulting is we have a model, we have a methodology for training, which is pre-training or we have to do a pre-training, which is a pre-work, which we do online before you come for a classroom training. And then we have various things that will make us identify whether you're applying those skills. So it's our PTA system, very cool system. You can check it out. You can send us an email to training at Dbrown Consulting for more details for our PTA system, which also has four proprietary reporting systems. So our reporting system is in Power BI, very detailed reporting system. So please contact us. This is our contact details, where Dbrown Consulting, you can call us on 0700 training. If you're calling from outside Nigeria is plus 234 700 training or just email training at dbrownconsulting.net. I'll go to our website at dbrownconsulting.net or you could go online and get some nice free courses and some other paid courses on officetraininghub.com. I'll advise you do the report automation in modern Excel courses and excellent course. I'll teach you a lot of the tricks you've seen here. So thank you very much guys. We've managed to do quite a few queries. I did various stuff, various tricks that Power Query has that could be hiding and we also showed you everything using Power BI. And again, as I said, is exactly the same tool in Excel. Although we just saw a quick bug where Excel seem to have trumped Power BI. Well, I'm sure it's just a tiny bug that Power BI team will find it and correct it. So thank you. I've been David, your host. And this has been Dbrown Consulting. And we hope to see you again soon. And next webinar is the third Thursday of every month. Every third Thursday of the month we do these webinars. This has been 10 things you didn't know Power Query can do, sponsored by Dbrown Consulting and is our Excel and Power BI webinar series, which is held every month on the third Thursday of every month. This is the September 2018 edition. And we look forward to seeing you next month. Thank you everybody and bye-bye.


Good morning, everyone. It is Wednesday morning. I am currently getting ready. It's like 8 a.m. Today, um, I have to take my car in to get some estimates, and then, um, I wasn't gonna walk, but I woke up like 30 minutes too late, and I just don't know if I'm gonna have time for that. So that's my bad, which I hate when I do that. Um, and then I'm recording a podcast at noon, and then I'm trying Barry's Boot Camp for the first time. Kinsey and I are going. I've heard amazing things about it, so I really wanted to try it. It's really great having, like, workout friends because they're all, like, down, you know? And then I need to come back home and, like, do some laundry and clean up my room and whatever, um, and, like, finish up some stuff because Bethel Conference starts tomorrow night, and tomorrow it plans during the night. So I'm trying to get a lot of stuff done tonight and be productive, like, laundry, and, like, not that fun of stuff to do. But that is what is on the agenda for today. I'll probably include a little bit of, like, tomorrow morning in the vlog because I'm actually working out at Paulina. Tomorrow morning, which I'm really excited for, Paulina and I haven't, like, hung out or talked to, like, literally it's, like, two years, I think. We were on the phone the other night for, like, two hours, so I'm, like, really excited to see her and to work out and stuff. I'll vlog that, but, like, I'm not gonna vlog the rest of tomorrow. So, if you guys are new here, be sure to subscribe. Um, that's pretty much all I have for you guys right now, so I'll see you in a sec. Okay, so I have my bag for the gym tonight all packed up, and I'm going to put my laptop and stuff in here because I don't want to have to bring a backpack. I don't know. I don't want 17 bags, so whatever. So, basically, everything's in here. Um, I just don't want to have to bring 17 bags. This bag is literally the best thing that ever happened to me. It's juicy and I love it. Okay, so here's my outfit for today. I just threw this jacket on because it's a little bit chilly. Um, I'm gonna take the jacket off. Actually, I'll show you without the jacket. So, I don't know where my jewelry is from. I think these earrings are Princess Polly in the cross. Necklace is Princess Polly. The stars are probably Brandy and Melville. That's just a guess, but I think it's right. My top is from Eritzia, and then I have some biker shorts on from Eritzia. I'm gonna put on my Adidas Falcons, but they're in my car, so I don't have them right now. And then I did like my two minute makeup look, but I also, yesterday, I bought like Yeezy 700s. I don't know. I saw them online. I really like them. I didn't even know, I like know nothing about any of that. I just don't care, but I didn't even know. Well, I guess I did know that Yeezy. I don't know. I never really cared about Yeezy's, but I did like these. So, I bought them, and I don't know when they're gonna be getting here, but they would be perfect for this outfit. And they're like the better version of like Falcons, basically. They're very similar. And if I wear those shoes, like as much as I have, like I might as well buy like a nicer pair of them, you know what I'm saying? So, I did that, but I'm gonna head out to a body shop and get some estimates. This is so fun. I have my coffee, my purse. I just put this in here, and then I have my iPad. I do so much off of this thing. Literally, it's my entire life. I just, it's the best thing ever. Okay, I'm finally ready to go. I have my coffee. I have my gym bag, all my laptop, my iPad, everything ready for today. I'm going to go to two auto shops to get, ooh. Oh my gosh, guys, my shoes have been shipped. They're gonna get here on Friday. That's so exciting. So, I'm like not excited for this since like a very adult morning is something that I don't want to do when I'm putting off like all week. So, I'm gonna go do that. Just be an adult, you know?  I just got off the phone with the insurance company. So, literally like in December, I sidestepped a car, but it was like literally not before the worst day of my life. So, like I've not gone back to do it and I'm getting taken care of and then also someone else in my car, not good. So, all that's gonna take care of. So, anyways, I was on the phone with their insurance company getting all that figured out and I'm such an adult. But it's only 9.58 am. This happened. So, I went like by way quicker than I thought. So, I think I am gonna take Coco on a little walk before I head off to the studio. I am really excited for today. I'm actually, I was gonna record with Electrolocy, but we moved that. Also recording with a therapist, which I feel like it's gonna be such an awesome episode and like just really cool. Like a lot of people have a lot of questions and it's like a very easy like accessible thing and I'm so excited to see Taylor. I'm always so excited to go to the studio. So, I'm gonna do that. I'm actually also gonna text Kenzie and be like, do you wanna work? Okay, so, I'm on my way to the studio. I'm gonna stop by Sweetgreen and get lunch that I can bring to the studio because I'll be there for two hours.  Not even Sweetgreen sounds good right now and that's like literally my favorite place to eat in LA. The Hollywood Bowl is the best thing ever and I'm sure it's really bad for you all. I'm gonna ask Kenzie today and I'm already bracing myself. I'm talking about Kenzie Burke by the way. If you guys watched the last vlog, if you guys watched Maggie's vlogs, Kenzie Burke is the food combining wellness gal that we all love and anyways, watch yesterday's vlog to like whatever but we're meeting up today to like do work after.  Here we go. Having a little lunch break on the rooftop. My favorite salad in the world, the Hollywood Bowl. Guys, we're in a new studio. This is my guest. Do you wanna introduce yourself? Hi, my name is Guyana. I'm a marriage and family therapist and I specialize in couples. This is gonna be so good. If I was dating someone, it would have been so funny but you know, I didn't do that one. So we're in the studios. Taylor is really hard at work right there. Always working. Always working and we have some, oh my gosh, we have some nice blue bottle. Taylor, I was booking guests. Like, this is actually really funny. I was booking guests the other day and I was like, we have to move it because I want to make sure Taylor's in the studio. And then they listened to Michael and Lauren and they were like, oh my god, is this the Taylor who like peed on himself or whatever in the wedding? Yeah, it was a wild night in Mexico. Yeah, so there we go. It's all over. He really has a following himself. I do. I've been on a lot of their episodes and it's, trust me, people, I get Instagram, you should create your own podcast. Like, I have the resources. I haven't done it but... You honestly should. Taylor would be really funny. I always, I think about that. People have said, you should just do it, come and record a couple of them and see if you like it. So I might try it. You honestly should and it will be kind of cool because it's like, you have like the real inside scoop on everything else too. Yeah, that is true. So it's like behind the scenes. Yeah. I was thinking, Michael even once said, what we should do is you should do like an after show after their show. Like an after show and that's actually a good idea. That would do really well and people on there like love you. Okay, cool. Guys, I'm already having such an amazing day, like literally the best day ever. I'm always in like the absolute best mood whenever I leave the office and I was like talking to Michael and Taylor and I hadn't seen them like since the whole Forbes thing and we were just like, we were being so like obnoxious about it. Taylor and I more specifically, they just said Jessica Abba, which is cool though, so I need to snub so today. I'm on my way to join the juice to meet Kenzie to get some work done. It's funny because in the studio, Taylor, Taylor's my producer for those of you who don't know, tails you down on Instagram guys, go follow him, he's so funny. And if you guys listen to this, it's gonna be confidential, it's the same Taylor. But he, normally I have like 15 Taylors in my life. So like normally it's an issue with having too many Taylors and now Kenzie's gonna come to the studio and there's gonna be too many Kenzie's and it's just a lot. So anyways, I'm meeting her at join the juice. We're gonna get some work done and then we are going to go to berries and I haven't tried berries before, so I'm pretty excited. I love rain, I love life. Okay, I'm just like literally I'm so happy. Okay, all right, so I need to get some stuff out of my gym bag. This is very, I like literally love this gym bag, it's my life at this point. Back with Kenzie. We just finished working, oh wow, at join the juice. I'm like, okay, no ticket, that's so weird. They just aren't charging for parking, is that? Do you want to just drive together? Or do you want to drive? I'm scared. Yeah, that's true actually. I don't trust it. I don't trust it either. Maybe parking on Melrose isn't normal. No, it's literally not normal. We're on our way to a relieving to go to berries right now. It's my first berries class. This card's gonna hit me. Update guys, parking stopped. It was really, we were really stressed out for a second. Like it was stressful. It was very stressful. We made it. Here we go. Just finished berries. It was a really good workout. It was tough. There's a lot of running and basically it was been half the time running. I think we did like two miles with interval sprints and then it was just like weights.  I honestly, I like the vibe of berries more. I don't think I really like that location. Like I'd be willing to go to a different location. I think I'd like any location better than that one. The parking like sucked. The garage was so annoying. And I don't know, like I feel like I would just like the other locations better than that one. It was a really good workout. It's pretty comparable to orange theory minus the rower. I think I like orange theory better still, but I did really like berries.  So anyways, I'm also trying a new workout class in the morning. You'll see that I haven't done Rise Nation and it's only like a 30 minute workout class and Paulina said, and I quote, neon lights and lots of rap music. So I was like, all right, we'll go. And then we're going to like Band-Aid and stuff. But I'm on my way home right now. I need to just clean, do some laundry and finish up some last minute stuff. I just need to have like a productive night and get some stuff done. So that I am literally passing gravel right now. So then I'm able to really chill because I'm going to have like four or five days in a row basically where I can't just like work work at all. Really. So it's kind of like a mini vacation, like but like a staycation for me. So just getting some stuff ready for that. Okay, I'm home from the workout. I just did a lot of dishes and I put one load in the laundry. I have like three loads of laundry to do tonight. And I really wanted to get it that early. So we'll see what happens. Currently drinking my last bottle of protein college and water and I just made I have no groceries. I'm going to be gone. So like I haven't gone grocery shopping. I literally just heated up like rice and I thought I want it like it's literally the weirdest meal I've ever made. But that's all I had and that's what happens. But I'm so excited because my rug came in from Reggable. It's going to go underneath the couch. I don't, you know, I should not do this myself, but I'm going to. So we will see how this ends up working out. This is the before and I'm very excited to show you the after. Reggable is like actually really, really cool. I'll tell you guys more about them. But basically like you can wash all the rugs and they're like a really good price and I love them. Like I want Reggable rugs all over my house. I want like an every room. I'm obsessed with them. And this room desperately needs a rug. This looks so cluttered and that looks so cluttered because that has to be plugged in there and it looks so ugly. And then that's just the studio. So anyways, it's going to be great. Also, I want to replace this because it's falling apart. Let me know what you guys think I should do. Okay, I'm watching my friend Peyton's vlogs. Let me pause it really quick. So the rug is here. I love it. I want to get like prints for over there to make this room like more mature and stuff. So this is the room. At first, I wasn't sure how I felt about the rug. And then when I actually like laid it out, I love it. This isn't the same rug that Olivia Vargas got. Unless I ordered the wrong one, but like I actually love it. And it would look like amazing with a white couch, which is what she has. This also for softness needs to be this entire situation needs to be fixed. But I just think it looks so cute. I wish that when you look that way, it wasn't just, you know, like that. See if I would have gotten the right size roll. It wouldn't have been that big of an issue. I need to throw away these boxes now. But I really like this. I think it's cute. It's a little bit messed up because it goes on basically the way this works. The regrettable is that like velcro is onto this thing, right? And then you're able to take this off and wash it.  But I really like it and I'm very excited. I'm really excited for the new prints over there. I really want to get rid of this clothing rack. I'm actually just going to like throw it away. I don't want it. And that's also colored like that has such good memories. Okay, let me show you guys this. I feel like you never really get a chance to see it. But like it just, it's so cute. And we have great like just funny things like here's photos of me and Joe throwbacks. Like, oh my gosh. This is so funny. There's Ryan and Quentin. That's like literally so funny. That was our commencement last year. Me and Joe walking in the neighborhood. There's some photos of us in like New York. This photo is like three years old. That was my 20th birthday. That was Christmas break 2016. One of the best things. And then we just have like really nice, like people just leave like nice notes and stuff. This is Jonathan Martin. He's like a great pastor in Oklahoma. If any of you guys live in Oklahoma, check him out. Also, there's Coke on my 21st. Like it's really good memories, but I just don't know if it's like what I really want anymore. This also needs to get fixed. I'm just like over the house not being fixed. I love this. I'm obsessed. I actually love this area. And can we talk about the fact that Joe and I have still, it's really because I don't have the knife for it and I'm just so bad at it, but we still have not fixed these tiles and like it's ridiculous. Like we just need to finish that. I'm going to make that happen like ASAP. But I absolutely love this. So I just showered and I'm in the middle of doing, two loads of laundry. I guess I was just being dramatic. So I showered. I'm just in sweats and a crew. And like this is like, you know, I really need to do laundry because I always sleep in matching pajamas. So it's like it literally will change your life.  Anyways, I have Frank Ocean on. I've been playing Frank Ocean a lot lately at night. I actually brought my laptop in my room because I just wanted to have a chill night and I have some stuff that I need to get done. So I'm like edits and whatever. So I'm just going to hang out in here. And get all that done. It's okay. I really don't like doing this that much. But tonight I'm kind of in the mood for it. So it's fine.   But I'm in my room. I don't know if I'm going to play TV or anything. Play TV? What? That's not so weird. But I am getting some work done. Okay, so this is my current situation. Everyone, it is like 6.40 right now. I woke up at 6 to do some laundry because I am going to work out and I needed my outfit from the dryer. Not because I really wanted to do a laundry that early. If you guys are new here, be sure to subscribe. Oh wait, this isn't even a new vlog. I don't know why I'm starting this like it's a new vlog. I forgot that it's just going with yesterday's vlog. I'm just going to be vlogging the morning today because I'm reuniting with Paulina. Every time PB pops up on my screen, it just feels right. So we're going to do that and then we're also going to try out Rise Nation, which is a class that she loves. It's only a 30 minute workout so I was like, sure, why not? And then we're going to Bandir. It's going to be great. But right now I'm currently pre-uploading a vlog and then I'm going to head off our class. I don't know why I said it was 6.40. It's 7.15. I woke up at 6.30. But our class today is at 8.15. But it's in West Hollywood so I have to drive and it's going to be like the worst time of traffic. But I'm listening to this kind of confidential episode with Jessica Alba that Michael and Taylor told me I had to listen to. Today Ann and Kendi Burke told me to listen to. So I think it's a sign. I think it's just an amazing, she's an amazing girl. I love those people. I feel like I talk about how I love... Where's my thumbnail? I think I talk about how I love skinny, confidential and all of them. Like obviously the podcast but also just them as people so much. Like Michael has done so much for me. Like literally the best people ever. So I'm going to head off to work out. I'm on my way to class listening to music. It's such a good drive and an amazing morning. I hope you all are having the best life ever because I am. I'm really excited for this workout. Pauline and I literally texted each other at like 8 p.m. last night and we're like, are you in bed too? Walking in.  No, exactly in the... Alright guys. We just finished Rise... I look at this Rise Nation. I did boxing class. I used boxing. I'm going back. I'm going back. Yes, yes, yes. I'm going back. I'm actually coaching and like training and technique. I'm like into it now. It's like basically I'm treating it as a sport. It's very exciting. It's my new hobby. But I went to Rumble the other night. Anyways that was really good and I like how it's only a 30 minute workout class. But it's so exciting. You know what? I wore a tracker. I burned 600 calories. Dang. You know what I remembered? Like as we were doing this in those 30 minutes do you remember when we would do Peter's spin class if he spoke and we would do his lunch break one to 30 minutes? I loved it. Those were, oh my god. 30 minute workouts were normally harder. They just come in and they're these ocean women who like we're so they're like come in their work attire. Yeah. Shower and be like fully glam. They were, yeah, fully glam. They were so incredible. Honestly. I wish you would do like three a day. I need to learn. Yeah. Cause we would sometimes we would go and multiply and we'd see them. I know that. I really want to just catch up and see where they're at now. Peter's in New York. Oh wow. Yeah. I wonder if he's teaching in New York. He probably is. I don't follow. Guys we made it. It's a coffee overdose in the new Van Deer store. Other stuff. Stop working here guys. But. I'm going to vlog right now. Woohoo. It's so cute. I don't know if the oatmeal is in there. She like left the oatmeal. We got our ice coffee with oatmeal in CBD oil. Yeah. Which is very controversial. It's fine though. We are literally at our third cafe in the past 30 minutes. Like actually we got. We love diversity. We love diversity. We got coffee overdose coffee. I wouldn't mind it. I wouldn't mind it. I wouldn't mind it. You should have told her. I should have done it. I don't agree with this. I don't agree with this. I don't agree with this. I love mornings. I don't agree with this. I love mornings. No. I like. We literally got ice coffee. With CBD. With CBD. So that is really us for you.  Okay guys. So I'm going to end this vlog here. I had the best morning of Paulina. We literally went to three coffee shops. But we worked out. We just went on like a nice walk on Melrose. And it was just amazing. And I'm so excited for Bethel conference this weekend and stuff. So I hope you guys enjoyed this video. If you guys did. If you didn't know. By giving it a thumbs up. Subscribe. Comment down below. I just realized I tried to end last night's vlog. Comment down below. Gucci shoes running from your shoes. You guys made it this far. And if. And comment on my Instagram for bonus points. So I can stalk you guys. Okay. Love you guys. Bye.