17 Power Query courses in Bradford delivered Live Online

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

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.

Course Objectives At the end of this course you will be able to: Work with advanced lookup and reference functions Create macros to automate common tasks Use advanced techniques to analyse data Create PivotTables and PivotCharts Work with data validation tools Import and export data ' Customer Feedback Best Training Ever! Just finished a bespoke 1-1 training course in Excel Advanced, Macros & VBA. Pedro is an excellent trainer, imparting his skills and knowledge in the best way - appropriately to audience skills, knowledge and ability. Pedro is always approachable, encouraging and supportive, giving delegates the optimum learning environment. I would not hesitate to recommend Pedro as a trainer, whatever your level of ability. Amanda Morris - Treasury & Systems Accountant at Reall - Real Equity for All The course was very interesting and engaging and will definitely be put to use. The trainer was very helpful and charismatic. Marving Lopez - AM Best Europe Pedro was excellent. Very knowledgeable, clear and great rapport with class. Darren Barkey - Direct Wines Great content and learnt a lot. Really enjoyable :) Kristie-Lee Ryan - Stirling Ackroyd 1 year email support service Take a closer look at the consistent excellent feedback visiting our site ms-officetraining co uk With more than 20 years experience, we deliver courses on all levels of the Desktop version of Microsoft Office and Office 365; ranging from Beginner, Intermediate, Advanced to the VBA level. Our trainers are Microsoft certified professionals with a proven track record with several years experience in delivering classrom, one to one, tailored and bespoke courses. Tailored In Company training: You can choose to run the course exactly as they are outlined by us or we can customise it so that it meets your specific needs. A tailored or bespoke course will follow the standard outline but may be adapted to your specific organisational needs. Please visit our site (ms-officetraining co uk) to get a feel of the excellent feedback our courses have had and look at other courses you might be interested in. Advanced Functions COUNT, COUNTA, COUNTIF, COUNTBLANK, SUMIF, SUBTOTAL IF, OR, AND, IFERROR VLOOKUP to lookup Exact Values and Approximate values MATCH, INDIRECT, ROW/COLUMN, INDEX, OFFSET Analysing Data PMT function to calculate a monthly payment of a loan What If Analysis using Goal Seek Use scenarios to consider many different variables Using Solver Add-in to optimize variables PivotTables Create a PivotTable to analyse worksheet data Add or remove fields in a PivotTable Change the data source for a PivotTable Working with external data sources Using different Statistical Functions Calculate a Running Total, % of Grant Total, Difference From… Using the Slicer and Timeline to filter the PivotTable Using Filters in a PivotTable Group by Date, Auto Group by ranges of values, Custom Group Create a PivotChart The GETPIVOTDATA function Formula auditing Precedents and dependents Error checking Watch a formula and its result by using the Watch Window Evaluate a nested formula one step at a time Data Validation Number Validation Data List Validation Message Prompts and Alerts Conditional Data Validation Data Validation Errors Consolidation Consolidate data by position Consolidate data by category Use a formula to consolidate data Outline (group) data in a worksheet Create an outline of rows Create an outline of columns Copy outlined data Hide or remove an outline Connect external data to your workbook Using the Power Query Editor to import data From Text and CSV, From Access, From Web, From another Workbook Using the Power Query Editor to; Transform your data by Selecting which fields to import, by Splitting Columns, Changing Data Type and more. Deleting Applied Steps Refreshing your data Working with Form Controls; Buttons, Option Buttons and Scroll Bars Introduction to Macros Change Macro Security settings How to Record a macro Some Macro Examples Delete a macro Edit the macro to view the VBA code Copy part of a macro to create another macro Assign a macro to a button Who is this course for? Who is this course for? This course is intended to end users who want to develop their skills so they can use advanced techniques to analyse extensive and complex datasets in Excel and to automate simple tasks with the use of Macros. Requirements Requirements Preferably, delegates should have attended the Excel Intermediate course. Career path Career path Excel know-how can instantly increase your job prospects as well as your salary. 80 percent of job openings require spreadsheet and word-processing software skills Certificates Certificates Certificate of completion Digital certificate - Included

