Dynamic Power Query: unbreakable queries
Shifting headers, renamed columns, multiple files: build M queries with no hard-coding, driven from Excel.
Access: an e-mail address is enough, you receive a sign-in code. Free modules need no bank card.
What you will be able to do
- Locate and merge header rows dynamically, up to an automatic detection function
- Rename with a mapping table and combine files with inconsistent headers
- Drive the columns kept, the groupings and the source files from Excel
- Combine queries automatically with #shared and extract a tagged block
- Compare several sources, pivot by groups of columns and test numeric columns
- Build a data infrastructure that withstands changes in structure
Who it is for
Analysts and controllers who consolidate recurring files whose structure varies.
Prerequisites
The “M language: lists, records and functions” course (lists, records, each, advanced editor).
Included
- Video with chapters
- PDF slides
- Marked quiz
- Verifiable certificate
Programme
Intermediate
- 01Finding the header row dynamically
- 02Merging two header rows into one
- 04Renaming columns with a mapping table
- 06Choosing the columns to keep from Excel
- 12Inserting a row into a table without a merge
Advanced
- 03A function that detects the headers automatically
- 05Combining files with inconsistent headers
- 07Grouping and summing columns chosen in Excel
- 08Querying scattered files via a table of paths
- 09Combining queries automatically with #shared
- 10Full automation: driven path, headers and summary
- 11Extracting a tagged block from a data file
- 13Comparing data from several sources with a pivot
- 14Pivoting by groups of columns
- 15Testing a row's numeric columns dynamically
- 16Cleaning up a messy table from start to finish
- 17Masterclass: an indestructible data infrastructure
Training a team? Access for several employees, on quotation. admin@axent-consulting.com