Important: This post was written and published in 2020, and the content below may no longer represent the current capabilities of Power BI. Please consider this post to be more of an historical record and less of a technical resource. All content on this site is the personal output of the author and not an official resource from Microsoft.
In case you didn’t have any plans for the weekend, the Power Query team has released a treasure trove of new documentation for dataflows in Power BI and Power Apps.
There are over 20 articles in this new resource, and the content they contain answers many of the questions I hear most frequently. For some reason WordPress isn’t letting me upload the screen shot I took of the content outline, so instead here is a picture of me when I first learned about this new content:
Please check out this new information, and please submit feedback to the documentation team using the feedback form on each page. When you let the docs team know what you like, what you don’t like, or what is missing, they read every comment!
For the second question, you need to understand how we got to where we are today – how the data and BI technologies we use today evolved over the past decades. And that’s where today’s video comes in. Check out a brief history of business intelligence for context on the problems that a successful data culture needs to solve, and what your starting point might be.
I won’t go into depth here (I spent six months on that video, so darned right I’m not going to spoil it!) but there are basically four phases in this brief history:
The dark ages, also known as “back when I was your age” when there was no BI, and decisions were made based on human knowledge, instinct, and experience
The good old days, when BI solutions were developed by highly skilled BI professionals using complex and difficult tools, and when ETL consultants could charge exorbitant rates without clients blinking an eye
The SSBI era where every business user got up to their elbows in data, and IT wasn’t slowing anyone down any more
Nirvana, where IT and business work together as partners, each contributing based on their skills and priorities to common shared goals
And each phase brought (or brings) with it its own challenges:
Dependence on specific individuals with specialized knowledge, slow decisions, and probably giant hair you spent hours sculpting with Aqua Net
Expensive development efforts, long turnaround times for insights, IT becomes a bottleneck
Inconsistent results, untrusted (and untrustworthy) insights, producing misinformed business decisions
Difficulty in aligning business and IT and making the necessary cultural changes… also flannel?
And for that third question, please let me just say that 2020 is kicking my butt in ways I never expected. Between work and family and the global pandemic that’s ravaging the globe, I’ve needed to harshly triage all non-essential work. Back in January I was sure I would publish this video a week late, but still in January. Back on February 24th I made this optimistic comment:
In case you’ve been wondering why my blog and YouTube output has dried up this month, it’s because real life has been kicking my ass. I think I can finally see the light at the end of the tunnel, so hopefully we’ll be back with regular content before too long. Hopefully.
I’m tempted to say I again see the light at the end of the tunnel, but I’m not going to do that. The last time the light turned out to be a train. I will instead say that I’ll do my best to get more blog and video content created and published as consistently as possible and that I appreciate your patience and your support.
 Today’s video was recorded on January 19th as “take two” that delayed its publishing. Basically I was filming the video at the end of the day when I had planned on publishing it, so I figured it would end up being a week late. Yeah.
 This may not be how everyone remembers those times, but this is my blog and my story and I’m sticking with it.
 Or Pearl Jam. Maybe Soundgarden. I never really did keep up with grunge, so I’m honestly not sure.
 I don’t want to get anyone’s hopes up, but between Friday evening when I wrote this post and finished editing the video, I have recorded the next two videos in the data culture series, and edited the first of them. Only time will tell if August is kinder to me than recent months have been, but barring unforeseen circumstances you’ll see the next two videos on the next two Monday.