Course Objectives At the end of this course you will be able to: Work with advanced lookup and reference functions Create macros to automate common tasks Use advanced techniques to analyse data Create PivotTables and PivotCharts Work with data validation tools Import and export data ' Customer Feedback Best Training Ever! Just finished a bespoke 1-1 training course in Excel Advanced, Macros & VBA. Pedro is an excellent trainer, imparting his skills and knowledge in the best way - appropriately to audience skills, knowledge and ability. Pedro is always approachable, encouraging and supportive, giving delegates the optimum learning environment. I would not hesitate to recommend Pedro as a trainer, whatever your level of ability. Amanda Morris - Treasury & Systems Accountant at Reall - Real Equity for All The course was very interesting and engaging and will definitely be put to use. The trainer was very helpful and charismatic. Marving Lopez - AM Best Europe Pedro was excellent. Very knowledgeable, clear and great rapport with class. Darren Barkey - Direct Wines Great content and learnt a lot. Really enjoyable :) Kristie-Lee Ryan - Stirling Ackroyd 1 year email support service Take a closer look at the consistent excellent feedback from our growing corporate clients visiting our site ms-officetraining co uk With more than 20 years experience, we deliver courses on all levels of the Desktop version of Microsoft Office and Office 365; ranging from Beginner, Intermediate, Advanced to the VBA level. Our trainers are Microsoft certified professionals with a proven track record with several years experience in delivering public, one to one, tailored and bespoke courses. Our competitive rates start from £550.00 per day of training Tailored training courses: You can choose to run the course exactly as they are outlined by us or we can customise it so that it meets your specific needs. A tailored or bespoke course will follow the standard outline but may be adapted to your specific organisational needs. Please visit our site (ms-officetraining co uk) to get a feel of the excellent feedback our courses have had and look at other courses you might be interested in. Advanced Functions COUNT, COUNTA, COUNTIF, COUNTBLANK, SUMIF, SUBTOTAL IF, OR, AND, IFERROR VLOOKUP to lookup Exact Values and Approximate values MATCH, INDIRECT, ROW/COLUMN, INDEX, OFFSET Analysing Data PMT function to calculate a monthly payment of a loan What If Analysis using Goal Seek Use scenarios to consider many different variables Using Solver Add-in to optimize variables PivotTables Create a PivotTable to analyse worksheet data Add or remove fields in a PivotTable Change the data source for a PivotTable Working with external data sources Using different Statistical Functions Calculate a Running Total, % of Grant Total, Difference From… Using the Slicer and Timeline to filter the PivotTable Using Filters in a PivotTable Group by Date, Auto Group by ranges of values, Custom Group Create a PivotChart The GETPIVOTDATA function Formula auditing Precedents and dependents Error checking Watch a formula and its result by using the Watch Window Evaluate a nested formula one step at a time Data Validation Number Validation Data List Validation Message Prompts and Alerts Conditional Data Validation Data Validation Errors Consolidation Consolidate data by position Consolidate data by category Use a formula to consolidate data Outline (group) data in a worksheet Create an outline of rows Create an outline of columns Copy outlined data Hide or remove an outline Connect external data to your workbook Using the Power Query Editor to import data From Text and CSV, From Access, From Web, From another Workbook Using the Power Query Editor to; Transform your data by Selecting which fields to import, by Splitting Columns, Changing Data Type and more. Deleting Applied Steps Refreshing your data Working with Form Controls; Buttons, Option Buttons and Scroll Bars Introduction to Macros Change Macro Security settings How to Record a macro Some Macro Examples Delete a macro Edit the macro to view the VBA code Copy part of a macro to create another macro Assign a macro to a button Who is this course for? Who is this course for? This course is intended to end users who want to develop their skills so they can use advanced techniques to analyse extensive and complex datasets in Excel and to automate simple tasks with the use of Macros. Requirements Requirements Preferably, delegates should have attended the Excel Intermediate course. Career path Career path Excel know-how can instantly increase your job prospects as well as your salary. 80 percent of job openings require spreadsheet and word-processing software skills Certificates Certificates Certificate of completion Digital certificate - Included

