Master your Data with Power Query

- Πληροφορική - Χρήστες - Πληροφορική - Επαγγελματίες IT

12 Δεκ 2022 08:15 23 Δεκ 2022 13:30
Ελληνικά
30 ώρες ( 5 μέρες )
Master your Data with Power Query

ΠΕΡΙΓΡΑΦΗ

Power Query is a data transformation and data preparation engine. Power Query comes with a graphical interface for getting data from sources and a Power Query Editor for applying transformations. Because the engine is available in many products and services, the destination where the data will be stored depends on where Power Query was used. Using Power Query, you can perform the extract, transform, and load (ETL) processing of data. Power Query is integrated in Power BI, Excel, Microsoft Dataverse and Microsoft Azure Data Storage. Business users spend up to 80 percent of their time on data preparation, which delays the work of analysis and decision-making. Several challenges contribute to this situation, and Power Query helps address many of them.

ΣΚΟΠΟΣ ΣΕΜΙΝΑΡΙΟΥ

Upon completion of this course the participants will :

  • Describe Multi-Query Architecture
  • Enlist and Describe Types and Errors
  • Describe what Relational Data Sources are
  • Move Queries Between Excel & Power BI
  • Importing from Flat Files and From Excel
  • Apply Simple Transformation Techniques
  • Append, combine and merge Data
  • Extract Data from Web Based Data Sources
  • Reshaping Tabular Data
  • Use Conditional Logic
  • Write Queries with the M Language
  • Use Parameters and Custom Functions
  • Apply Date and Time Techniques
  • Automate Refresh
  • Adopt the best practices in extracting and transforming data with Power Query

ΣΕ ΠΟΙΟΥΣ ΑΠΕΥΘΥΝΕΤΑΙ

The audience for this course is data professionals and business intelligence professionals who want to learn how to extract, transform and load data using Power Query to perform data analysis with various software (Excel, Power BI Desktop). This course is also targeted toward those individuals who develop reports that visualize data from the data platform technologies that exist on both in the cloud and on-premises.

ΠΕΡΙΣΣΟΤΕΡΕΣ ΠΛΗΡΟΦΟΡΙΕΣ

Topics

Unit 1 - Power Query Fundamentals

  • Default Settings
  • Extracting Data: The 4 steps
  • Transforming Data

 

Unit 2 - Query Management

  • Using a Multi-Query Architecture
  • Referencing Queries
  • Choosing Query Load Destinations
  • Keeping Queries Organized
  • Splitting an Existing Query


Unit 3 - Data Types and Errors

  • Data Type vs Formats
  • Common Error Types
  • Query Error Auditing


Unit 4 - Moving Queries Between Excel & Power BI

  • Copying Queries Between Solutions
  • Importing Excel Queries to Power BI


Unit 5 - Importing from Flat Files

  • Understanding How Systems Import Data
  • Importing Delimited Files
  • Importing Non-Delimited Text Files
  • Basic Cleaning and Transformation

 

Unit 6 - Importing Data from Excel

  • Data Within the Active Workbook
  • Data From Other Workbooks


Unit 7 - Simple Transformation Techniques

  • Un-Pivoting the Curse of Pivoted Data
  • Pivoting Data
  • Splitting Columns
  • Filtering and Sorting
  • Grouping Data


Unit 8 - Appending Data

  • Basic Append Operations
  • Combining Queries with Differing Headers
  • Appending Tables & Ranges in the Current File

 

Unit 9 - Combining Files – A Case Study

  • Sample Case Background
  • Process Overview
  • Step – by step Implementation


Unit 10 - Merging Data

  • Merging Basics
  • Join Types
  • Cartesian Products (Cross Joins)
  • Approximate Match Joins
  • Fuzzy Matching


Unit 11 - Web Based Data Sources

  • Connecting to Web-Hosted Data Files
  • Connecting to HTML Web Pages
  • Connecting to Pages Without Tables
  • Caveats and Frustrations with the Web Experience


Unit 12 - Relational Data Sources

  • Connecting to Databases
  • Query Folding
  • Data Privacy Levels
  • Optimization


Unit 13 - Reshaping Tabular Data

  • Complex Pivoting Patterns
  • Complex Unpivoting Patterns
  • Advanced Grouping Techniques


Unit 14 - Conditional Logic in Power Query

  • Conditional Logic Basics
  • Creating Manual IF() Tests
  • Replicating Excel’s IFERROR()
  • Function
  • Working with Multiple Conditions
  • Compare Against Next/Previous Row
  • Columns From Example

 

Unit 15 - Power Query Values

  • Types of Values in Power Query
  • Tables
  • Lists
  • Records
  • Values
  • Binaries
  • Errors
  • Functions
  • Keywords in Power Query


Unit 16 - Understanding the M Language

  • M Query Structure
  • Understanding Query Evaluation
  • Iterators (Row by Row Evaluation)
  • Other techniques


Unit 17 - Parameters and Custom Functions

  • Building a Custom Function Using Parameters
  • Building a Custom Function Manually
  • Dynamic Parameter Tables
  • Implications of Parameter Tables


