Episode 197
| April 15, 2020
| 23 minutes
Power Query in Power BI
In 1990, the group Snap! release a song called “The Power”. It would take Microsoft more than 20 years, but they eventually came up with a series of ‘power’ tools they built into Excel. Those tools later grew into Power BI–which is kinda like Voltron if you are old enough to remember. In this episode we discuss one of the first data manipulation tools in the set: Power Query. Before you start manipulating all those fun visualizations, you might need to get your data in shape. If you have a data warehouse, you are probably in good shape; however, for the rest of us, there is Power Query. This episode takes us through an overview and discusses in what cases you might have to dive into the code, or M language, when the GUI doesn’t cut it.
Click here to sign up on Pluralsight (free in April 2020) and watch Eugene’s courses on M and Power BI
Books to help:
Power Query for Power BI and Excel by Christopher Webb
M Is for (Data) Monkey: A Guide to the M Language in Excel Power Query by Miguel Escobar & Ken Puls
Collect, Combine, and Transform Data Using Power Query in Excel and Power BI by Gil Raviv
Listen to Learn
00:38 Intro to the topic
02:41 Compañero Shout-Outs
03:33 What are we talking about again today?
06:02 80% is in the GUI, 20% is an M code nightmare
08:47 There are books out there to help, but they are aimed more at Excel folks
10:54 M is the data prep language, the actual code
11:53 How often does Eugene have to get into the code?
13:24 Kevin didn’t do all his jokes, but we still have tangents
14:51 Eugene names several books that can help you with Power Query & M
16:14 M code has limited side-effects
17:58 Power Query can also do lazy evaluation
19:07 Query folding is very similar to predicate push-down
19:56 Closing Thoughts
The official litmus test is [Power Query] was designed for people to get daily value out of the Excel formula bar, so it’s certainly targeted for them.
Meet the Hosts
Carlos Chacon
With more than 10 years of working with SQL Server, Carlos helps businesses ensure their SQL Server environments meet their users’ expectations. He can provide insights on performance, migrations, and disaster recovery. He is also active in the SQL Server community and regularly speaks at user group meetings and conferences. He helps support the free database monitoring tool found at databasehealth.com and provides training through SQL Trail events.
Eugene Meidinger
Eugene works as an independent BI consultant and Pluralsight author, specializing in Power BI and the Azure Data Platform. He has been working with data for over 8 years and speaks regularly at user groups and conferences. He also helps run the GroupBy online conference.
Kevin Feasel
Kevin is a Microsoft Data Platform MVP and proprietor of Catallaxy Services, LLC, where he specializes in T-SQL development, machine learning, and pulling rabbits out of hats on demand. He is the lead contributor to Curated SQL, president of the Triangle Area SQL Server Users Group, and author of the books PolyBase Revealed (Apress, 2020) and Finding Ghosts in Your Data: Anomaly Detection Techniques with Examples in Python (Apress, 2022). A resident of Durham, North Carolina, he can be found cycling the trails along the triangle whenever the weather's nice enough.
Want to Submit Some Feedback?
Did we miss something or not quite get it right? Want to be a guest or suggest a guest/topic for the podcast?