OVERVIEW Prerequisites—DIAD training or equivalent working experience This one-day course will cover using Power BI Desktop to import and shape data from a variety of sources. It will also walk through Power BI capabilities you can use to enhance the data model for your business users. The course covers key aspects of how to create a great data model to meet your business needs, various features in Power BI used to enhance data models so you can build great reports, and an introduction to DAX to create calculations. After completing this training, the attendees should be able to import data from a variety of data sources into Power BI, shape the data, create the data model, and write DAX functions to the Power BI model. COURSE BENEFITS: Understand the Power BI Desktop data model, its components and most effective schemas Describe concepts of calculated columns and measures Create queries using M Create calculations with DAX Understand the use of functions Create and optimize a data model Understand the consequences of data model design decisions WHO IS THE COURSE FOR? Power BI report developers who wish to improve the structure of their data models Power BI report developers who wish to use advanced features like parameters and M coding in Power Query Power BI practitioners who wish to optimise their models more effectively Attendees wishing to prepare thoroughly for the DAX In A Day course COURSE OUTLINE Module 1 Getting And Shaping The Data Understand what is meant by data model in the context of Power BI Understand the consequences of data model design decisions Understand consequences of Power BI’s data type handling Understand data connection options Module 2 Basic Data Modelling Understand basic data modelling Understand basic data model types Explore dimension tables and fact tables Explore data connections Module 3 Getting Started With M (Power Query Language) Get introduced to M Understand key components and syntax Module 4 Understanding Logic Operators Understand Transformations Understand Join operation Module 5 Introduction To DAX Get introduced to DAX and how can it be used Understand working with parameters and DAX (lab combining the previous module) Module 6 Working With Functions - DAX CALCULATE And More Understand working with functions Understand the basics of the CALCULATE formula Module 7 Modelling With Power BI & DAX Best Practice Gain familiarity with basic data modelling for business scenarios Learn some best practices for working with Power BI

OVERVIEW This official Microsoft Power BI training course will teach you how to connect to data from many sources, clean and transform it using Power Query, create a data model consisting of multiple tables connected with relationships and build visualisations and reports to show the patterns in the data. The course will explore formulas created using the DAX language, including the use of advanced date intelligence calculations. Additional visualisation features including interactivity between the elements of a report page are covered as well as parameters and row-level security, which allows a report to be tailored according to who is viewing it. The course will also show how to publish reports and dashboards to a workspace on the Power BI Service. COURSE BENEFITS: Learn how to clean, transform, and load data from many sources Use database queries in Power Query to combine tables using append and merge Create and manage a data model in Power BI consisting of multiple tables connected with relationships Build Measures and other calculations in the DAX language to plot in reports Manage advanced time calculations using date tables Optimise report calculations using the Performance Analyzer Manage and share report assets to the Power BI Service Prepare for the official Microsoft PL-300 exam using Microsoft Official Courseware WHO IS THE COURSE FOR? Data Analysts with little or no experience of Power BI who wish to upgrade their knowledge to include Business Intelligence Management Consultants who need to conduct rapid analysis of their clients’ data to answer specific business questions Analysts who need to upgrade their organisation from a simple Excel or SQL-based management reporting system to a dynamic BI system Data Analysts who wish to develop organisation-wide reporting in the form of web reports or phone apps Marketers in data-intensive organisations who wish to build visually appealing, dynamic charts for their stakeholders to use COURSE OUTLINE Module 1 Getting Started With Microsoft Data Analytics Data analytics and Microsoft Getting Started with Power BI Module 2 Get Data In Power BI Get data from various data sources Optimize performance Resolve data errors Lab: Prepare Data in Power BI Desktop Module 3 Clean, Transform And Load Data In Power BI Data shaping Data profiling Enhance the data structure Lab: Load Data in Power BI Desktop Module 4 Design A Data Model In Power BI Introduction to data modelling Working with Tables Dimensions and Hierarchies Lab: Model Data in Power BI Desktop Module 5 Create Model Calculations Using DAX In Power BI Introduction to DAX Real-time Dashboards Advanced DAX Lab 1: Create DAX Calculations in Power BI Desktop, Part 1 Lab 2: Create DAX Calculations in Power BI Desktop, Part 2 Module 6 Optimize Model Performance Optimize the data model for performance Optimize DirectQuery models Module 7 Create Reports Design a Report Enhance the Report Lab 1: Design a Report in Power BI Desktop, Part 1 Lab 2: Design a Report in Power BI Desktop, Part 2 Module 8 Create Dashboards Create a Dashboard Real-time Dashboards Enhance a Dashboard Lab: Create a Power BI Dashboard Module 9 Perform Advanced Analytics Advanced analytics Data Insights through AI Visuals Lab: Perform Data Analysis in Power BI Desktop Module 10 Create And Manage Workspaces Creating Workspaces Sharing and managing assets Module 11 Manage Datasets In Power BI Parameters Datasets Module 12 Row-Level Security Security in Power BI Lab: Enforce Row-Level Security

