45 Courses

Power Query and Power Pivot in Microsoft Excel:  A Quickstart Guide   Course Description   Copyright Ross Maynard 2022   Course Description Power Query and Power Pivot are two tools available in Microsoft Excel. If you are serious about data analysis, then you need to know how to use these tools. This Quickstart guide will give you the skills you need to get started with these powerful features. The Power Query tool allows you to import external data and then modify it to meet your needs, before loading it into Excel. These editing tasks are all things you can do manually in Excel, but Power Query greatly speeds things up when you have a large volume of data. The Power Pivot tool allows you to connect multiple data tables in an extended pivot table. Power Pivot also introduces new analysis and query tools to further enhance data analysis. If data analysis is your thing, then this course will help you get up to speed with these feature-filled tools. Through screen-capture video, the course provides a step-by-step guide to the foundation skills needed to use both Power Query and Power Pivot effectively. The course is designed to make your first steps into Power Query easy and to build your confidence to use them on your own. Both Power Query and Power Pivot are also available in Microsoft Power BI – the data visualisation suite. Once you have learned how to use Power Query and Power Pivot in Excel with this course, you’ll have a head start in getting to grips with Power BI Get up to speed with Power Query and Power Pivot in Microsoft Excel with this Quickstart guide.     Key Learning Points  On completion of the course, delegates will be able to: * Use Power Query to load data from an external source into Microsoft Excel * Clean and manipulate external data before loading it into Excel * Consolidate separate files with Power Query * Update Excel tables with new data in the same format * Use Power Query to “unpivot” report tables to prepare the data for analysis * Explain the core functionality of Power Pivot * Link data tables together in the Power Pivot data model * Create pivot tables that draw data from several source data tables * Create performance measures and new analysis in the Power Pivot data model * Use the most popular DAX query language functions in Power Pivot     Curriculum Lesson 1: Power Query Quickstart Lesson 2: Consolidating Files with Power Query Lesson 3: Unpivoting a Table Lesson 4: Power Pivot Quickstart Lesson 5: Power Pivot – Adding Data and Functionality Lesson 6: Key DAX Functions for Power Pivot Lesson 7: The DAX CALCULATE Function Lesson 8: Key Learning Points     Pre-Course Requirements There are no pre-course requirements     Additional Resources Starter spreadsheets for each lesson are provided so that learners can follow the steps presented.     Course Tutor Your tutor is Ross Maynard. Ross is a Fellow of the Chartered Institute of Management Accountants in the UK and has 30 years’ experience as a process improvement consultant specialising in business processes and organisation development. Ross is also a professional author of online training courses. Ross lives in Scotland with his wife, daughter and Cocker Spaniel     Questions 1. What is the main feature of Power Query?  1. The ability to link data tables together to create pivot tables that allow data to be drawn from any and all of the source tables 2. The ability to clean and manipulate large volumes of external data efficiently and to load the result into Microsoft Excel 3. The removal of Microsoft Excel’s restrictions on file sizes and the number of rows of data allowed 4. The ability to visualise data in dashboards and management reports     1. What is the main feature of Power Pivot?  1. The ability to link data tables together to create pivot tables that allow data to be drawn from any and all of the source tables 2. The ability to clean and manipulate large volumes of external data efficiently and to load the result into Microsoft Excel 3. The removal of Microsoft Excel’s restrictions on file sizes and the number of rows of data allowed 4. The ability to visualise data in dashboards and management reports     1. Which of the following actions cannot be performed within Power Query? 1. Split text into multiple columns 2. Trimming text and removing spaces 3. Creating calculated columns using DAX functions 4. Make date based calculations   1. Which of the following statements is true? 1. You can use Power Query to combine files that have the same format and open them all in the same spreadsheet  2. You can use Power Query to link data tables and create pivot tables that pull data items from all the linked tables 3. You can use Power Query to create interactive dashboard reports 4. You can use Power Query to export data files to Microsoft Access and other databases     1. Which of the following provides a compelling case to use Power Pivot in Microsoft Excel? 1. The data visualisation and presentation features of Power Pivot are much more powerful than those in Excel 2. Power Pivot offers the ability to clean large files of data imported from other systems quickly 3. Existing reports and tables can be “reverse engineered” (unpivoted) to create data tables for more advanced analysis 4. Files sizes in Power Pivot are smaller than in Excel and there is no limit on the number of rows of data that can be stored. This makes it much faster and more efficient to use Power Pivot for large data sets     1. Power Pivot uses analysis functions from the DAX formula language. What does “DAX” stand for? 1. Digital Architecture Extraction 2. Data Arbitrage Extrapolation 3. Display Analytics Extension 4. Data Analysis Expressions 5. Deeply Annoying Exercise   1. The DAX CALCULATE function is one of the most powerful and useful functions available in Power Pivot. Which statement best describes it? 1. It performs row by row calculations defined by the user and adds the total of them all together for a specified table  2. It performs row by row calculations defined by the user and displays the maximum result for a specified table 3. It performs a defined calculation and then applies filters specified by the user 4. It filters the data in a specified table according to criteria set by the user   1. As well as in Power Pivot in Microsoft Excel, in what application are DAX functions also available? 1. Microsoft PowerPoint 2. Microsoft Forms 3. Microsoft Teams 4. Microsoft Power BI   1. How do DAX functions differ from Microsoft Excel functions? 1. DAX functions are designed specifically for use with SQL based data sources 2. DAX functions are completely new and provide additional functionality within Microsoft Excel  3. DAX functions never take a cell reference or a range as reference, but instead take a column or table as reference 4. DAX date and time functions return a date as a serial number, whereas Excel date and time functions return a datetime data type  