My professional career was enabled by professional certifications. I would not be where I am today – however you want to parse that statement – without my Microsoft certifications.
Before you read too much into that bold opening statement, please allow me to elaborate by telling my personal certification story. This post was prompted by an online conversation, and responses on Twitter that made me remember that this is a nuanced topic, and that my experience is part of a bigger discussion. Understanding my story will help put my opinions in context.
tl;dr: certifications are a great way to build and validate knowledge and skills, and can help meet career development goals while complementing real world experience.
I started my IT career in 1996 when I got a job as a software applications trainer. I spent around six months teaching end users how to use Word, Excel, PowerPoint, Access, Windows 3.11, and Windows 95. Despite being terrified of public speaking, it turned out I was pretty good at it. After those six months I was offered the opportunity to become a Microsoft Certified Trainer and to teach MCSE courses at the training center where I worked. The risks were high, but the potential gain was enough to offset the risk. I said yes.
At this point you might be saying something along the lines of “hey wait, you didn’t have any real world experience!” You’re exactly right. And I wasn’t about to get any. I was passing exams so I could teach people with real world experience how to do their jobs that I had never done. I’m as aghast as you are. Please bear with me.
After I passed my first few exams I developed a study routine that looked something like this:
Read the courseware cover to cover
Make a list of topics that I didn’t understand well enough to explain
Investigate these topics in secondary sources like Microsoft TechNet CDs and technical books
Read the courseware again, crossing items off the list as appropriate
Repeat steps 3 and 4 as necessary
Take and pass the exam
Teach the class
Get awesome ratings from my students
Fast forward a few years and I was in a consulting role where training was a much smaller part of my responsibilities. Instead, I was working with clients to design and implement their network infrastructures, and later to design and implement custom software solutions using SQL Server, Visual Basic, and ASP.
This transition had a few bumps, but it was all about the tasks, not the knowledge. The skills and knowledge I had gained by preparing for certification exams and had reinforced through training enabled me to hit the ground running.
I no longer needed to pass exams to teach classes, but I did have another motivation: my company was a Microsoft Partner, and the benefits we received depended in part on the certifications held by our employees. I kept taking exams, but because I had different goals and a different starting point of skills and experience, I took a different approach.
My new exam prep routine looked something like this:
Read the “skills measured” list published by Microsoft on the exam web page
For any skill on the list where I don’t have meaningful knowledge and experience, study and practice the skill
Take and pass the exam
This new routine is shorter and simpler because I now had more experience and a richer personal knowledge base to rely on, and because I wasn’t preparing to teach a class – I just needed to pass the exam and use the product.
I adopted this second exam prep routine over 20 years ago, but I’ve never seen the need to change it, even as my role and motivations have evolved.
Since 2001, I have used certification exams mainly as a skill-building tool. When there is a new tool or technology I want to learn, I will use the exam skill list as my prep guide, and the exam as validation that I have learned it well enough. (More later on what “well enough” means here.) The list of skills measured by an exam is informed by a lot of industry research, and it covers a broader range of topics and features than you’re likely to encounter solely through hands-on project work.
One standout example of this process is BizTalk Server. Using this exam prep approach I was able to quickly build my BizTalk knowledge well enough to use it on a few projects. I wasn’t proficient on day one, but I went into my first BizTalk project with a clear understanding of what features it had, where to use each one, and how they worked together. This meant that as I was figuring out how to use a specific feature to solve a specific project requirement, I knew from the beginning what feature to use, and where to begin. Because I was certified.
But that’s just me. What about the other guy?
In the late 90s and early 00s the phrase “paper MCSE” got kicked around a lot. The idea was that some people would pass the exams and gain the certification without actually earning it, maybe by going to an illegal brain dump site or otherwise cheating. They’d have the letters but not the skills to back them up. What about those guys?
In my experience the paper MCSE was more often heard than encountered, but it was a real problem, as evidenced by articles like this one. I hired a few paper MCSEs, and I fired a few as well.
Based on my personal positive experience with certification, I updated my interviewing and hiring practices to go deeper in other areas – and to pay attention to how the candidate represented the importance of their certifications. If someone acted like their certifications made them God’s Gift To IT, that was a red flag – their certification had become a reason to not hire them. If someone acted like their certifications provided a foundation on which they could build with experience, they were more likely to get hired.
I’m hesitant to compare certifications to college degrees, because I have never completed a degree program – but I’m going to make the comparison anyway. I don’t know of any technical jobs where simply having letters after your name – be it MCP, MCSE, OCP, BA, or BS – makes you qualified. If you’ve earned those letters, they contribute to your qualification to one degree or another. The rest of who you are, what you know, and what you do, is what completes the qualification.
I haven’t passed a certification exam since 2012. On my birthday in 2012 – four years into my career at Microsoft, and having just helped ship SQL Server 2012 – I decided I would take the SQL Server 2012 exams on my birthday as a present to myself. All seven of them. And I passed them all.I knew my stuff, and it showed.
But I have failed an exam since then. A year or two later I was at a conference where a testing center was set up. A friend was taking an exam, and the folks at the testing center had an opening (also, as a Microsoft employee, I didn’t need to pay anything) so I said “why not?” I picked an exam on some technology I’d dabbled with, and went for it. I figured my general platform knowledge and experience and my having passed over 75 different Microsoft exams should compensate for my lack of knowledge on the specific topics being tested.
I failed, and not by a small margin. And this was what should have happened. I was not a qualified candidate for the skills being tested on this exam. I should have known better, but tried it anyway. The exam knew better.
Anyway, that’s enough about me. Let’s talk about you.
Rather than simply saying yes, please take a look at the skills measured by this exam and let me ask you if you would like to have those skills validated. Or, ask yourself if these are the skills that hiring managers would want a Power BI data analyst to have.
Then I’ll say yes. You should take the exam.
If it aligns with your goals well enough for you to be asking in the first place, yes you should take and pass the exam. Use the skills measured by the exam to guide your study and preparation, and then use the exam to validate that you know the topics well enough.
If you’re using Power BI every day, odds are you can spend a few weekends studying the things you don’t do very often, and you’ll be ready to pass the exam. If you’ve never used Power BI you’ll probably need to spend a few months studying and practicing before you can pass. But either way, the skills measured by the exam align well with what you need to succeed in the real world – and how could that be a bad thing?
Remember how I said earlier that we’d get back to what “well enough” means in the context of certification validating skills? Think about when you got your drivers license and the test you had to take. Think about the times you failed the test, or the friends who failed it.
Did passing the test and getting your license make you a Formula One driver? No, it validated that you were a minimally qualified driver, and that you were now allowed to legally operate a specific class of motor vehicle because you’d met a predefined bar. This is what certification tests to – they show that you are minimally qualified for the skills or role tested by the exam.
Is anyone interested in a blog post on the value of certification? This came up in a chat earlier today, but I'm trying to see if there's broader interest.
As I’m publishing this post, my question on Twitter has gotten 31 replies, and spawned some interesting discussion. I’m not going to try to reply to all of those topics here, but I’d love to have you join the discussion, and to hear what you think.
Also, when you take and pass the DA-100 Power BI exam, let me know what you think, and if you agree with me.
 If you’re interested, you can see my certifications by clicking on this link, and entering the transcript ID 678145 and transcript sharing code MatthewMCPCode1 when prompted.
 Part of it, anyway. The whole thing would likely take far too long to tell, and I would be as bored as you.
 My previous presentation experience was a college public speaking class where I needed to deliver 5 or 6 short speeches to the rest of the class. I spent 20-30 minutes before each speech being physically ill in the restroom down the hall from the classroom. But the trainer job’s starting pay was double what I had ben making at my retail job, and it was working with computers – combined, that was some serious motivation.
 If you think back to 1996, you’ll remember what a big deal the MCSE was. I remember seeing people charging over $1000 per day to deliver the official training courses. If that doesn’t sound like a lot of money to you, please consider that very recently I’d been making under $7.50 per hour. This was the opportunity of a lifetime.
 The deal was basically this: My employer would pay for the certification exams for each new course I was asked to teach, and I would pass them. They would also pay for me to attend each course – this was a Microsoft-mandated prerequisite to teaching at that time. For each course day, I would get a paid study/prep day. By the end of the prep days I needed to pass the exam(s) required to teach the course. If I did, I got a raise for each new course I taught. If I didn’t, I would need to pay my employer back my salary for the prep time, and the full retail cost for the course I attended. It’s also worth noting that at this point I had 3 peers who had collectively taken and failed 4 or 5 exams, and I knew zero people who had passed one. So yeah, high stakes, high pressure.
 The first exam I took was Implementing and Supporting Microsoft Windows 95. I was so nervous that when I sat down at the testing center computer to take the exam, I looked around trying to figure out where the loud pounding noise was coming from, because I was hoping for a quiet environment for this stressful activity. After a minute I realized that the loud pounding noise was my own racing heart. I am not exaggerating at all – I was terrified, but I prevailed.
 This was before “searching online” was a thing. Kids these days and their Googles don’t know how good they have it. <<shakes fist at cloud>>
 I don’t mean to brag, but despite my lack of real world experience, I was really good at what I did. Really, really good. I earned “instructor of the month” every month, and because this was disheartening to the applications instructors the training center split this recognition into “applications instructor of the month” and “networking instructor of the month” so someone else would have a chance. Every time I’m in Albuquerque, my ego tells me I should visit to see if the wall of fame is still there. I tell my ego to shut up, because it’s gotten me into enough trouble already.
 I need to shout out here to my amazing mentor Jeff Lunsford, without whom I may never have taken this leap. Of all the people in my life to whom I owe an eternal debt of gratitude, you are the one named Jeff. Thank you for believing in me, for opening doors for me, for pushing me when I needed pushing, and for supporting me when I needed support.
 Did you know I was Oracle certified too? Around 20 years ago I got my Oracle Certified Professional Developer certification as a way to learn the basics of Oracle database. I fell in love with P/L SQL, but I hated Oracle’s tools so much I never did anything with the certification or the knowledge.
 Pun not intended, I swear.
 If you look at my transcript, only five of these seven show up on my birthday, but there are other weird date discrepancies throughout the transcript too. I’ve learned to let it go. Also, if you try to schedule 7 exams on the same day please be prepared for the exam people to tell you “no” more than once.
 YouTube isn’t letting me see the chat for some reason, so I can’t say who it was.
 I’m still laughing at the “certification rocks” caption on that photo. I get all of my random pictures from https://pixabay.com/, just so there’s something to show in the preview when the link is shared. This one works on so many levels… if like me you have the sense of humor of a 12 year old…
