I CANT BELIEVE ITS NOT SQL An Introduction

I CAN’T BELIEVE IT’S NOT SQL An Introduction to Power Query M

Daniel Villarreal Jr. ■ Junior Data Analyst @ Iron. Edge. Group ■ Currently working in the BI Consulting Department Specializing in Power BI Solutions ■ Linked. In: https: //www. linkedin. com/in/daniel-villarreal-jr/ ■ Background in Data Modeling, Reporting, Analytics, Database Management, and Information Systems

What is Power Query M? ■ Formula Language optimized for building queries. ■ Functional, case sensitive. ■ Like VBA, you define steps ■ Easy to Learn, Harder to Master ■ M essentially defines your queries

What is the Advanced Editor? ■ The Advanced Editor shows the Power Query M code. ■ This code represents the “Applied Steps” ■ You can “Love it or Leave It” ■ I recommend learning to love it…

What is going on inside the Advanced Editor? Tip: The Output of every line is a table itself.

Common Statements in Power Query ■ The output of every* line of M is a list/table. ■ Common Sightings in the Advanced Editor: – *Table. From. List – Turning a list into a table – Table. Transform. Columns – Transforming Columns – Table. Transform. Column. Types – Transforming Column. Types – Table. Rename. Columns – Renaming Columns ■ Let’s break down the parts of a line of Power Query M.

Anatomy of a Power Query Statement Parameter s #"Renamed Columns" = Table. Rename. Columns(#"Changed Type", {{"area", "Area (km^2)"}}) Step Name Functio n Target Table a. k. a. The Previous Step Column Target New Column Name ■ Translation: Take the table defined in #”Changed Type” and Rename the Column Area to Area (km^2)

Things to keep in mind about Power Query M ■ You can do line breaks ■ Commas mark the end of a statement ■ You can declare local variables ■ Implement common patterns like if else statement ■ M is case-sensitive ■ Enabling Intellisense is very helpful ■ There is no undo!

Anatomy of a Power Query Statement Parameter s #"Renamed Columns" = Table. Rename. Columns(#"Changed Type", {{"area", "Area (km^2)"}}) Step Name Functio n Target Table a. k. a. The Previous Step Column Target New Column Name ■ Translation: Take the table defined in #”Changed Type” and Rename the Column Area to Area (km^2) ■ This may look familiar…

When you use the GUI to rename a column you are generating M ■ The formula bar goes from looking like this: ■ To looking like this: ■ This is because you’ve created a new step in your Query, however, this could be your lead into writing your own code by modifying it.

Writing Power Query with the Assistance of the GUI ■ M code is generated automatically when interacting with certain operations in the Query Editor GUI. ■ You can edit the generated Power Query code to accomplish more without waiting for the GUI to perform an operation that takes a more than few clicks per column. ■ All operations that transform, rearrange, add or subtract data in the table of your query, will generate Power Query M code. ■ Basically everything except the ‘R’ in CRUD generates Power Query M.

Example: ■ #"Removed Columns" = Table. Remove. Columns(#"Renamed Columns", {"top. Level. Domain"})

Using Power Query to avoid redundant work ■ Power Query M code can be copied and pasted in a blank query in any report. ■ This is especially helpful when you have to query the same data source several times, but transform it in different ways.

Cleaning up your Power Query steps by combining similar statements. ■ It is common to do operations in pairs. ■ This leads a single action in a step multiple times. ■ Can be avoided by grouping steps. ■ You can combine all similar steps in the Advanced Editor. ■ Combining similar steps is a best practice for optimizing queries.

Q&A