From Excel to Power BI in 12 Minutes (Beginner Essentials)

ever felt like you're wrestling with spreadsheets trying to make sense of overwhelming data only to end up with static charts that barely scratch the surface of what's truly happening you're not alone the leap from Excel to powerbi is about turning that frustration into empowerment for those at the starting line of data analysis in powerbi this journey transforms daunting data into Dynamic insightful visualizations powerbi excels where Excel reaches its limit with Advanced Data integration it connects to almost any data source you can think of its visualization capabilities are not only more sophisticated but also interactive allowing for insights that speak volumes more than static charts realtime analytics po has it covered offering live updates directly in your dashboards and when it comes to collaboration pobi makes sharing insights Breeze across your organization simply put powerbi is designed to make your data work harder for you but first things first let's get powerbi desktop which is the free design tool and the perfect starting point head to the Microsoft powerbi website and down under Microsoft powerbi desktop click on the download button or if you prefer you can go to Advanced download options run the installer and follow the simple steps and in no time you'll be greeted with the powerbi interface ready to start your data Journey we're going to create a new report so I'll click on the new report button and next I want to bring in my data which is in an Excel file so I can simply click on import data from Excel don't forget there are tons of different sources that you can get data from navigate to your file I should point out that if you're using Excel the Excel file needs to be closed before you can load it into powerbi so let's go ahead and open it here I have a list of the items in my file I've got a table and one sheet table one is on sheet one so I'm going to go with the table and you get a preview here on the right and from here I can simply load it directly to my pobi data dat model alternatively I can click on transform data and this opens the power query editor window here you can clean your data remove duplicates filter columns unpivot to name a few this step is crucial for ensuring your analysis is based on accurate and relevant data cleaning your data and power query before starting your analysis is the key to saving time and avoiding headaches down the line trust me for our project today I've cleaned the data set just to keep things streamlined so I'm going to go to the Home tab and close and apply to load the data to the powerbi data model if you're curious about mastering data cleaning in power query I have a dedicated course on powerquery to get you started and it doesn't end there I also offer a comprehensive course on powerbi which you can get in a bundle with pqu and power pivot to fully round out your data analysis skills and you can find the links in the video description the data I'll be using is 12 months worth of fictitious customer feedback data by category feedback source and sales associate ID now before we build our report I'm just going to select the customer ID column and on the column tools I'm going to change the default summarization to don't summarize and likewise for the sales associate ID this will just prevent the visuals from thinking they should sum those fields okay let's create the visuals and take advantage of power's unique features to create a dynamic and interactive dashboard I'm going to start with a column chart and you can insert it from the drop down here or via the view tab you can turn on the different panes and use the task panes on the right for example here I want the clustered column chart pops it on the left I'm just going to bring it over to the right closer to my task panes I'm going to collapse the filters I don't need them right now in the data pane I've got my table and all the columns which represent the fields that I can work with the first thing I want is the date and notice it's inserted it and it's grouped it in into a date hierarchy which is super helpful I don't want to be able to go down to the day level of details so I'm going to remove day from my hierarchy and next I want to see the rating now it's defaulted to sum the rating here I'm going to average it instead now currently the charts displaying the data aggregated to the year level I'm going to click on the Pitchfork icon to drill down first we go to quarter and then month's level let's make it a bit wider so we can see the month's horiz I onally all right let's do some formatting I'll click on the formatting Pane and firstly the x axis I don't need the title it's pretty obvious their dates for the Y AIS I'll do the same turn the title off I'm also going to turn the values off because instead of having the axis I'm going to add data labels and let's just go in here and change the position to inside the end lastly let's double click on the chart title because we don't need by year quarter and month that's obvious and we'll just say average rating let me left click and drag just to move the chart down off the very top and give it some white space all right next let's insert a pie chart and again I'll just bring it over closer to the task panes here I want to look at my data by category and I want to know how many customers gave feedback by category so I'm just going to left click and drag it into the values area and it's counted the customer which is perfectly fine all right let's do some formatting we'll start with the title and I can also change it in here so we'll call it feedback count by category next I don't want the legend so let's turn that off and then under detailed labels we need to add the legend information back in so I'm going to choose category and percent of total all right let's move that over here and I'll just roughly resize it next I want a bar chart so I want this clustered bar chart and this is going to show the feedback source and I want the response time but instead of sum let's change that to average and let's do some formatting we'll start with the title so we'll just call this average response time and we don't need to say buy feedback Source that's obvious next let's remove the titles from the axes because they're self-explanatory and again we'll turn off the values for the x axis and instead we'll add data labels and we'll position them inside the end all right let's make this a bit smaller so we've got space for one more next I want to insert a matrix table and this is a bit like a pivot table so here I want to look at my data by sales associate down the rows and categories in the columns and then let's look at the rating and again instead of sum let's let's change that to average now tables don't come with a title by default so let's add one and we'll call this average rating now let's add some conditional formatting under cell elements I want to put background color on the default formatting is pretty good it's highlighting the larger values in a darker color and the smaller ones in a lighter shade of blue and that just helps us easily identify the scale within each sales associate all right let's make this a bit narrower and give our other charts more space one of the cool things about powerbi is the ability to interact with your visuals for example here I've clicked on the product segment in the pie chart and now my column and bar charts show the product values highlighted with a darker bar and the Matrix table is now filtered to display just the product column clicking on another segment changes the focus I can do this with the bar chart as well so now everything in my report is highlighting social media likewise I can do it with the column chart now my visuals are all filtered to highlight or only display data for March and just left click again to remove the cross filtering and highlighting now another way we can filter is with slices so let's go ahead we've got actually got a new slicer so let's use that one and I want to be able to filter based on the feedback Source the slicer populates with the unique values from that field and from here you can customize the appearance and behavior of your slicer I'm going to go in and change the layout to only have one column and that's just going to make my slicer buttons display in a vertical layout now when I click on the slicer all visuals are filtered this means with just a few clicks you can reshape your entire dashboard to focus on product feedback from email and every chart automatically reflects this Source it's a streamlined and integrated way to explore your data making powerbi unique Neely powerful for uncovering insights in complex data sets now I can also control which visuals the slicer filters via the format Tab and then edit interactions for example I already have the average response time Broken Out by feedback source so I don't also want to filter this bar chart I can turn off the filtering by clicking on the none icon and then I'll turn off edit interactions so now when I filter with the slicer notice that the bar chart doesn't get filtered now before we look at the next cool feature let's double click the page and rename this summary pv's drillthrough feature allows you to create Focus pages in your report that provide detailed information about a specific data point selected from another page now I've added a page to my report to illustrate how it works for example this page is now filtered for sales associate 5 and I can focus in on their performance it's a powerful way to offer detailed insights without over crowding your main dashboard to set up a page for drill through go to the page information under formatting and then under page type just select drill through next you need to add the field that you want to be able to drill through from so I want the sales associate notice in the top left it's now added a button and that allows me to control and left click to go back to my original page now when I rightclick on any of the sales associates in the menu I get drill through and I can choose the page you can have more than one let's go to sales associate and now this page is filtered for sales associate 12 which is the one I right clicked on pretty cool okay let's contrl s to save the report and look at publishing sharing your interactive dashboards in powerbi allows stakeholders to explore Data Insights in a dynamic and intuitive way and unlike Excel sharing in powerbi is easy and secure you can even lock down different views of the data depending on the person who views the report which saves you having to create multiple versions of the same report for each recipient and I cover this technique in my powerbi course to share a report that you build in powerbi desktop go to the file tab publish publish to powerbi you'll need to sign in with your powerbi account if you're not already and then choose the workspace you want to publish to and click select now depending on how big your report is this could take a few minutes once published go to the powerbi service in app. powerbi.com and navigate to your report so mine is in my demos workspace and I've got a report and a semantic model which is the data set so clicking on report you can see I've got my sales associate page and my summary page now one way you can share reports for free is via the file tab in bed report and then publish to web which is going to be publicly available alternatively you can embed them in a website or portal although that requires a pro or premium license alternatively you can securely share your reports with other powerbi users within your organization by the share button this also requires Pro or premium license in the powerbi service cross filtering and highlighting works in the same way as power desktop enabling your users to personalize their view of the report without affecting what other users see if today's overview sparked your interest there's more to discover check out this detailed video for a deeper dive into unlocking the full power of your data with pobi see you in the next video

Channel: MyOnlineTrainingHub

Views: 30,801

Keywords: Mynda Treacy, My Online Training Hub, power business intelligence, learn power bi, master power bi, power bi for beginners, power bi from scratch, power bi for business analytics, what is power bi, understand power bi, how to learn power bi, microsoft power bi, transitioning from excel to power bi, comparing excel and power bi, power bi tutorial, power bi tutorial for beginners, power bi dashboard, power bi dashboard tutorial, power bi beginner, power bi essentials, powerbi

Id: gOs7EC-FebE

Length: 12min 42sec (762 seconds)

Published: Tue Mar 12 2024