Excel Power Query #12: Find & Replace, Group By 3 Columns, Load With Existing Connections

welcome to power query video number 12 hey if you want to download this workbook and follow along click on the link below the video and we got a great power query trick here look at this we have a region column and our system always records West and Midwest as a condition or criteria for a particular record and when we summarize we only want west and east meaning we need to combine these two now of course we could do some formula and say or criteria here and a straight East here but the advantage to power query is we will open up our query build all the steps and it will be automatic as long as we refresh so when we add new records we just click refresh and it'll all update all right so I'm going to click in this table and I've already converted it by insert table and then I already went up and named it call table so I have a single cell let's go to power query from table or we can use the keyboard alt y1 y7 that's a keyboard in 2013 alright now I want to highlight the region column and check this out we're going to go to replace values and I'm going to find Midwest and replace it with West now match entire cell contents our system is always going to have Midwest replacing West there's not going to be a problem so I'm going to click OK instantly it does a find and replace now the 15 and 34 they're both for West and we actually want them combined so we're going to use group by but if we said just group by region column it would take the unique values and give us two values I actually want the date the sales rep and the East so we're going to use the group by so I'm going to click on group by and we're going to start with date and then I'm going to add and I'm going to say employee and finally we're going to add a third condition in essence region so what the group by and you see this in access and also here in power query it's really a calculation with a condition or criteria we have three of them and we want a unique list the new column will be called total tab the calculation will be sum and the column we're going to do a calculation upon is calls so now with these three conditions is that it will give us a unique list in essence it will remove the duplicates where there are two regions when I click OK boom there we go now we definitely want to change the type and I'm going to go up to data type and say date now I'm using the November update of power of November 2014 power query update so there's a problem with dates and they said they fix it soon but we'll import it as a date because that's what we need we have our columns I'm going to come over here and call this final table tab and now we can close and load to the sheet table existing worksheet click the collapse button I'm going to select each level and click OK click load and there we have our table here now let's go ahead and update this and check this out here's our new records we're simply going to copy this and notice that there are when I copy there are new regions here now if we were trying to do this with the formula member originally I proposed well we could do an or but our formula might not have included any new regions but power query will handle that no problem I'm going to click in the cell a13 exactly the row below the last record control V and instantly that table feature is so amazing now we can come over here and right click refresh there's our query updating 18 rows and there's that error in the November 2014 power query oh that's so annoying I'm going to control one tab arrow arrow arrow and enter still hopefully they'll fix that soon maybe in the December or January update now I want to do control home and take a look if we look over here our first data set went down and it was missing one record on the tenth so when we got our record of here notice the 10th of December 22 for Moe for the Midwest and check that out here's the 22 on the 10th but the other Moe was up here in the first data set inch lo and behold those two numbers add up to 56 so it is absolutely beautiful on how it updates not only that but it got the new regions I love it you know you could do something with formulas that would take into consideration all the known regions but man pivot tables power query things like that the way they update just amazing now we have one last thing we want to see in this video here remember we have this date error when we import new data and refresh watch this this is a great workaround we actually right click Edit we loaded using the close and load to well watch this if you have a connection you don't have to load it we loaded it to the table but there's a connection in this workbook you could just load it straight to a connection and watch this I'm going to do this side by side so we can see and I got this idea from Daniel Dion in a comment below one of the videos this is just great we go up to data and of course we have existing connections there is our final table I'm going to say open there's a table we want to exist in worksheet m1 and click OK now this table comes in as a date and time I'm going to control one tab arrow arrow arrow to date and enter now this will only have to be done once and then later when we update let's go ahead and this is the original data source tab and I'm going to add a record 12/13 500 and this was a new rep in a new region alright now I'm going to right click refresh the first table and boom we get it without the formatting there's that date format now right click refresh in this table bloom we don't have the same problem with our date it is so awesome hanging out on our online Excel team and man power query is awesome alright we'll see you next trick

Channel: ExcelIsFun

Views: 20,784

Rating: 4.9024391 out of 5

Keywords: Excel Power Query, Excel 2010, Excel 2013, Mike Girvin, Michael Girvin, Mike excelisfun Girvin, excelisfun, Highline College, Power BI, Microsoft Power BI, Find & Replace, Group By 3 Columns, Load With Existing Connections, Avoid November 2014 Date Error, Work Around For November Error, Find Values, Transform Group, Home Ribbon Tab, Change Date Type, Daniel Dion, Excel, Load Table, Data Ribbon Tab, Get External Data, From Existing Connections, Summary Report

Id: aijXoglf8wo

Length: 7min 13sec (433 seconds)

Published: Tue Dec 09 2014