In the five years since then Power BI has evolved to be much more than a self-service tool. Power BI today includes capabilities for self-service and enterprise BI, adding data preparation, information management, machine learning and more, while still including powerful tools for data visualization and reporting.
Today the Power BI team at Microsoft is commemorating this milestone by saying thank you.
Even though I’m on the Power BI team at Microsoft, this is my personal blog, and I wanted to say thank you as well. So I put together a short video that includes five of my favorite things:
My awesome new “5’ve got the Power BI” t-shirt
The one thing I couldn’t include is all of you.
Thanks for the comments and feedback on the blog and the videos, and thanks even more for helping make Power BI what it is today.
 Yes, I know today is July 23rd here in North America, but by commemorating this milestone today we can be more inclusive for folks in parts of the world where it’s already the weekend.
 There is one joke in the video that I suspect I am the only person who will understand enough to laugh at it. If you get it please tell me so I don’t feel so alone.
Back in 2013 I presented for the very first time at the MVP Global Summit. I was terrified.
At this point I had been working for Microsoft for five years. I’d been an MVP before joining Microsoft as an employee and I had been a regular public speaker for many years – but this presentation felt different. MVPs are some of the most technical, most vocal, and most skilled people I know, and as a general rule they are talented presenters and communicators. I was very nervous, and I wanted to break the ice, both to help myself relax and to set an open and conversational tone for the session.
So I started my presentation like this:
A warning before we proceed: This post is likely to have more coarse language than I usually use. If you’re offended by swearing you may want to consider skipping this one, but I hope you’ll read on. I’m writing the warning before I write any swears, so maybe I’ll find another approach by the time I get there.
As the initial icebreaker I used the cover of Madonna’s 1984 album “Like a Virgin.” This was my first MVP Summit presentation, and I wanted to remind everyone that even though I might be a familiar face, this was my first time… or close enough.
The next slide represented the transition from the intro into the technical content of the session. I wanted to emphasize how we were talking about internal Microsoft processes and planning, and that the session would share these details that are typically not shared with anyone not on the product team… so I used the title “opening the kimono” and as a family-safe but slightly risqué visual joke I used a “no image found” placeholder for the slide body.
The session went better than I could have hoped. People laughed where I wanted them to laugh, and shared deep technical feedback and constructive criticism were I wanted them to share deep technical feedback and constructive criticism. I was very happy.
Until later in the day, when someone came up to me to let me know that there were complaints. That someone had been offended, that someone hadn’t liked my choice of slides and sexually charged metaphors and images.
I was shocked.
The MVP who was reporting the news let me know that he wasn’t at all offended, and that the complaint came from someone who was always complaining anyway. “You know who,” he said to me, knowingly. I didn’t know who, and I never tried to find out who.
Instead, I thought about how my actions had a negative impact on someone in a way I didn’t plan, intend, or desire, but which was real nonetheless. That process was painful, and even now – almost seven years later – it is still uncomfortable to write about. I’m worried that someone might comment “you should have known better, you were an adult and 2013 wasn’t 1984” because criticism is difficult, and maybe they were right. Maybe I should have known better, but I didn’t, and because I didn’t I caused problems for someone who didn’t need me causing problems for her.
And I didn’t do it again. I messed up, I learned about my error, and I did my best to correct it. I made a choice, and I chose to change and grow.
I want to unpack that last sentence before I proceed. Please bear with me, because this is important.
Jen is one of my heroes. She’s deeply technical – she’s one of those people who knows more things about more things than anyone could ever be expected to know. She works with some of the biggest companies in the world, helping them deliver global scale data solutions using a huge range of technologies. She can hold her own standing with the best of the best because she’s that good. She’s also an effective communicator who freely and selflessly shares what she knows. She’s soft-spoken, but when she’s speaking it’s worth leaning in and listening to what she has to say.
But that’s not the only reason she’s a hero. Jen is compassionate and tireless, and speaks the hard truths where so many others would chose the easy path and remain silent. Jen consistently chooses the right thing even though she knows it is also the hard thing.
And Jen chose to give up her status as an MVP. If you know anything about the program, you’ll know that this is a big deal. The MVP program comes with significant benefits and access to information that is invaluable to someone in her profession. I remember being an MVP and I know that I could not have done what she did. I am in awe.
The final thing I want to unpack is community – I called Jen a data platform community hero. The context of a community carries some baggage that may not be obvious, and I want to look at it a little bit here. Every community is defined by the behaviors it tolerates. If a community tolerates abusive behavior, that abusive behavior will thrive and will become the norm. People who can’t tolerate the abusive behavior will leave, and they won’t return. People who are made uncomfortable by the abusive behavior will engage less, and contribute less. And people who appreciate the abusive behavior will recognize that it is allowed, and they will repeat and amplify it. This is how communities work – not just technical communities.
Weeding out the “bad apples” isn’t enough. Successful communities need to define what they want to be, and to do it deliberately. I don’t know of any technical communities that do this, but I know one in another part of my life: Valkyrie Western Martial Arts Assembly. Valkyrie is a martial arts school in Vancouver, BC that saw the abusive behavior in the North American HEMA community and decided to be something different. In this awesome blog post, Valkyrie co-owner Kaja Sadowski describes how she did what she did, and why. Please read the whole post (it’s much shorter than this one) but for now, please read this bit:
If a female student is worried about whether the guy she’s partnered with is going to hit on her again, or “accidentally” grab her breast, or refuse to hit her in drills, she can’t focus on her training. If an Asian student feels like he has to prove he’s not a bookish stereotype, or has to put up with constant shitty Kung Fu jokes, he can’t focus on his training. If a queer student is anxious about how their fellow students will react when their partner comes to pick them up after class, or a transgender student is stuck worrying they’ll be called out for using the “wrong” bathroom or misgendered by their training partner, they can’t focus on their training. All of these students end up in a position where their learning suffers, because they can’t put 100% of their energy and effort into their training and instead have to deal with the background noise of harassment and discrimination. If I put them in that position, I have failed them as an instructor.
Kaja decided that inclusion and diversity was important, so she decided to be aggressivelyinclusive.
And the results are fantastic. I’m a middle-aged straight white guy. I’m tall and reasonably fit and reasonably good and I expect I would be welcome in any club. I have never felt more welcome than I am at Valkyrie. I visit Valkyrie for events, and am delighted when they come down to my club in Seattle for our events. They built a community that is aggressively inclusive for the people who might feel unwelcome in a traditional martial arts school, and along the way they built a community that includes everyone.
That’s how inclusion works.
I’m not done. There’s one more thing I need to call out here – both because it’s important and because it is central to Jen’s decision – and that is the #MeToo movement.
Some of you might be saying “MeToo is so 2017, is that still a thing?” I hope not, because that is likely to get the swearing started, and I’ve done so well so far.
MeToo has the name that it does because women have put up with sexual harassment and sexual abuse that men don’t experience, and often men don’t see. Women are literally saying “me too – I have also suffered the thing that you are talking about” because for far too long they’ve been told everything but this. Most of the women you work with have been harassed and abused at every step of their careers, and they’ve tolerated it as best they could.
This is important, and this goes back to my personal story from 2013.
My choice of slides may not have been a big deal. On their own, they may not have been worth mentioning, even for someone who would rather I chose different images and phrases. But they weren’t on their own – they were part of a lifetime of challenges and indignities large and small. Despite my lack of malice, they were another straw on the back of someone who didn’t need one more person telling her she wasn’t welcome, and I was that person. Casual sexism doesn’t need to be intentional to be hurtful.
In 2013 the only person who mentioned that complaint was the guy telling me it was no big deal.
In 2016 a small martial arts school decided to build a welcoming and inclusive place for everyone, and their results speak for themselves.
In 2020 one of my heroes left the MVP program.
I suspect that in 2020 there are young women and brilliant people from other underrepresented groups who are questioning if the MVP program has a place for them.
Communities are defined by the behaviors they tolerate.
One community in this story chose to make diversion and inclusivity a true priority, and one apparently did not. One invested time and money and careful thought in “actively and aggressively building… a safe space for all,” and the other one apparently did not.
I hope the MVP program will recognize this moment for the wake-up call it is. I hope the MVP program will choose to change and grow.
But it needs to be a choice.
Hey, look – no swearing after all. Good on me.
 The MVP Summit is an NDA event, which is one of the things that makes is to much fun.
 If this phrase feels immediately inappropriate as you read this today, I would like to share this MacMillan Dictionary post from the general timeframe of this event that calls this phrase “a vividly effective metaphor for conveying transparency and frankness” which nicely sums up my intent.
 The same brain that made it hard for me to know who might have been offended by my presentation also makes it hard for me to remember the male MVP who let me know about the complaint. Faces and names have always been hard for me.
 Not to say that they don’t exist – there’s a universe of stuff out there I don’t know about. If you know of a vibrant and inclusive technical community, please tell me.
 HEMA stands for Historical European Martial Arts, which is a blanket term for the modern swordplay practice I love so much. The HEMA community has enough “bad apples” to drive away many people who would love it as much as I do, including sexism, racism, and more. That community is starting to change and grow, and Valkyrie is part of the reason. There are some other delightful folks in this community who take no bullshit, and I love them too. If you want to imagine what the abusive behavior looks like, watch Karate Kid, but give the Cobra Kai dudes swords.
 This was a rough post to write – it’s difficult to talk about hurtful mistakes even if they’re years in the past. I like to think I am a better person than I was a year or a decade ago, but I still make mistakes. I still act thoughtlessly and carelessly, and I still fail to live up to the moderately lazy standards I set for myself. But I’m trying, and the people in my life are forgiving.