Supercharge your skills and career and learn in-demand knowledge needed to build business intelligence dashboards. This beginner to intermediate level course will introduce you to all the Power BI technologies i.e. Power Query, DAX, Data Modelling (Power Pivot), M, types of visualizations, etc.

Learn how to work with and connect multiple data sets to effectively analyse and report on data. -------------------------------------------------------------------------------- COURSE OVERVIEW Duration: 1 day (6.5 hours) Within Excel you have some powerful features to enable you to connect and analyse multiple data sources. Power Query enables you to import and manipulate your data, Power Pivot enables you to connect multiple data sources and create pivot tables and pivot charts from them. This course is an introduction to Power Query and Power Pivot in Excel to get you started on creating a powerful reporting capability. Knowledge of working with Excel workbooks and relational databases would be an advantage. OBJECTIVES By the end of the course you will be able to: * Import data from multiple data sources * Edit and transform data before importing * Add extra columns of data * Append data * Merge data from other tables * Create data models * Build data relationships * Build Pivot Tables * Build Pivot Charts * Use Slicers and Timeline Filters CONTENT IMPORTING DATA  * Data sources * Importing data TRANSFORMING DATA   * Editing your data * Setting data types * Removing columns/rows * Choosing columns to keep * Setting header rows * Splitting columns APPENDING QUERIES  * Appending data from other tables ADDING TEXT  * Columns from example * Custom columns * Conditional columns MERGE QUERIES  * Setting up and using merge queries * Merging in columns of data CREATING A DATA MODEL  * The data model * Multiple data tables * Connecting tables * Building relationships * Relationship types * Building visuals from multiple tables ANALYSING INFORMATION USING PIVOT TABLES * Creating and modifying a Pivot Table * Recalculating the Pivot Table * Filtering the Pivot Table * Searching the Pivot Table * Drilling down to underlying data * Customising field names * Changing field formatting PIVOT CHARTS, SLICES AND TIMELINES * Creating Pivot Charts * Adding and using Slicers

This course is designed for students who already have foundational knowledge and skills in Excel and who wish to perform robust and advanced data and statistical analysis with Microsoft Excel using PivotTables, use tools such as Power Pivot and the Data Analysis ToolPak to analyze data and visualize data and insights using advanced visualizations in charts and dashboards in Excel.

