C Chandoo.o org Podcasst Transcrript
Transccript for SSession 0 040 Listen to o the podcastt session, seee resources & & links: http://ch handoo.org/ssession40/ Transcrip pt: Hey everrybody, welccome to http://chandoo.o org podcast. Our podcastt is aimed to make you awesome in data analysis, chartting, dashbo oards and V VBA using EExcel. This is episode number 40. You can p://chandoo.org/session4 40 where all the show no otes, links an nd resourcess in this episo ode can be visit http accessed d. Today I h have a very eexciting guest, a good frieend and a fellow Excel blo ogger, Migueel Escobar. M Miguel is an expert in Power Qu uery. You kn now I have been b promising you a po odcast on Po ower Query. Initially, I n I kept thinking that I d don't know m much Power Query and I can't talk wanted to do it mysself but then n and Miguell. They have been runnin ng a Power Q Query trainingg program, about it.. So, I reacheed out to Ken website and have written w a boo ok together.. I reached o out to them m and Ken su uggested thaat I should w Miguel forr this podcasst. So, I am really r honored, pleased and happy tto invite Migguel to our interview podcast.. Chandoo o: Hey Migueel, how are you doing? Miguel: Hey, thank yyou man. Chandoo o: Thank you so much forr joining me o on this podcaast. I am sorrry we started d the call a bit late but I am reallly excited ab bout all the topics that we are going to cover today. Can you brieflyy introduce yourself and tell us w what you are doing these days? Miguel: Yeah, for surre. I am an Exxcel guy; I plaay with Excel a lot. I main nly use Poweer BI and Pow wer BI tools like Pow werView, Pow werQuery an nd PowerMap. I do consultancies, BI projects or training aro ound those tools. So o, that's basiccally what I d do in a nutsheell.
1 | P a g e
© Chandoo.org
C Chandoo.o org Podcasst Transcrript
Chandoo o: Awesome.. Can I ask yo ou that beforre doing Pow wer BI and Po owerQuery kkind of stuff, what were you working as? Miguel: I was mainlyy working as a Sales Analyyst using Exccel a lot and d doing analysis for a comp pany which was like a regional ccorporation ffor Twentieth Century Fo ox here in Paanama. So, b basically, I w was the guy doing the Box office reports on aa daily basis and telling m my superiors how the acttual movies w were doing at the Bo ox Office. o: Okay, greaat. So, is that where you ggot interesteed in Excel an nd Power BI? Chandoo Miguel: TThat's wheree I actually go ot to know w what PowerV View was and that's how it actually all began. Chandoo o: Did you q quit your job to work o on Power BI full‐time n now or are you y still holding a job somewh here? hen I quit thee job and became my ow Miguel: I was with th hat companyy for almost 2 2 years and th wn boss! Chandoo o: Awesome.. When was tthat? Miguel: TThat was aro ound April 20 013. Chandoo o: What did yyou feel wheen you quit yo our job? Whaat were you thinking at that time? Miguel: Actually, thaat was scary. It was a scary place to b be. I don't haave any family members to support and so I didn't have any financiall restraints o or constraintss. So, I was o okay on that part. I kept ssaying that if I don'tt do it now then I won'tt do it later.. So, I decideed to do it tthen. Hopefu ully, everything will be alright; just like the ssong. So far itt is going greeat. Chandoo o: Awesome. I am very happy to heear that. I th hink I was in n a similar siituation in 2 2010. Well, fortunately or unforttunately for m me, I had a vvery young faamily at that time ‐ my tw wins were bo orn about 6 months before I quitt my job ‐ so o, I was actuaally far moree stressed an nd worried in n the initial m months but everything turned out okay. Wh henever I hear about a ffellow Excel author or blogger becom ming a full
2 | P a g e
© Chandoo.org
C Chandoo.o org Podcasst Transcrript
time enttrepreneur, I feel so hap ppy because it is like a little commu unity and wee have a new w member there wh ho works for themselves and is his ow wn boss and iis setting outt to do aweso ome things. So, great! I want to talk more about what yyou did in the past two yyears but firsst let's talk aa little bit ab bout Power Query an nd what thiss technology is all about, how it can help an Analyst, and how w it can makke their life better. SSo, in your wo ords, how do o you explain n Power Query? hat you can find in Excel n nowadays. Itt is the tool Miguel: Power Queryy in a nutsheell is the bestt data tool th orm and enrich your dataa. Wheneverr you need to o do any of that can actually hellp you integrrate, transfo ower Query is your go to tool. those, Po Chandoo o: It's a very clear and po owerful defin nition. I like tthe words integrate, tran nsform and eenrich your data. Lett's talk a littlle bit more aabout those three aspeccts. What do we mean w when you sayy integrate, transform m and enrich h data? How do you relatte this to reall world situattions or prob blems? Miguel: That's easy. When we saay that we trransform thee data, just to o give you an n example m most people deal with data that iis really messsy, that is not in the best way to bee used in pivvot tables or in Excel in general and so they have to do a lot of cleaning. That's what I mean n by transform. So, theyy transform some meessy way and d transform it or clean it iinto somethiing that is farr more usefu ul for Excel an nalysis. We clean the data that we get from anywheree ‐ Excel filees, text files or anywherre. The seco ond part is ou might, forr example, haave data for last year in integratiion. A common task that people actuaally have ‐ yo a differeent Excel file ‐ or you can actually havve it in a diffeerent Excel spreadsheet o or worksheett ‐ and you want to integrate orr combine th hose two wh hich is an append operattion. Whenevver you wan nt to do an or any type of merge o operation, yo ou can do itt with Poweer Query. So o, you can append operation o which is basiccally like an integratee those two sources, tables or data seets to come up with the end result w append o operation off two years o or two month hs or all the m months that you have in your workbo ook. That's as far as integration goes. So, wee clean up ou ur data and then we integgrate our datta and last, b but not the least, wee can actuallly enrich thee data. By en nriching we m mean that w we can createe new colum mns and we can summarize the d data that we have. So, wee can add ussing functions that actually are in Pow wer Query. We can add pretty m much anythin ng that we w want ‐ any tyype of colum mns or any tyype of calculaations that you can possibly thin nk of or you can just sum mmarize dataa that can bee later analyzed or consu umed in an Excel tab ble or an acttual data mo odel with Pow wer Pivot. Th hat's the beaauty of it. Yo ou can clean it, you can integratee it and you ccan make it eeven better b by enriching it with Poweer Query. Chandoo o: Awesome.. I think these are the thrree main pro oblems that p people will faace when they want to analyze data and bu uild a report or create a model or an nything like tthat. The firsst stage happens to be having th he right dataa. Now, even n though it lo ooks like one step in the process, peo ople go throu ugh a lot of whoops just to get tthe right data. They need d to first obttain this dataa from wherrever the sou urce is and they neeed to bring itt from there to Excel. Theen, they havee to scrub it and clean th he data. Sometimes the data is missing m or it is not correect. Then theey have to enrich the data and only then is it in a position
3 | P a g e
© Chandoo.org
C Chandoo.o org Podcasst Transcrript
where they can actu ually start analyzing the data. From my experieence, when I I was workin ng or even today, if f I am just using plain Exccel, I find thaat a good 30‐‐40% of the ttime goes into this stagee where we have to get the dataa, clean it an nd make it ready r for thee analysis. And, then, evverything else falls into place very quickly. Ju ust to give yo ou an examp ple, I run a forum on http://chandoo.o org. I don't kknow if you are familiar with it. It is quite a popular fforum with lots of mem mbers and H Hui, one of our forum administtrators and w who is also a MVP, askeed me a while ago that he wants to o know some statistics about th he forum beccause he wan nted to show wcase those sstatistics to somebody. He wanted to know how many po osts he is making every m month, how m many likes hee has receiveed, how many posts he has made in a month h that are a rreply to someebody's quesstion and thiings like thatt. Now, it is aa very simplee question. Essentially, he wantss me to analyyze the forum m data and give him thesee answers. So, what do I do? I go to m database and downloaad it. It is acttually a 300 MB sized daatabase; it is quite big. So o, I grab all my forum the dataa. The forum m runs on a M MySQL serveer. So, I tookk a dump of the data and d loaded it tto my local computeer MySQL and looked at the tables. TThe data wass all scattered all over the place and everything was everywhere. And, I couldn't answer Hui's questions vvery quickly.. I knew thatt all the dataa was there hese steps ‐ but gettiing his answeers was goingg to take mee forever. Forrtunately, I know one wayy to do all th integratee, transform and enrich data ‐ so I u used SQL to do this and I got him an nswers. Now w, to me, it sounds like what Pow wer Query do oes is that it is like a repllacement to SQL but morre suitable and friendly hat? for Excell users. Would you say th Miguel: Yeah. Peoplee say it is bassically a userr‐friendly way for you to create queries, i.e. like aan assistant to createe queries. That is basicallyy what it is. Chandoo o: I think thaat's also why they probably named it Power Qu uery because a big partt of Power Query iss querying w which is notthing but wh hat SQL doees but insteaad of trying to write complex SQL queries aand memorizing the syntax and the functions an nd clauses an nd everythin ng, you are n now simply clicking o on ribbon op ptions just th he way you w would in Excel and get yo our data cleaaned, transfo ormed and enriched d for you. So,, really, I thin nk this is an eexciting technology for an nybody dealiing with dataa and using it to do something vvaluable. Thee first time tthat I heard about Poweer Query, I w was like "Oh my God; I must have it. I mustt use it". I neever felt that kind of enthusiasm even for Poweer Pivot. But,, I felt that Query is a mu ust‐have tooll for me in m my computer because I deeal with lots of data and I could use Power Q some heelp when it ccomes to scrubbing the d data and maaking it readyy for the nexxt stages. So, it is good that Miccrosoft has laaunched such h a technologgy. Now, let'ss talk a little bit about ho ow an ordinary user can on their com mputer. Whatt are the step ps involved to o obtain Pow wer Query? get this o Miguel: The first steep is to have either Excel 2010 or Exccel 2013. Then, all you h have to do is download the tool. I am sure tthat we are ggoing to havve a downloaad link somewhere in thiis podcast an nd you can wnload it ‐ 32 2 bit or 64 bitt ‐ and simplyy install it. In n the event that you don''t have Excel ‐ for some just dow reason yyou're using EExcel 2007 or Excel 2003 ‐ all you havve to do is do ownload the Power BI Deesktop and you are ggoing to be aable to use Power Query completely ffor free with this tool.
4 | P a g e
© Chandoo.org
C Chandoo.o org Podcasst Transcrript
Chandoo o: So, I understand how iit works on EExcel. You do ownload it an nd install it aand it becom mes an add‐ in and sh hows up as aa separate ribbon in Exceel. And, when you do som mething with h Power Queery, we will talk morre about thee process later on but evventually thee data ends up in Excel or as part of o the data model. I am curious tto know whaat this Powerr BI Desktop or Power BI Designer thaat you are tallking about does that relate to the Exxcel work thaat people do?? is. How d Miguel: Well, Powerr BI Designeer is an actuaal free tool that t Microso oft created to t be the too ol for their Power BI which is su upposed to go for generaal availability on 24th Julyy. So, this too ol is not a reeplacement for Excel. It is just simply anotheer tool to givve you just aa quick way tto create yo our reports w with all the Power BI B tools thatt you alread dy know abo out. So, Pow wer BI Deskttop is simplyy Power Pivvot, Power Query an nd Power Vieew all integrrated into on ne. That is acctually the Po ower BI Deskktop. Chandoo o: What doess it produce?? I have down nloaded Pow wer BI Designer on my lap ptop but I havven't had a chance tto play with iit yet. Once yyou set up yo our entire thing in Powerr BI Designerr, what does the output look like? Miguel: Well, for the output of those charts, you are ggoing to first have your d data model. Your data model iss going to be in that Power BI Designeer file. Once you actuallyy have your d data model in that, you can use aa new Power View which h is completeely HTML‐5 b based and so it is going to o render on aany device, i.e. it is H HTML‐5 compatible. And,, you are going to have a lot more chaarts than thee ones that you actually see in Exxcel nowadayys like gorges, field mapss etc. There aare a lot of n new charts and graphs th hat you can ower BI Desiigner or Pow actually use in this Po wer BI Deskto op. Once you create this m massive, amaazing piece hich is your ccreation, you u want to pu ublish it and share it with h someone eelse. All you have to do of art wh now is simply click o on a single b button and th hen it is goin ng to publish h that same report/modeel that you our Power BI Designer/D Desktop to your Power B BI account w where you caan actually just creaated with yo share it w with anyone. And, that report that yo ou created in n there is going to be avaailable or is ggoing to be consumeed on any sm mart phone d device. They actually havve native app ps for the An ndroid and iP Phone. You have an iPhone, rightt Chandoo? Chandoo o: Yeah. Miguel: You can acttually download the Pow wer BI app right now an nd you will be able to ssee all the samples that they h have available for any ussers on how w the end result of the P Power BI Deesigner will actually look like on yyour iPhone..
5 | P a g e
© Chandoo.org
C Chandoo.o org Podcasst Transcrript
Chandoo o: Awesome.. I will save tthe Power BII Designer to opic for another podcast.. I want to taalk about it more bu ut I think thatt it is someth hing that we can talk abo out a little latter. Now let's switch back to Power Query. SSo, you go an nd download d Power Queery and install it on your computer w with Excel 201 10 or Excel 2013 and d then, whatt's next? How w do you gett started with h it? What arre the first stteps that you u would do to get into Power Qu uery? Miguel: Once you in nstall this, yo ou need to take into co onsideration that Power Query or th he concept behind tthis tool is th hat it is an ETTL tool. ETL is an acronyym that mostt technical o or IT users acctually use. That acrronym stands for Extract Transform m Load. So, itt extracts, transforms and loads thee data. So, when yo ou go to Excel, you will no otice the new w Power Queery ribbon. Once you clickk on that, you u are going to be ab ble to see a whole sectio on dedicated d to get dataa. You can gget data from m Excel files,, text files, databasees and even from SAS and other applications ‐ theere are so many that to b be able to keeep up, Ken and I will be writing another boo ok just abou ut connectingg to data because you caan basically connect to magine! Oncee you connecct to it and on nce you inpu ut the server address or any dataa that you can possibly im file locattion then yo ou will get aa new windo ow which is the Power Q Query Editor window. W Within that window,, you are going to scrub b, transform or integratee your data. That's basicaally your foccus in your Power Q Query window w. Chandoo o: Okay, greaat. So, in a waay, with Pow wer Query once you load tthe data then the way to o work with data or ttransform orr enrich or cllean this datta will happeen in a separrate window w. This is simiilar to how you wou uld do VBA coding or deal with Pow wer Pivot. The T initial sttep is in Exccel but everyything else happenss in a separatte window w where you do o all the stufff and once yyou've done it, you ask itt to bring it back to Excel and then it comes into Excel. SSo, it is a con ncept similarr to what maany people aare familiar with. Eveen if you havve never used Power Queery, if you haave done a b bit of VBA pro ogramming o or if you've done som mething with h Power Pivo ot then you ccan relate to the idea of P Power Queryy because it is similar. It takes your data, takees it to the Power Query window wheere we do all the steps aand then we come back and put it in Excel. Now, becau use this is aan audio pod dcast, some of these steps can be a little bit confusin ng, so I will provide p somee links and rresources in the show no otes where people p can learn more about th hese initial ssteps. Now let's talk a little bit mo ore about w what happens in the Pow wer Query window,, i.e. what do o we see oncce we get theere, what aree the key things that you u would noticce and why they are there and how they can help? Miguel: The Power Q Query team d did an amazin ng job with tthe UI. Everytthing that yo ou can possib bly imagine is one click away. So o, let's say th hat you actuaally load the data from an Excel or teext file. You n notice that the first row is the on ne that actuaally has the h headers and we need to promote tho ose headers o or that row as the headers. With h just 2 clickks you can prromote thosse to be the headers of aa table. If yo ou want to remove some colum mns, all you h have to do iss right click and choose Remove Columns and th hey will be removed d. The conceept behind th his is that evverything should be onee click away from you. That can be adding new n column ns, removingg columns eetc. Transforrmations sho ould be maxximum two cclicks away. Three cliicks may be aa bit too mucch. They are probably all kind of two cclicks away iff I rememberr right.
6 | P a g e
© Chandoo.org
C Chandoo.o org Podcasst Transcrript
Chandoo o: Yeah, okayy. I have playyed with Pow wer Query qu uite a bit and I totally agreee with you. I think you will imm mediately feell the power p part of it oncce you see th he Power Query window.. It’s like "Oh h God, I can do this!"" If you werre trying to d do such cleaan‐up or transformation steps either manually or o through some formulas or VB BA, some of tthem would take a lot off code and a lot of extra steps and a llot of work on your Pow wer Query wiindow and your data is and thinking whereaas it is just one button orr two clicks o o give you a very simple example, maany times yo ou get data that looks likee a pivot tab ble and you clean. To want to un‐pivot or de‐pivot thiss data so thaat you could do some further analysiis. I have written some articles aabout this ass well and I fiind it amazin ng that you can just click a button in P Power Queryy and it un‐ pivots th he data for you. y So, you u can then m move on to doing d other things with your data rather than thinking about the fo ormulas or VB BA to do the un‐pivoting. How amazin ng is that? Miguel: TThat is truly amazing. Chandoo o: Can you tell us somee of the com mmon transformations tthat can happen like m maybe your ower Query. favorite transformatiions that you u can do in Po Miguel: Well, I would d say that myy favorite traansformation n would be to o 'Group By'. I use it a lott. Basically, it finds the rows thatt have match hing values aand it simply groups them m basically likke how you aactually do a pivot table. You tryy to group things on a column or row basis. You caan do pretty much the same stuff in the Power Query win ndow. Chandoo o: Yeah, I think the Grou up By is esseentially the sstep for going from raw data to repo orts level. I rememb ber that backk when I waas learning SSQL in 2003,, I finished m my studies aand started a job as a Softwaree Developer and I was bu uilding reporrts for Reebo ok, the shoe company. W We were building some systems for them and my part w was to develo op the sales fforce reportss and every d day I would w write these of them just had Group By. Until thaat time, even though I dozens aand dozens of SQL queries and all o learnt SQ QL in collegee, I would n never use Grroup By. I w would think w why I would need to usse it. But, I realised that this is all people neeed in the bussiness world. Nobody wants to look at all the milliion rows of higher level aand then justt look at the summarized view. Group p By is such data; theey want to grroup it at a h a powerfful feature. O Obviously, in n Excel, a sim milar feature w would be pivvot tables wh here you takke data and you pivo ot it which iss nothing butt grouping and it gives you the results. But, sometimes, we w want to go two leveels, i.e. we take the data and create aa pivot and th hen we wantt to analyze tthe data in the pivot to do someething. This is where I ffind the man nual processs or even th he process o of using pivo ot tables is considerrably time‐co onsuming wh hereas with P Power Queryy, you could load the raw w data to Po ower Query and do tthe Group Byy there and tthen load that data to Exxcel directly so that you can focus more on the analysis and it does not take a lot of time. It is just am mazing that tthey have su uch a featuree in Power Query.
7 | P a g e
© Chandoo.org
C Chandoo.o org Podcasst Transcrript
Miguel: One of the kkey features aabout how P Power Query essentially w works or the workflow off it is that it u are workin ng with VBA. I don't know w pretty mucch anything aabout VBA. I only know actually feels like you a existts and when n I create an nything relatted to VBA, I use the M Macro Recorrder which that it actually basicallyy records eveery step that I do. With P Power Query,, it essentiallly records a macro. Everyy time that you do aany type of creation, it is a step. It wo orks on a step p‐by‐step basis that you can even seee and audit yourself on the writing (called Sttep Dialog) aand you can even go forw ward in time and see you ur last step o and do aany modificaation at a specific step. SSo, it has a llot of similarrity to VBA or go prrior to that one especiallly with that R Record Macrro situation aand it gives yyou the flexib bility where you can just go back in time and d add any neew type of rrequirementss that you actually have. Another thing about Power Query and VBA A is that yourr results ‐ mo ost of the tim me that I actually dealt w with VBA, thee code actuaally does all of it for you ‐ so wheen you finish h running thee VBA code yyou are goingg to get a cleean report. YYou can do hoose Powerr Query overr VBA is that the actual the samee with Poweer Query. Thee reason why I would ch engine h has some opttimization so o it can actuaally run fasteer or it can acctually push the work at the source and actu ually work loccally in your ccomputer. o: Yeah, I think where I fiind Power Q Query to be a winner is th hat anytime tthat you deal with data Chandoo related p problems ‐ data manipu d lation, data transformatiion, data chaanges, and d data removaals or clean up ‐ all o of those are incredibly faaster and verry easy to thiink and do in n Power Queery. Although h VBA does have an edge, for exaample if you want to auttomate the ccreation of a bunch of chaarts then natturally VBA But, your raw w data proceessing is wheere Power Query Q will givve you that would be the right ffit for you. B wer and easse of use. No ow, let's talk about the Steps Recorder feature e of Power extra processing pow her area of Po ower Query that every time I see, I feeel amazed Query a little bit because I think tthat is anoth Query (you ccan correct m me or maybee you can add d on) let's saay that you at what it is. Becausee, in Power Q database and d removing are doing a couple of steps like yyou are bringging some daata from yourr corporate d ou are running some steeps like a Grroup By or a bunch of columnss and for thee remaining columns yo ower Query has done something and d that step iss recorded. whateveer, at every sstep, you will see that Po It's not rrecorded justt so we can see what it is doing. It iss basically a script for prrocessing data, isn't it? So, anytiime your datta changes aand you run aa refresh, Po ower Query w will go throu ugh the stepss again and produce a new set o of data. When the data changes, you don't have tto re‐do the steps. You just refresh of those 75 steps will hap ppen again and you will gget clean datta for your reeports or analysis, isn't and all o it? Miguel: Yeah, that's the amazingg part about Power Queryy. Chandoo o: It is re‐usaable. In a waay it is betterr than VBA in n that sense because when you record a macro with VBA A, many timees even if som mething sligh htly changes in your workkbook settinggs or data, fo or example you reco ord the macro o from cell A A1 and you w want to do the same thingg but in cell B B7 then the m macro may not workk correctly. W Whereas, witth Power Qu uery, when tthe data changes, every one of thosee steps will happen to the changed data an nd it will work. Probablyy the only exxception wou uld be when n the table
8 | P a g e
© Chandoo.org
C Chandoo.o org Podcasst Transcrript
structuree changes but as long aas that remaains the sam me, you don'tt have to do o any extra steps. Just refresh aand you get yyour new datta. Miguel: Yeah, that'ss an amazingg thing about Power Queery. Let's sayy that you ccreate the sttep‐by‐step basis an nd suddenly something that should happen isn't happeningg because itt is a completely new requirem ment, you caan go into th hat step and then add th hat new logiic or enrich the data however you want. On ne of the coo ol features about Power Query is thaat once you aactually know w how to mo ove around your UI, your experieence is greattly improved. The actual interface is ccompletely u user‐friendly. You don't need to know any co ode to get w working with h Power Queery. You can learn more, of course, w with the M d with the Po ower Query p programmingg language b but the idea is that you caan do a lot with just the code and UI. So, th hey got it rigght with the user experieence where yyou simply ju ust jump righ ht into it and d get things done. Yo ou don't actu ually need to learn any co ode to get thiings done. Yo ou just simply jump right into it and do it. It is completelyy intuitive. Chandoo o: Yeah, it is. We will talkk about the M M code part a little later. I totally agreee with you. I think the interfacee is fairly intu uitive especiaally for someebody who d deals with daata and who knows what they want to do neext, i.e. the kkind of transfformation th hey are looking for; they are going to o immediately find it in the Pow wer Query rib bbons because everythin ng is spelt ou ut and everything is out there, neatlyy arranged and it is easy to get ggoing. As I said, we will taalk about thee M languagee part a little later. So, theese are the mations, i.e. how to change data. Ap part from traansforming d data, what ellse can we d do with the transform Power Q Query window w? What are the other th hings that you u commonly do? Miguel: One scenariio that we h handle with Power Queryy is the com mmon VLOOK KUP matchin ng. We can only use VLOOKUP o or the INDEX‐‐MATCH com mbination to get data fro om another table. Now, w with Power Query, you can do y ssomething caall a 'join' wh hich is basicaally creating a relationsh hip between these two tables ‐ our referen nce table and the table that has du uplicates ‐ and a simply d do a VLOOKUP. It is a UP completely within the user interfaace of Powerr Query. Simply select the tables thatt you want VLOOKU to comb bine. You telll them which h column fro om each tab ble to use to actually uniite or createe the union between n these two tables. Oncee you actuallly click on OK, it will actually create this merge ffrom these two tablles. With jusst a few clickks (3 clicks I believe) you u actually geet this completely nativee VLOOKUP that is w way faster thaan the VLOOK KUP that you u can actuallyy use in Excel. Chandoo o: Yeah. Is this the same aas the ‘merge’ feature? Miguel: TThis is ‘comb bine’.
9 | P a g e
© Chandoo.org
C Chandoo.o org Podcasst Transcrript
Chandoo o: Then, let's talk abou ut this a litttle more beecause I am m curious to know, for example I understaand what this is doing bu ut can you givve me a scen nario where w we may havee to use such h a feature. When would you usee it? Can you give us an example from m the recent p past maybe?? Miguel: Yeah. It is n normally useed by Analyssts. Analysts usually creaate pivot tab bles and theey need to actually get the dataa into one table. So, to gget most of the data thatt we can posssibly find intto just one table, they can creatte a pivot tab ble because tthey can only use one table at a timee. So, let's im magine that this Analyst is dealing with sales data. It is baasically just aa table with tthree fields. It has a datee, the ID of the prod duct and thee total amount sold of that specificc product on that specific date. Thiis also has another table with a unique list o of all the pro oducts that th hey actually ssell. So, he w wants to com mbine these two tables ‐ the prod ducts table w with the one ffor the sales.. He will actu ually do this o on the sales ttable using a VLOOK KUP to get more m of the data from the t productss table. What you will do o with Poweer Query is simply im mport those two tables iinto the Pow wer Query wiindow. Then, you will go o to Power Q Query again and in th he Power Qu uery ribbon there is a group called combine and d inside thatt group you will notice merge. You Y will click on merge and it will simply mergge those two o tables. Thee result of that t merge operatio on is simply ggoing to be the sales tablle plus all of the VLOOKU UP operations that you caan possibly imagine already donee by the com mbination of tthese two tables. So, it iss going to be that sales taable plus all orrespondingg fields from the productss table. Whatt do you thin nk? of the co Chandoo o: I think thiis is something that is really powerfful and very useful. I wo ould use thiss kind of a feature aall the time. In fact, man ny times becaause they do on't have Pow wer Query o or they don't know that such a thing exists, p people writee these VLOO OKUPs on th he sales tablee. And, whatt usually hap ppens with tables likke the sales table is that they are very long, prob bably like 100,000 transaactions or half a million transactiions or whatever. So, esssentially, yo ou have thesse 100,000 VLOOKUPs V r running just to get the product details whicch will slow down the w workbook and keep it un‐responsiv u ve and anytiime that a h you will have tto do this aggain and it ccan have a ssignificant peerformance impact. So change happens, what people do is th hat they writee the VLOOK KUPs and theen they replaace the VLOO OKUPs with jjust values, mulas as valu ues so that th he workbookk is faster. Th his means that anytime i.e. they copy and paaste the form hey need to rre‐write the formulas. So o, some peop ple even use VBA to do tthese steps the dataa changes, th automattically. All of these are u unnecessary if you could use Power Query to meerge multiplee tables to come up p with one view that you are after. No ow, I know th hat the mergge feature is very relevan nt for many Analysts who deal with w data thaat is in differrent places aas in differen nt tables. Bu ut, how would you feel he new featu ure in Excel 2013 which 2 is data modeling and relationships. We could usse them to about th combinee tables as w well. So, whatt are your co omments abo out this? Would you use a data modeel or would you use Power Queryy to combinee the data? Miguel: Well, it deepends. It really depeends on wh hat your en nd goal is. Both are completely c understaandable and both of them m can work. The benefitt of using Po ower Query o over the dataa model or Power P Pivot is that in Power Pivvot you can only use one type of reelationship th hat is the on ne‐to‐many relationsship where you need to aactually have a table that has unique vvalues. The w way that actu ually works
10 | P a g e
© Chandoo.org
C Chandoo.o org Podcasst Transcrript
with Pow wer Query iss that you can do any typ pe of relation nships betweeen two tables. This is go oing to get really tecchnical right now but witth SQL, you ccan do an inn ner join, left join, right join etc. Chandoo o: I understand what you u mean. We ccould come u up with all co ombinationss ‐ the left joiins and the right join ns. Okay, I un nderstand where Power Query will be better. And d, probably tthe other asp pect is that with thee data model feature of eeither Excel o or Power Pivot, it can only be used in n the contextt of a pivot Whereas, if alll the data is combined w with Power Q Query, you co ould then usee it in your formulas or table. W your VBA A or whatevver because aall the data is there and you can acccess it. Yeah, I think those are two places where w the Po ower Query option woulld be significcantly betterr. This is verry interestingg to know. Now, ob bviously, people who are listening to tthe podcast might be lefft wondering what an inn ner join is. I ho are just don't waant to ask th hat question because I kn now it is a very technicaal concept an nd people wh getting sstarted with Power Queryy might feel frustrated iff they try to understand iinner join on n day 1. So, we will leave that ou ut as a resource for them m to figure ou ut and learn but I think w we’ve had qu uite a good ussion wheree we covered a lot of grou und on Poweer Query, how w to get startted with it, and interesting discu on things yo ou can do, h how the pro ocess is simiilar to VBA or Power Piivot where some off the commo everything happens in a separatte window and where Po ower Query is different, for examplee it uses its own Steps Recorder and you can n re‐apply th hose steps, you can edit tthem, you caan change th he order of out the M lan nguage which h you mentio oned a few these steeps and whaat not. Now, we talked a little bit abo times in the call. Let me ask you w what this M language is aand why it is named so? Miguel: Well, I reallyy don't know why it is M! Chandoo o: Is it M for Money?! Thaat's what you ur book is. Miguel: I really don'tt know why iit is called M M! For a bit of history, it is really old eever since baack in 2003 and it haas been undeergoing changes and gettting better an nd better and d what we seee now is thee improved version after so man a ny iterations. Power Queery, just like Power Pivott, has its ow wn language. Whenever you actu ually click on n any of the buttons of tthe Power Q Query window w, the engine is actually creating a specific ccode for you u. It is writin ng its own co ode for you. So, you don n't actually need to know w any M to work witth Power Qu uery because Power Querry takes caree of that for yyou. Now, M M is simply a functional languagee. By functional we mean n functions like the ones tthat you actu ually write in n arithmetic w where: a+b=c c=20+d Those arre two functtions. That'ss the way yo ou can actuaally work witth Power Query. It is a functional languagee.
11 | P a g e
© Chandoo.org
C Chandoo.o org Podcasst Transcrript
Chandoo o: Yeah, I think when so omebody witth VBA or an ny other pro ogramming eexperience moves m to a functional language kkind of style,, it can be a b bit of a learning curve, isn n't it? Miguel: Yeah, well iit depends. TThe good th hing about this languagee is that in tthe way thatt someone delivers the actual basics of it, i.ee. if someon ne can actuallly teach you u the basics o of it, you can n learn this languagee way faster than you can n learn a langguage like th he Excel langu uage ‐ way faaster than th he one VBA has. Thiss is a conversation that I have had a lot with Ken n because, aggain, I don't really know that much about VB BA and so I rrely heavily o on Ken who is an Excel gu uru. He know ws a lot about VBA. We taalk about it a lot on how much time he actuaally spent to know what he actually kknows aboutt VBA and ho ow much in wer Query. Th he differencee is humongo ous; it is yearrs against a comparisson it actually took him tto learn Pow few dayss or even mo onths. So, to o be honest w with you, forr Power Queery, it can bee a really sho ort learning curve. Chandoo o: I understaand. I think tthat once yo ou overcomee the initial aapprehension ns and doub bts about it and actu ually start usiing the add‐iin, you can im mmediately start seeing the benefitss. There are lots of low‐ hanging fruits in the Power Query garden. You just go an nd pluck a feeature and u use it on you ur data and w much time am I saving w with this?!" TThat is like a positive feedback loop. Every time you say, "Wow, how u feel like yo ou want to d do more, wiithin six weeeks, you are doing awessome stuff w with Power that you Query. SSo, I totally agree with yo ou. I think the M languagge is somethiing that you don't even h have to get in to unttil very late in n the game; unless you w want to reallyy know whatt's going on, yyou don't evven have to look at it. Just to be honest, beccause I am im mmensely cu urious about these things, I downloaded the M uide which is nothing bu ut the M lan nguage guidee from the Microsoft we M formula reference gu bsite and I started rreading it but I felt that I didn't underrstand all of it and what tthis and thatt does. Then,, I realised, that I do on't even havve to learn th his. I can use Power Queryy. I can use the ribbon an nd I can play with it and I can gett a lot done. Nowadays, I rarely toucch the Script Editor screeen and I can still do mosst of what I want to do with Pow wer Query. There are som me places wh here I would spend somee time on Go oogle or on bsite but, forr most part, I can get thin ngs done without ever wo orrying abou ut the M langguage. That your web said, let's talk a little bit more about if I am an n Analyst and d if I am gettting started w with Power Q Query and I nology and I want to learrn more abou ut it, what kind of resources would am very curious about this techn ommend for such a perso on? you reco Miguel: I'm glad you ask. Ken and d I created this website aand its sole p purpose is just to give eveeryone the resourcees that they n need to get sstarted with P Power Queryy. Chandoo o: What is it ccalled? Miguel: TThe actual siite is http://p powerquery.training.
12 | P a g e
© Chandoo.org
C Chandoo.o org Podcasst Transcrript
Chandoo o: I'll make ssure that I lin nk to this UR RL in the sho ow notes butt it is http://p powerquery..training. It can't gett easier than n that; it's an awesome website nam me! So, whaat kind of ressources are we talking about heere? Can you u briefly mention about th hem? Miguel: What we pro ovide on thiss website is ssimple to usee products. TThese are reaady‐to‐go products like the one that we spokke about herre where we actually merrge tables fro om the sales table and th he products table; it is already in n there. You can just jum mp right into it, download d the samplee workbook and simply w you can putt this tool to o the test witth your own data. We acctually provid de a lot of reesources in see how terms off explaining w what Power Query is, wh hy you should actually usse it, the com mparison bettween VBA and Pow wer Query and the Excel language. That sort of stu uff is actuallyy on that sitee. Nonetheleess, besides providing all of this ffree material, we also prrovide live on nline workshops. The livee online workshops are d to the basiics of Power Query and tthe second basicallyy just 4 hourss on 2 days. The first dayy is dedicated day is to make you a pro and an EExcel guru in terms of Pow wer Query and the actuaal M languagee. Chandoo o: These aree live classess where people will attend at a speccific time an nd they will attend the session cconducted byy you and Keen and go thrrough all the steps of Pow wer Query? Miguel: Yeah. Chandoo o: When is th he next batch h starting forr this? Miguel: We actually had a worksshop schedulled for 12th August but itt got sold ou ut in less than 3 weeks! So, we are going to b be launching a new set off dates for the 26th August. Chandoo o: So, the nexxt one is on 2 26th August.. How often aare you runn ning these byy the way? Miguel: The actual co oncept or go oal is to have one such evvent per mon nth. The idea of why we aare actually doing this is becausee Power Query is being co onstantly upgraded and m more featurees are being added. So, every mo onth you aree going to be getting a new version off Power Querry and that iss amazing beecause they are activvely workingg on this. It iis not sometthing that th hey are goingg to take ligghtly. They are actually investingg a lot of tim me in this. TThe way thatt we want to o take advantage of thaat is that wee want this worksho op to providee the latest an nd the greateest. Chandoo o: Great. So, the next battch is on 26th h August whiich will be arround the corner when th his podcast goes livee and so thaat gives 3 weeeks’ time to o people wh ho listen to iit as soon ass the podcasst is out to
13 | P a g e
© Chandoo.org
C Chandoo.o org Podcasst Transcrript
participaate in this class. But, if tthey miss ou ut, they can always catch h the next seession which h is once a month aat least. Thatt's good to kknow. Maybee I will sign myself m up fo or one of theese classes o once I have better baandwidth and watch you u and Ken talk about this and learn fro om you live. I will leave a link to the training program on the show no otes page on n http://chan ndoo.org/session40 so that people caan go for it. u briefly telll us the co ost for thiss live class and if you have any discount orr anything Can you for http://chandoo.o org visitors. Miguel: Yeah, we aree going to leeave a discou unt for you gguys. The actual list price is $595. Hop pefully, we are goingg to give you u a really goo od discount fo or being an aactive listeneer for this pod dcast. o: Thank you u so much Miguel. I will p put up the lin nk for the traaining prograam web pagee and offer Chandoo the discount code in the podcaast show no otes page. TThat's good to know. Ap part from th he training m, do you wan nt to talk a litttle bit aboutt the book th hat you and K Ken authored d? program Miguel: As part of tthis entire eenvironment that we aree creating w where we pro ovide the reeady‐to‐use productss, provide all of these reesources to aactually get tto know Pow wer Query an nd provide this course, we just ffinished writting a book. TThe name off this book iss 'M is for Daata Monkeyy'. To be honest, I don't really kn now what this means butt it is more o of a US or Caanadian kind d of name. I actually speak Spanish and so I don't know tthat much bu ut I like it. Chandoo o: The first tiime I saw the book namee I thought itt was funny. I kept laugh hing so it is ggood. Yeah, please teell me more aabout the bo ook. Miguel: The actual b book has really unique caases and som me of them tthat we actually cover in the actual worksho op that are never before seen. These are basicallyy products th hat we haven n't published d on any of our blogg posts or You Tube video os or anythin ng like that. This is reallyy great and u unique conteent that we haven't published that you are going to be getting in thiss workshop aand also in th his book. So,, it is never before seeen and you better get itt. Chandoo o: When is th his book releaasing? Miguel: Hopefully, itt is already o on pre‐orderr on Amazon n. You can prre‐order it. If you actually take the worksho op, we are go oing to be givving you thee digital copyy of this bookk as well oncce it becomees available but it sho ould be availlable by Octo ober of this yyear.
14 | P a g e
© Chandoo.org
C Chandoo.o org Podcasst Transcrript
Chandoo o: Great. I asssume you have done the writing parrt of the boo ok and it is n now in the co opy‐editing and prod duction stagees. Is that right? Miguel: Yeah, well, w we actually d delayed the rrelease date because theere were som me things thaat were not publisheed yet and so o we waited so they werre actually published so that we could actually w write about them especially abou ut the Power BI integration and all tthat good stu uff. What you can expect from it is nt. You know w Ken; he is amazing. just awesome conten Chandoo o: Yeah, I haave no doubtts about thaat. I know bo oth of you are very passionate, know wledgeable and enth husiastic peo ople and so I am just waaiting for thee book to co ome out so I can grab a copy of it. Miguel, I just want to o thank you ffor sparing aan hour and ttalking to our audience about Power Query as a technolo ogy. It is a reeally transformative tool and I have been promising my pod dcast listeneers about a podcast dedicated to o Power Query for quite ssome time and so I am veery happy that I am talkin ng to you. I duled this a couple of times but due to o travel or on ne thing or aanother, we ccouldn't do know wee have sched it earlierr. So, I apologgize for the d delay but I am m very happyy about this ccall and this p podcast. I think this is a really infformative session and I am sure our p podcast listen ners are goin ng to love this. Miguel: TThank you fo or the actual opportunityy. Chandoo o: Thank you u so much fo or joining mee once again Miguel. I'll cconnect with h you once th he podcast goes livee. Miguel: Awesome.
15 | P a g e
© Chandoo.org