There is a lot to learn in Power BI, this course takes a comprehensive look at the fundamentals of analysing data and includes a balanced look at the four main components that make up Power BI Desktop: Report view, Data view, Model view, and the Power Query Editor. It also demonstrates how to utilise the online Power BI service. It looks at authoring tools that enable you to connect to and transform data from a variety of sources, allowing you to produce detailed reports through a range of visualisations, in an interactive and dynamic way. It also includes a detailed look at formulas by writing both M functions in Power Query, and DAX functions in Desktop view. This knowledge will allow you to take your reports to the next level. The aim of this course is to provide a complete introduction to understanding the Power BI analysis process, by working hands-on with examples that will equip you with the necessary skills to start applying your learning straight away. 1 Getting Started The Power BI ecosystem Opening Power BI Desktop Power BI's four views Introduction to Dashboards 2 Importing Files Importing data sources Importing an Excel file Importing a CSV file Importing a database Connect to an SQL Server Database Import vs. Direct Query Importing from the web Importing a folder of files Managing file connections 3 Shape Data in the Query Editor The process of shaping data Managing data types Keeping and removing rows Add a custom column Appending tables together Hiding queries in reports Fixing error issues Basic maths operations 4 The Data Model Table relationships Relationship properties 5 Merge Queries Table join kinds Merging tables 6 Inserting Dashboard Visuals Things to keep in mind Inserting maps Formatting Maps Inserting charts Formatting Charts Inserting a tree map Inserting a table, matrix, and card Controlling number formats About report themes Highlighting key points Filter reports with slicers Sync slicers across dashboards Custom web visuals 7 Publish and share Reports Publishing to Power BI service Editing online reports Pinning visuals to a dashboard What is Q&A? Sharing dashboards Exporting reports to PowerPoint Exporting reports as PDF files 8 The Power Query Editor Fill data up and down Split column by delimiter Add a conditional column More custom columns Merging columns 9 The M Functions Inserting text functions Insert an IF function Create a query group 10 Pivoting Tables Pivot a table Pivot and append tables Pivot but don't aggregate Unpivot tables Append mismatched headers 11 Data Modelling Expanded Understanding relationships Mark a date table 12 DAX New Columns New columns and measures New column calculations Insert a SWITCH function 13 Introduction to DAX Measures Common measure functions Insert a SUM function Insert a COUNTROWS function Insert a DISTINCTCOUNT function Insert a DIVIDE function DAX rules 14 The CALCULATE Measure The syntax of CALCULATE Insert a CALCULATE function Control field summarisation Things of note 15 The SUMX measure X iterator functions Anatomy of SUMX Insert a SUMX function When to use X functions 16 Time Intelligence Measures Importance of a calendar table Insert a TOTALYTD function Change financial year end date Comparing historical data Insert a DATEADD function 17 Hierarchies and Groups Mine data using hierarchies Compare data in groups

SQL Queries Course, 1-Day. Basic SQL syntax. This course is good for any database, covering standard SQL. Most attendees are in-work IT Professional. Private individuals are also very welcome.

