hi everyone welcome to Bsmart2gether. In the last couple of videos i shared a couple of ideas on how
to build dynamic queries some users told me those ideas were a little advanced for them in this
video i want to step back and provide an overview of excel power query what is m code and why we
use it before we start please click subscribe and turn on the notification to avoid missing valuable
videos also feel free to drop a comment or any power query questions if you are new to power
query i highly recommend you watch the power query zero to one series which are designed to help new
users to learn from scratch in less than an hour so what is power query what is m code
in the power query video i explained in detail what power query can do in short power query is
a powerful data transformation to land as part of power bi and microsoft excel m code is the
coding language behind the scene mike jerving from excel is fun described power query equal to power
anim code equal to more power i learned a lot from his channel why do we use m code fun fancy
we hit the bottleneck due to the limitation of standard function
say you were so excited transforming and building up a monthly report for the sales department
you received an email from the sales department
what's wrong that's why we need to write m code for future proof queries
there are some basic terminologies i want to you know before we dive in a bit more anything within
the red line is known as table in a table you have columns and rows in power query a column is known
as list and a row is known as record you will get a list if you create a blank query and type sales
open square parenthesis or the number and close square parenthesis in the formula sales is a
table where order number is a column select a row and you will get to preview your record
those steps in the applied steps field are known as let expression
let us dive into each of the transformation steps when we apply the data type function you get the
change type step you see the curly parenthesis in the formula above it represents a list the
highlighted list contain multiple lists the m code being generated as a static code for the change of
data type for example if the column order number delete or changed of name then you will get an
error when you try to refresh the query when you unpivot columns you select the columns and then
unpivot them those selected columns appear as a list as highlighted in the formula again these
codes are static and any change to the column or column will result in error during refresh
you select particular columns and then use the replace value function to find the keyword and
replace it with a specific value the static part in this case is the selected columns
any changes to the column name will result in an error during the refresh the concept of the
rename column step is the same as the change type s step with the pivot column function
power query generates the m code for the selected column as a list which is different to previous
examples two static codes are generated in this case a column as a list and a column name changing
the column name or deleting the column will result in an error during the refresh due to static code
i showed you a few examples today about the limitation of m code generated from
the standard functions will work well if only there are no changes to the source data
which is very unlikely in today's world however slightly modifying the generated m code can
help you polish up your queries and make them unbreakable thank you for watching and i hope
you find this video helpful please don't forget to click like if you like the video