Duration 2 Days 12 CPD hours This course is intended for This course is designed for students who already have foundational knowledge and skills in Excel and who wish to perform robust and advanced data and statistical analysis with Microsoft Excel using PivotTables, use tools such as Power Pivot and the Data Analysis ToolPak to analyze data, and visualize data and insights using advanced visualizations in charts and dashboards in Excel. To ensure success, you should have baseline skill using Microsoft Excel worksheets, particularly in creating workbooks with formulas and function Overview #NAME? Analyzing data to find issues, insights and opportunities, is now a critical part of many job roles. Beyond the analysis, data analysts in all job roles must be able to effectively present and communicate their findings in visually compelling ways. Microsoft© Excel© is designed for this purpose. Excel can connect to a wide range of data sources, perform robust data analysis and create diverse and robust data-backed visualizations to show insights, trends, and create reports. These capabilities enable people who use Excel for data analysis to turn data into thoughtful action. 1 - DATA ANALYSIS FUNDAMENTALS * Introduction to Data Science * Create and Modify Tables * Sort and Filter Data 2 - VISUALIZING DATA WITH EXCEL * Visualize Data with Charts * Modify and Format Charts * Apply Best Practices in Chart Design 3 - ANALYZING DATA WITH FORMULAS AND FUNCTIONS * Analyze Data with Formulas and Named Ranges * Analyze Data with Functions * Implement Data Validation, Forms, and Controls * Create Conditional Visualizations with Lookup Functions 4 - ANALYZING DATA WITH PIVOTTABLES * Create a PivotTable * Analyze PivotTable Data 5 - PRESENTING VISUAL INSIGHTS WITH DASHBOARDS IN EXCEL * Visualize Data with PivotCharts * Filter Data Using Slicers and Timelines * Create a Dashboard in Excel 6 - CREATING GEOSPATIAL VISUALIZATIONS WITH EXCEL * Create Map Charts in Excel * Customize Map Charts in Excel 7 - PERFORMING STATISTICAL ANALYSIS * Visualize Trendlines and Sparklines with Excel * Analyze Data with the Data Analysis ToolPa 8 - GETTING AND TRANSFORMING DATA * Connect to Data with Queries * Clean and Combine Data * Shape and Transform Data 9 - MODELING AND ANALYZING DATA WITH POWER PIVOT * Install Power Pivot in Excel * Create Data Models with Power Pivot * Create Power Pivots * Perform Advanced Data Analysis and Visualization 10 - PRESENTING INSIGHTS WITH REPORTS (OPTIONAL) * Plan a Report * Create a Report

Gain the skills and credentials to kickstart a successful career and learn from the experts with this step-by-step

Gain the skills and credentials to kickstart a successful career and learn from the experts with this step-by-step