Unit 18 - Date and Time Techniques

  • Generating Calendar Boundaries
  • Calendars with Consecutive Dates
  • Filling Specific Date/Time Ranges
  • Allocations Based on Date Tables


Unit 19 - Query Optimization

  • Optimizing Power Query Settings
  • Leveraging Buffer Functions
  • Reducing Development Lag


Unit 20 - Automating Refresh

  • Options for Automating Refresh in
  • Excel
  • Automating Query Refresh with VBA in Excel
  • Scheduling Refresh in Power BI


Prerequisites

Very Good knowledge of Microsoft Excel.


Methodology

The course is 100% practical. The topics are delivered with short presentations by the instructor followed by a  step-by-step demonstration by the instructor and repetition by the students, examples and discussions on how a feature may be used with real life examples and practice through written exercises.

Πληροφορίες Εκπαιδευτή
Μαρία Κναή - Managing Director at EDITC & ΚΕΜ
Αναλυτικό Κόστος Σεμιναρίου

Για Δικαιούχους ΑνΑΔ

  • € 890.00
  • € 510.00
  • € 169.10
  • € 380.00

Για μη-Δικαιούχους ΑνΑΔ

  • € 890.00
  • € 0.00
  • € 169.10
  • € 890.00
ΠΡΟΓΡΑΜΜΑ ΣΕΜΙΝΑΡΙΟΥ

Δευτέρα - 12 Δεκ 2022

Ώρα

08:15 - 16:00

ΕΚΠΑΙΔΕΥΤΗΣ:

Μαρία Κναή

Τοποθεσία:

OnLine Virtual Classroom

Δευτέρα - 19 Δεκ 2022

Ώρα

08:15 - 16:00

ΕΚΠΑΙΔΕΥΤΗΣ:

Μαρία Κναή

Τοποθεσία:

OnLine Virtual Classroom

Τετάρτη - 21 Δεκ 2022

Ώρα

08:15 - 14:45

ΕΚΠΑΙΔΕΥΤΗΣ:

Μαρία Κναή

Τοποθεσία:

OnLine Virtual Classroom

Πέμπτη - 22 Δεκ 2022

Ώρα

08:15 - 13:30

ΕΚΠΑΙΔΕΥΤΗΣ:

Μαρία Κναή

Τοποθεσία:

OnLine Virtual Classroom

Παρασκευή - 23 Δεκ 2022

Ώρα

08:15 - 13:30

ΕΚΠΑΙΔΕΥΤΗΣ:

Μαρία Κναή

Τοποθεσία:

OnLine Virtual Classroom

Εκδήλωση Ενδιαφέροντος : Master your Data with Power Query

Σεμινάρια στην ίδια κατηγορία από τον ίδιο διοργανωτή
AutoCAD: Beginner To Professional...

AutoCAD is an important software tool for anyone working in engineering, architecture and manufacturing. The course covers the development of computer-aided des...

17/04/2024 15:00

35 ώρες (10 μέρες)

Αγγλικά

Επιχορηγημένο-ΑνΑΔ

Live Online

Introduction to Adobe Photoshop...

Το Adobe Photoshop είναι ένα λογισμικό που χρησιμοποιείται εκτενώς για την επεξεργασία εικόνας, γραφιστική και ψηφιακή τέχνη. Το Photoshop είναι το σημείο αναφο...

10/05/2024 08:15

14 ώρες (2 μέρες)

Αγγλικά

Επιχορηγημένο-ΑνΑΔ

Editc (Λευκωσία)

BIM Revit for MEP Systems (Mechanical, Electrical, Plumbing)...

BIM (Building Information Modeling) είναι μια διαδικασία που υποστηρίζεται από διάφορα εργαλεία και τεχνολογίες (Hardware & Software) που περιλαμβάνουν τη δημιο...

13/05/2024 14:30

40 ώρες (10 μέρες)

Ελληνικά

Επιχορηγημένο-ΑνΑΔ

Editc (Λευκωσία)

Using Canva for Social Media Visuals, Videos and Infographics for Busi...

In this course participants will learn how to use Canva. Canva is an online graphic tool that is used to create designs for various categories. This course star...

15/05/2024 08:15

14 ώρες (2 μέρες)

Ελληνικά

Επιχορηγημένο-ΑνΑΔ

Live Online

Lumion Landscaping...

Lumion is an essential tool for everyone who wants to visualize their building designs. It produces impressive, high quality videos very quickly and is extremel...

17/05/2024 08:30

21 ώρες (3 μέρες)

Ελληνικά

Επιχορηγημένο-ΑνΑΔ

Live Online

Visual Communication & Graphic Design Techniques in Marketing/ Adverti...

Στο σεμινάριο Visual Communication διδάσκονται τα προγράμματα Photoshop, Illustrator, InDesign παράλληλα με τις πρακτικές τεχνικές της σύγχρονης γραφιστικής. ...

22/05/2024 08:15

40 ώρες (8 μέρες)

Ελληνικά

Επιχορηγημένο-ΑνΑΔ

Live Online