Duration 3 Days 18 CPD hours This course is intended for This course is primarily designed for students who want to gain the skills necessary to use VBA to automate tasks in Excel such as collecting data from external sources, cleaning, and manipulating data. The target student may also want to learn how to create custom worksheet functions to streamline worksheet formulas and make complex worksheets easier to support, maintain, and understand. Overview In this course, you will develop and deploy VBA modules to solve business problems. You will: Identify general components of VBA and their appropriate use in solving business solutions. Record VBA macros to automate repetitive tasks. Use reference tools built into Excel to get help on VBA programming language and objects used in the Excel VBA environment. Write VBA code to create a custom worksheet function. Eliminate, avoid, or handle errors in VBA code, and optimize its performance. Control how and when macros run. Develop UserForm objects to create custom dialog boxes and windows. Use VBA to read and write data from local files and cloud services. Use VBA to clean and transform data. Run programs and commands outside of Excel and share VBA projects with other users VBA (Visual Basic for Applications) enables you to enhance and extend the capabilities of Microsoft© Excel© and other applications in the Microsoft© Office application suite. You can use VBA to perform tasks that would be difficult or impossible to do using only worksheet functions, and you can automate a wide range of tasks involving the collection, processing, analysis, and visualization of data. This course will give you a good foundation for understanding, creating, and using VBA in your own Excel workbooks, show you how to work with data across different applications, and how to package the macros and functions you create so you can back them up, move them to other computers, and share them with other users Prerequisites To ensure your success in this course, you should be an experienced Excel user who is comfortable creating and working with Excel workbooks, including tasks such as entering worksheet formulas, using absolute and relative addressing, formatting cells, and creating pivot tables and charts. This level of skill could be acquired by taking the Microsoft Excel for Office 365? (Desktop or Online) courses, Parts 1, 2, and 3 1 - Using VBA to Solve Business Problems Topic A: Use Macros to Automate Tasks in Excel Topic B: Identify Components of Macro-Enabled Workbooks Topic C: Configure the Excel VBA Environment 2 - Automating Repetitive Tasks Topic A: Use the Macro Recorder to Create a VBA Macro Topic B: Record a Macro with Relative Addressing Topic C: Delete Macros and Modules Topic D: Identify Strategies for Using the Macro Recorder 3 - Getting Help on VBA Topic A: Use VBA Help Topic B: Use the Object Browser to Discover Objects You Can Use in VBA Topic C: Use the Immediate Window to Explore Object Properties and Methods 4 - Creating Custom Worksheet Functions Topic A: Create a Custom Function Topic B: Make Decisions in Code Topic C: Work with Variables Topic D: Perform Repetitive Tasks 5 - Improving Your VBA Code Topic A: Debug VBA Errors Topic B: Deal with Errors Topic C: Improve Macro Performance 6 - Controlling How and When Macros Run Topic A: Prompt the User for Information Topic B: Configure Macros to Run Automatically 7 - Developing Custom Forms Topic A: Display a Custom Dialog Box Topic B: Program Form Events 8 - Using VBA to Work with Files Topic A: Use VBA to Get File and Directory Structure Topic B: Use VBA to Read Text Files Topic C: Use VBA to Write Text Files 9 - Using VBA to Clean and Transform Data Topic A: Automate Power Query Topic B: Transform Data Using VBA and Workbook Functions Topic C: Use Regular Expressions Topic D: Manage Errors in Data 10 - Extending the Programming Environment Beyond the Workbook Topic A: Run Other Programs and Commands Topic B: Share Your VBA Projects

This course starts with the basics then moves seamlessly to an intermediate level. It includes a comprehensive yet balanced look at the four main components that make up Power BI Desktop: Report view, Data view, Model view, and the Power Query Editor. It also demonstrates how to use the online Power BI service. It looks at authoring tools that enables you to connect to and transform data from a variety of sources, allowing you to produce dynamic reports using a library of visualisations. Once you have those reports, the course looks at the seamless process of sharing those with your colleagues by publishing to the online Power BI service. The aim of this course is to provide a strong understanding of the Power BI analysis process, by working with real-world examples that will equip you with the necessary skills to start applying your knowledge straight away. 1 Getting started The Power BI process Launching Power BI Desktop The four views of Power BI Dashboard visuals 2 Connecting to files Connect to data sources Connect to an Excel file Connect to a CSV file Connect to a database Import vs. DirectQuery Connect to a web source Create a data table 3 Transforming data The process of cleaning data Column data types Remove rows with filters Add a custom column Append data to a table Fix error issues Basic maths operations 4 Build a data model Table relationships Manage table relationships 5 Merge queries Table join kinds Merging tables 6 Create report visualisations Creating map visuals Formatting maps Creating chart visuals Formatting chart Tables, matrixes, and cards Control formatting with themes Filter reports with slicers Reports for mobile devices Custom online visuals Export report data to Excel 7 The power query editor Fill data up and down Split columns by delimiter Add conditional columns Merging columns 8 The M formula Creating M functions Create an IF function Create a query group 9 Pivot and unpivot tables Pivot tables in the query editor Pivot and append tables Pivot but don't summarise Unpivot tables Append mismatched headers 10 Data modelling revisited Data model relationships Mark a calendar as a date table 11 Introduction to calculated columns New columns vs. measures Creating a new column calculation The SWITCH function 12 Introduction to DAX measures Common measure categories The SUM measure Adding measures to visuals COUNTROWS and DISINCTCOUNT functions DAX rules 13 The CALCULATE measure The syntax of CALCULATE Things of note about CALCULATE 14 The SUMX measure The SUMX measure X iterator functions Anatomy of SUMX 15 Introduction to time intelligence Importance of a calendar table A special lookup table The TOTALYTD measure Change year end in TOTALYTD 16 Hierarchy, groups and formatting Create a hierarchy to drill data Compare data in groups Add conditional formatting 17 Share reports on the web Publish to the BI online service Get quick insights Upload reports from BI service Exporting report data What is Q&A? Sharing your reports 18 Apply your learning Post training recap lesson