Power Query/M - Nested Structures

An outline of this training course  

Want to become a better data analyst?  

Whether you are using Power Query in Excel for report automation, Power BI Desktop or Dataflows to design and shape building blocks (tables) for your data model, understanding what structured values are and how to interact with them will give you M(agical) powers. 

Just as virtual tables are the key to unlocking the real power of DAX, learning how to manipulate nested structures will unlock your ability to perform nearly any complex transformation within Power Query.   

What are needed to take this course  

To reap maximum benefit from this course, you should have a working knowledge of the Power Query user interface and have completed Melissa’s eDNA course on Power Query/M Fundamentals. This course builds on the knowledge of basic Power Query structures (values, list, records, tables, and functions) covered extensively in the fundamental course.  

That course dealt with each structure separately.  This course will teach you what can be achieved by combining these types in almost every form (e.g., Lists of tables, tables of lists, records containing lists, functions transforming lists within tables, etc.) 

Who is the course for  

This course is for the intermediate to advanced Data Analyst who wants to make maximal use of Power Query to push transformations “upstream” (e.g., closer to the data source, per Roche’s Maxim), thereby increasing report transparency, maintainability, and speed.  Moving transformations and calculations not dynamic within the context of a reporting session will also dramatically simplify your DAX. 

Details of what you will learn during this course 

• Quick intro to Lists, Records and Tables 
• Explore expand and extract options 
• Access structured values 
• Use Row Index- and or Key Match Lookup 
• Selection and Projection 
• Expanded vs Nested transformations 
• and more 

What you get with the course  

• A three-hour self-paced video training
• Resource pack that includes the PBI source files

Program Level  

Intermediate 

Field(s) of Study

Computer Software & App

Instruction Delivery Method

QAS Self-study

***This course was published in January 2023

Enterprise DNA is registered with the National Association of State Boards of Accountancy (NASBA) as a sponsor of continuing professional education on the National Registry of CPE Sponsors. State boards of accountancy have final authority on the acceptance of individual courses for CPE credit. Complaints regarding registered sponsors may be submitted to the National Registry of CPE Sponsors through its website: www.nasbaregistry.org