Excel Magic Trick 1332: Power BI Desktop: Import Multiple Excel Files & Build Dashboard

welcome to excel major trick number 1332 hey wait a second this is an Excel magic trick but I'm going to show you power bi desktop now here's our goal in this field and it's the same goal we have last video we have a bunch of Excel files of proper datasets and we need to import all of them and stack them up one on top of each other so we have a single table and then we need to create this visualization in power bi desktop now I'm going to look at the final result here's what we're trying to do boomerang incorporated 2016 - 2017 sales here are some cities and here are some sales rep if I click on Portland Qin instantly all the other visualizations are filtered if I come up and click on quad instantly all the visualizations update and are filtered if I come over to the map and click just on Oakland instantly everything is filtered now that's our end result but we're using power bi desktop now why in the world what I call us an excel magic trick the reason why is we're going to use all the skills we learned in our last video one three three one and use them in power bi desktop now I'm no expert with power bi desktop I'm just going to take my excel skills and see if I can use them over there to make this amazing visualization now two things you are going to have to go to the link below the video and download this folder that contains all of these files now you have to download it as a zipped folder and unzip it to get to these files and we'll look at these files in just a moment you're also going to have to download power bi desktop now you just go to google search for it it's a free download from Microsoft I'm going to close this now why in the world would we download power bi desktop when we already have Excel with power query and power pivot and visualization tools well this is a free tool you do not need Excel and it automatically in one tool combines power query power pivot Power View and a bunch of visualizations and for us Excel users we can apply many of our power query and power pivot skills over here so it'll look good on our resume if we've used it a few times say yeah views power bi desktop now in this example we will only use the power query and the visualization part of it we won't make any Dax formulas we'll come back to this and look at this in just a second but guess what all of our power query skills that we learned last video for importing and transforming multiple Excel files would be the same over here in power bi desktop now let's go look at our files just like last video we have a bunch of files and I put some extra files like an Access file and a dot CSV and text that we'll have to filter out but when I open one of the city files each one of the city files will have date product units and sales and on each sheet tab there will be sales rep so we're going to have to consolidate these four columns we're going to have to add an extra column with sales rep and we're going to have to add an extra column that will incorporate this CD name no problem for power query or power bi desktop all right so now I'm over here in power bi desktop before I do anything I'm going to save as and guess what the keyboard f12 works over here just like in Excel I'm going to call this I called it one three three - boomerang Incorporated 2016 217 dashboard and you can save it where you want all right now power car we got to go up to and look at this get external data there's just get data that's going to be power query I don't see from folder so I'm going to go down to more get data and there it is I'm going to click on folder double-click looks kind of like power query over cell click browse' point to the folder we're telling power bi desktop slash power kori to get all the files in that folder I click OK click OK I'm going to click Edit I'm going to click apply changes I don't know why it popped over here I'm going to right click edit query and there's what should have popped up I'm not quite sure why I did but guess what this is very similar to power query only a few different elements in our query editor window now the first thing we need to do is filter out to show only xlsx and xlsm notice that there's some capitalized one and since case matters in power bi desktop and power Korea I'm going to right click transform a lowercase now I want to filter to only include xlsx and dot XLS M come up to the filter text filters equals and I'm going to do this different than our last video now in our last video we said begins with dot XLS but in this query here I want to exclude any dot XLS or dot XLS B because those files aren't quite as easy to deal with as our X's and M's so I'm going to say equals dot xlsx and then or you can also equal dot XLS M that way I'll only get those to click OK now we need to get the city name so I'm going to split it after the dot select the column split column by delimiter a delimiter is just something that separates data it's not a comma I'm going to come down to custom it's going to be a period we're going to say split at rightmost delimiter just in case someone had a period near the front click OK now we don't need any other columns so I select name hold shift click content right click remove other columns we need to get at the data in this column we cannot expand that that little double downward pointing arrow is for text data and we don't have text atoms we actually have Excel workbooks that contain objects so we're going to add an extra column to extract the excel objects add custom column I'm going to call this column get excel objects notice this window looks exactly the same as over in power query we're going to use the same function excel . workbook open parentheses now case matters and spelling correct manners i need to get at the content in this column right here so i'm going to double click now that content contains a bunch of different objects but when we get to the sheets that we want with the headers date sales and so on i need to promote those headers so the second argument in excel dot workbook is do you want to promote headers in it's true all lowercase so the first argument is the excel objects or the content the second one is promote headers close parentheses click okay and there's our extra column right click content we don't need that remove now I'm going to expand the column and these are a bunch of different aspects for those excel objects I actually do not need hidden I only need kind so I'm gonna uncheck hitting kind will give us the type of object whether it's a sheet or table or define name I don't need item I need data and I need name that'll be my sheet name click OK now the first thing we want to do is the objects their sheets table define names we only want sheet so I click the filter text filters equals and I want to only equal capital s H ee t click OK everything's filtered out except for sheet over in our sheets looks like they're all named properly we want to filter out any sheets that begin with eat so I come to the drop down text filters does not begin with and I'm going to type sheet this will filter out any you know sheets that weren't named properly click OK now I don't need kind right click remove now I can expand to finally get to the actual transactional records I click the expand button I want all of them but I do not want to use the original column names up here I want just these names so I click OK now I'm going to come over here and double click and call this city enter remember that data came from the file name I want to also check in the transform ribbon tab the actual data type text is fine double click I'm going to call this sales rep and enter I'm going to check the data type and change it to text date I want to change the data type to date product data type to text units data type to hole number sales change the data type to decimal number now I want to come over and name this query notice there's all the steps just like over in power query I'm going to name this all city tables and enter now I'm going to go over to the home and close and apply now here's our blank white canvas that we get to build our visualizations over here here's our visualization there's our table and there's any relationships we don't have any relationships we have a single table here go back to visualizations now I actually just hit pause because I wanted to update my power bi desktop which I hadn't updated in the last month and guess what you will actually have to go back out to the site and redownload it every time there's not an update button like there is inside of Excel to update the latest Power Core but I want to show you something I'm going to go over here's the table we just imported I'm going to run I click Edit query and when we open it back up look at this it has these icons which came out recently for data types so now this is looking even more like power query we did in our last video all right this is all working fine I'm going to close this now we want to make some visualizations here's we have three visualizations here's our visualizations this will be the area like the pivot table field list where we drag fields and drop them here's our table and there's our field list I'm going to start by selecting the visualization I'm going to click on the map we instantly see a little preview there I'm going to check city and then check sales and just like that I'm going to pull this down for sales we can see the various cities there Seattle Tacoma Portland San Francisco actually San Francisco and Oakland alright now I'm going to click in the white somewhere and do my next visualization they're going to be two bar charts I'm going to click on the bar chart drag it down to the bottom over to the side now I'm going to drag City down to the axis and instantly we see nothing I'm going to drag sales down to the values and there we go now I need sales rep in the legend and instantly at the top we see our legend now I'm going to click in the white somewhere and do a second bar chart click on the bar drag it over or drag it down product it's going to be in the axis sales it's going to be in the values maybe try and drag it down a little bit make the map a little bit smaller I'm going to add a title at the top text box here's my text box I'm going to click alright something like boomerang incorporated 2016-17 sales control a to highlight everything I'm going to make it something like 32 drag this so it's on one line pull it up near the top format I want to align Center click off in the white somewhere that already is looking amazing in here is what is so amazing if I click on Aspen everything else is filtered if I come back and click a second time it's unfiltered if I come down to Oakland Fran there it is all of the filtering I click again and it's unfiltered there's all of Portland Seattle the Oakland sales and there were filtered on Oakland if I go back and click Oakland back to unfiltered now here are five cities remember this is power bi desktop and it's pointing to a folder so let's go drag a new file into that folder I'm going to come back over to Windows Explorer one of the files you can download is San Jose I'm gonna copy it double click to open that folder control B there it is a new file with a dot xlsx so when we go back to power bi desktop we go to home refresh and there it is it's refreshing and just like that we have San Jose and there it is right there if I click on San Jose everything is filtered if I uncheck it there we go so that was pretty amazing power bi desktop well it was kind of just like using power query and a pivot table field list to create this great visualization all right we'll see you next trick

Channel: ExcelIsFun

Views: 37,045

Rating: 4.9700599 out of 5

Keywords: Excel, Microsoft Excel, Highline College, Mike Girvin, excelisfun, Excel Magic Trick, Power BI Desktop, Power Query, Power Pivot, Import Multiple Excel Files in Power BI Desktop, Clean and Transform Data in Power BI Desktop, Build Dashboard in Power BI Desktop, Refresh Data in Power BI Desktop, Excel & Power BI Desktop, Power Query and Power BI Desktop, How to use Power BI Desktop, What is Power BI Desktop?, Learn Power BI Desktop Basics, Introduction to Power BI Desktop

Id: uCo-ypA-G70

Length: 15min 23sec (923 seconds)

Published: Wed Oct 19 2016