Register on the Excel PowerQuery and PowerPivot today and build the experience, skills and knowledge you need to enhance your professional development and work towards your dream job. Study this course through online learning and take the first steps towards a long-term career. The course consists of a number of easy to digest, in-depth modules, designed to provide you with a detailed, expert level of knowledge. Learn through a mixture of instructional video lessons and online study materials. Receive online tutor support as you study the course, to ensure you are supported every step of the way. Get a digital certificate as a proof of your course completion. The Excel PowerQuery and PowerPivot is incredibly great value and allows you to study at your own pace. Access the course modules from any internet-enabled device, including computers, tablet, and smartphones. The course is designed to increase your employability and equip you with everything you need to be a success. Enrol on the now and start learning instantly! WHAT YOU GET WITH THE EXCEL POWERQUERY AND POWERPIVOT * Receive a e-certificate upon successful completion of the course * Get taught by experienced, professional instructors * Study at a time and pace that suits your learning style * Get instant feedback on assessments  * 24/7 help and advice via email or live chat * Get full tutor support on weekdays (Monday to Friday) COURSE DESIGN The course is delivered through our online learning platform, accessible through any internet-connected device. There are no formal deadlines or teaching schedules, meaning you are free to study the course at your own pace. You are taught through a combination of * Video lessons * Online study materials CERTIFICATION After the successful completion of the final assessment, you will receive a CPD-accredited certificate of achievement. The PDF certificate is for £9.99, and it will be sent to you immediately after through e-mail. You can get the hard copy for £15.99, which will reach your doorsteps by post. WHO IS THIS COURSE FOR: The course is ideal for those who already work in this sector or are an aspiring professional. This course is designed to enhance your expertise and boost your CV. Learn key skills and gain a professional qualification to prove your newly-acquired knowledge. REQUIREMENTS: The online training is open to all students and has no formal entry requirements. To study the Excel PowerQuery and PowerPivot, all your need is a passion for learning, a good understanding of English, numeracy, and IT skills. You must also be over the age of 16. COURSE CONTENT Excel PowerQuery and PowerPivot pivot table power query power pivot - 1 00:09:00 pivot table power query power pivot - 2 00:01:00 pivot table power query power pivot - 3 00:02:00 pivot table power query power pivot - 4 00:03:00 pivot table power query power pivot - 5 00:01:00 pivot table power query power pivot - 6 00:04:00 pivot table power query power pivot - 7 00:08:00 pivot table power query power pivot - 8 00:05:00 pivot table power query power pivot - 9 00:03:00 pivot table power query power pivot - 10 00:04:00 pivot table power query power pivot - 11 00:04:00 pivot table power query power pivot - 12 00:02:00 pivot table power query power pivot - 13 00:03:00 pivot table power query power pivot - 14 00:05:00 pivot table power query power pivot - 15 00:02:00 pivot table power query power pivot - 16 00:03:00 pivot table power query power pivot - 17 00:03:00 pivot table power query power pivot - 18 00:08:00 pivot table power query power pivot - 19 00:02:00 pivot table power query power pivot - 20 00:04:00 pivot table power query power pivot - 21 00:05:00 pivot table power query power pivot - 22 00:09:00 pivot table power query power pivot - 23 00:04:00 pivot table power query power pivot - 24 00:03:00 pivot table power query power pivot - 25 00:04:00 pivot table power query power pivot - 26 00:09:00 Resources Resources - Excel PowerQuery And PowerPivot 00:00:00 FREQUENTLY ASKED QUESTIONS Are there any prerequisites for taking the course? There are no specific prerequisites for this course, nor are there any formal entry requirements. All you need is an internet connection, a good understanding of English and a passion for learning for this course. Can I access the course at any time, or is there a set schedule? You have the flexibility to access the course at any time that suits your schedule. Our courses are self-paced, allowing you to study at your own pace and convenience. How long will I have access to the course? For this course, you will have access to the course materials for 1 year only. This means you can review the content as often as you like within the year, even after you've completed the course. However, if you buy Lifetime Access for the course, you will be able to access the course for a lifetime. Is there a certificate of completion provided after completing the course? Yes, upon successfully completing the course, you will receive a certificate of completion. This certificate can be a valuable addition to your professional portfolio and can be shared on your various social networks. Can I switch courses or get a refund if I'm not satisfied with the course? We want you to have a positive learning experience. If you're not satisfied with the course, you can request a course transfer or refund within 14 days of the initial purchase. How do I track my progress in the course? Our platform provides tracking tools and progress indicators for each course. You can monitor your progress, completed lessons, and assessments through your learner dashboard for the course. What if I have technical issues or difficulties with the course? If you encounter technical issues or content-related difficulties with the course, our support team is available to assist you. You can reach out to them for prompt resolution.

OVERVIEW This comprehensive course on Excel PowerQuery and PowerPivot will deepen your understanding on this topic. After successful completion of this course you can acquire the required skills in this sector. This Excel PowerQuery and PowerPivot comes with accredited certification from CPD, which will enhance your CV and make you worthy in the job market. So enrol in this course today to fast track your career ladder. HOW WILL I GET MY CERTIFICATE? You may have to take a quiz or a written test online during or after the course. After successfully completing the course, you will be eligible for the certificate. WHO IS THIS COURSE FOR? There is no experience or previous qualifications required for enrolment on this Excel PowerQuery and PowerPivot. It is available to all students, of all academic backgrounds. REQUIREMENTS Our Excel PowerQuery and PowerPivot is fully compatible with PC's, Mac's, Laptop, Tablet and Smartphone devices. This course has been designed to be fully compatible with tablets and smartphones so you can access your course on Wi-Fi, 3G or 4G. There is no time limit for completing this course, it can be studied in your own time at your own pace. CAREER PATH Learning this new skill will help you to advance in your career. It will diversify your job options and help you develop new techniques to keep up with the fast-changing world. This skillset will help you to- * Open doors of opportunities * Increase your adaptability * Keep you relevant * Boost confidence And much more! COURSE CURRICULUM 3 sections • 28 lectures • 01:50:00 total length •pivot table power query power pivot - 1: 00:09:00 •pivot table power query power pivot - 2: 00:01:00 •pivot table power query power pivot - 3: 00:02:00 •pivot table power query power pivot - 4: 00:03:00 •pivot table power query power pivot - 5: 00:01:00 •pivot table power query power pivot - 6: 00:04:00 •pivot table power query power pivot - 7: 00:08:00 •pivot table power query power pivot - 8: 00:05:00 •pivot table power query power pivot - 9: 00:03:00 •pivot table power query power pivot - 10: 00:04:00 •pivot table power query power pivot - 11: 00:04:00 •pivot table power query power pivot - 12: 00:02:00 •pivot table power query power pivot - 13: 00:03:00 •pivot table power query power pivot - 14: 00:05:00 •pivot table power query power pivot - 15: 00:02:00 •pivot table power query power pivot - 16: 00:03:00 •pivot table power query power pivot - 17: 00:03:00 •pivot table power query power pivot - 18: 00:08:00 •pivot table power query power pivot - 19: 00:02:00 •pivot table power query power pivot - 20: 00:04:00 •pivot table power query power pivot - 21: 00:05:00 •pivot table power query power pivot - 22: 00:09:00 •pivot table power query power pivot - 23: 00:04:00 •pivot table power query power pivot - 24: 00:03:00 •pivot table power query power pivot - 25: 00:04:00 •pivot table power query power pivot - 26: 00:09:00 •Resources - Excel PowerQuery and PowerPivot: 00:00:00 •Assignment - Excel PowerQuery and PowerPivot: 00:00:00

The PowerBI Formulas course delves into the intricacies of Power Query and Power Pivot in Power BI, focusing on various techniques to enhance data analysis and visualization. Through a series of modules, participants will learn how to effectively use Power Query and Power Pivot to transform, manipulate, and model data for creating insightful reports and dashboards. LEARNING OUTCOMES: 1. Master the fundamentals of Power Query and Power Pivot in Power BI. 2. Understand data transformation techniques using Power Query. 3. Explore advanced data modeling with Power Pivot. 4. Learn to create calculated columns and measures. 5. Gain proficiency in using DAX (Data Analysis Expressions) formulas. 6. Apply Power Query and Power Pivot to real-world data scenarios. 7. Utilize data relationships and hierarchies for effective data modeling. 8. Create dynamic visualizations and reports using the transformed data. WHY BUY THIS POWERBI FORMULAS? 1. Unlimited access to the course for forever 2. Digital Certificate, Transcript, student ID all included in the price 3. Absolutely no hidden fees 4. Directly receive CPD accredited qualifications after course completion 5. Receive one to one assistance on every weekday from professionals 6. Immediately receive the PDF certificate after passing 7. Receive the original copies of your certificate and transcript on the next working day 8. Easily learn the skills and knowledge from the comfort of your home CERTIFICATION After studying the course materials of the PowerBI Formulas there will be a written assignment test which you can take either during or at the end of the course. After successfully passing the test you will be able to claim the pdf certificate for £5.99. Original Hard Copy certificates need to be ordered at an additional cost of £9.60. WHO IS THIS COURSE FOR? * Business analysts and data analysts seeking to enhance their Power BI skills. * Data professionals looking to leverage Power Query and Power Pivot for data analysis. * Professionals working with data visualization and reporting. * Individuals interested in learning advanced data transformation and modeling techniques. PREREQUISITES This PowerBI Formulas does not require you to have any prior qualifications or experience. You can just enrol and start learning.This PowerBI Formulas was made by professionals and it is compatible with all PC's, Mac's, tablets and smartphones. You will be able to access the course from anywhere at any time as long as you have a good enough internet connection. CAREER PATH * Data Analyst: Analyze and visualize data to extract meaningful insights. * Business Intelligence Analyst: Transform raw data into actionable business insights. * Data Scientist: Apply data manipulation and modeling techniques for predictions. * Reporting Specialist: Create engaging and informative reports and dashboards. * Business Analyst: Use data-driven insights to inform decision-making processes. COURSE CURRICULUM PowerBI Formulas power pivot power query - 1 00:01:00 power pivot power query - 2 00:02:00 power pivot power query - 3 00:06:00 power pivot power query - 4 00:07:00 power pivot power query - 5 00:02:00 power pivot power query - 6 00:05:00 power pivot power query - 7 00:05:00 power pivot power query - 8 00:04:00 power pivot power query - 9 00:03:00 power pivot power query - 10 00:02:00 power pivot power query - 11 00:04:00 power pivot power query - 12 00:08:00 power pivot power query - 13 00:04:00 power pivot power query - 14 00:03:00 power pivot power query - 15 00:13:00 power pivot power query - 16 00:03:00 power pivot power query - 17 00:13:00 Assignment Assignment - PowerBI Formulas 00:00:00