I gave this talk for the first time a
little over a year ago and I guess what
I think I'm trying to show is that you
know the better you get with very tool
you know the more you're going to be
able to take on these giant problems
that you face inside here so also this
area that we're about to look at you
know M programming there isn't a lot of
resources out there you know if you want
to learn decks you know first of all
there's the two Italian guys Margaret
Rosa Alberto Ferrari you read their
books you know everything about it and
there's tons of other resources on decks
you get to em and you're gonna find
there there's seven blog entries you
know by Chris Webb and that's pretty
much it but I learned so much from these
three guys right here you know so Matt
Mason works on the team in Microsoft
with Howard brewery and he's one of the
ones that's kind of driving things
forward and evangelizing there's a guy
named Chris Webb out of the UK you know
who is what I think of you is the
leading third-party expert in power
purring and working with them and then
another fellow from Down Under
Reza read okay so I just want to call
out to them nothing all right
okay can we got him in an EM suit okay
also everything I'm doing here you know
there is a github repository you know if
you go there you'll be able to download
these slides you'll be able to download
the samples inside here okay so we're
gonna cover first of all just the power
query matchup engine talking about you
know what that is and one of the things
that we're gonna look at is that when
you use power query you know quite often
we just stay within the user experience
that we click here and click there and
we kind of create these
very transforms you know but there is
also the ability you know to go in and
work directly with em code and so one of
the things I want to bring out you know
is that you know there are certain
advantages and certainly there's a lot
more that you can do if you work
directly with the EM code so we're gonna
look at them just as a new programming
language I know not all of you are
developers but it's got to start out
with you know this tool and if I go
ahead and I bring up power bi desktop so
how many people use power bi desktop
okay just kind of seen hoping everyone
in the room raises their hand here you
know let's say I want to do something
you know fairly straight ahead you know
so I bring up this new project now one
of the things I have here is this CSV
file so I'm going to use NFL teams in
this example and so one neat thing about
NFL teams is they all have a webpage
that has a roster you know so let's say
that I get inside here and I grab this
so if I just bring up a browser you know
and I go through this particular page
right here no we should see there is a
page and sooner or later there's going
to be an HTML table you know but what we
want to do is we don't care about the UI
we just want to go and scrape the data
from this HTML table inside there I tend
to like teams that are at the bottom it
doesn't matter you bring up it's gonna
be controversy okay oh Portland doesn't
like football I forgot okay should have
something I should have had soccer
examples inside here but let's say we go
to web yeah that's it now I go through
here and I enter this inside and so
there's this neat experience you know
where you're behind the scenes they're
doing all kinds of things like looking
at that particular page and determining
you know what are the HTML tables and
they're gonna bring up this navigator so
the one that I want is going to be the
first HTML table it's the one that's
named active and so we're gonna go ahead
and say edit inside here power bi
desktop also has this kind of
unfortunate bug to the query editor is
not there it
it just doesn't become the active window
you know so we need to go through but
now that we have this inside here you
know what are some of the things you
know that I might want to do you know so
I might want to go through and start you
know changing some of the columns inside
here you know here one of our main jobs
that we have when we create queries is
to clean the data and if you have data
that's supposed to be numeric and you
don't convert it and it's text-based
you know all of a sudden you can't sums
you can't average you know all you can
really do is count so want to remain job
just to make sure that these things are
all being converted yeah so if I have
weight inside here let's go through here
and we'll put in weight age looks like
it's already fine if I look down here at
experience you know this is the number
of years that a player has been in the
league but instead of giving rookies 0
they give them an R so this could
convert into a number you know so the
tool is gonna let me do something such
as do a replace value you know find
everything that's is an r and replace it
with a zero and now I can go through
here and I can go ahead and convert that
to a whole number you know and now I've
done like conversion inside there okay
now let's go back to the slides for a
second you know so we have this tool and
it's all about going to different data
sources you know be its equal you know
via Big Data it's in CSV files or Web
Services you know I'm able to connect
the multiple data sources and there's
this engine now so this is index this is
the query engine which is called the
mashup engine inside there and that's
what basically runs my queries and pulls
the data back and dumps it inside the
dataset now when I wrote this
m-code was just good to create datasets
Microsoft has now introduced data flows
which give us yet another place where
everything we're gonna learn tonight you
know we can use the idea of a data flow
is that you want to stage the data and
you might have a lot more data than you
can fit the power bi data set you know
it might be 100 gigs worth of data you
can ever load that in but we just want
to pull the data down cleanse it
transform it and then kind of put it in
Azure storage you know where I can
basically ass or as much data as I want
because data is cheap no so whether
you're pushing data into a data flow or
into a data set in power bi you know the
query tool in the mashup engine you know
it's going to be your friend
so what many people do is they use this
query editor window and you know you can
see that as I was creating this query
right here you know we started with
there was this one particular page that
had a bunch of tables you know and as I
picked the table and then I change
different types and I changed things
inside there you know it was able to go
through and do the work now these
applied steps are things where there's
an instruction that when I click and I
create a new step there's a piece of
code you know that's written and so you
know every one of these steps you know
has a piece of code that's written
behind in a language which is called M
my understanding is they basically
created a language called M as kind of
the cool code name and then no one had
any ideas how to create the real name so
M just kind of stuck and I guess there's
other languages like are with one light
or so also it's called the power query
formula language you know if you look at
the specification for this now if I go
back here in the tool and I go to view
note that I can bring up the formula bar
and see the M code or make it disappear
you know so there are two kinds of them
those that like M code those that want
to ignore their family and friends if
there's nothing I'd rather do than write
code and then the other extreme is I
don't want to see the uncooked if my
head will explode so
the tool is really made for both
audiences so certainly there's gonna be
lots of people that just want to hide
this and just use the tool and click
click click yeah but what we can see is
that for every single step here that you
know the tool generated M code and if I
go to the advanced editor and I bring
this up here to the code that's actually
stored and when they write your furry
this is the code that goes against that
mashup engine and executes ok so what
we're gonna do is kind of just look at
the language and talk about you know why
we might want to get more involved now
the first time you might come into
contact with this is that you want to
use something like a custom column now
so here's an example of a custom column
so you kind of saw that I was able to
take you know things like experience and
just using the tool I was able to
replace the arm with a zero and then
convert okay great how about this other
thing down here you know where we have
the height five foot ten six foot four
how the heck am I going to average that
so let's kind of start by I'm gonna
right-click and I'm gonna basically say
let's go ahead and split you know by a
delimiter and a year ago power bi
desktop would be smart enough to see in
the data there was life and now it is
you know so I don't have to do much I'll
just go ahead and do it okay
you know but now I have you know one
column for B and then I have you know
one column for inches inside here and so
now what I want to do is I want to
create a custom column and I want to be
able to you know fine you know what is
the height of inches so we go to add
column we now go to custom column and so
what we'll do is we'll create something
you know which is called height and now
I've got a rhytm code luckily the M code
here is not going to be all that
challenging you know because I'll open
up a parenthesis and then I'll say let's
take the number of feet and asterisks
times
twelve plus the number of inches and
let's go ahead and choose okay
and now you can see that we have the
number of inches note that when you do
this if I ever look up there and I see
abc123
it means I could determine what type it
is so let's up type any and generally
you want to you know convert to a
specific type rather than having any
inside there
but now you've kind of seen the first
place the way you might have to write em
code is that you have a custom column
okay now the second thing is we can also
use the advanced editor you know so
let's say that I have this query inside
here and notice that when I created the
query and I go back again remember okay
this was the Giants so let's go ahead
and rename this one to the Giants and
now I want to create a second and a
third and a fourth query but I don't
want to have to click click click each
time so one of the things that we can do
is we can go back to the advanced editor
window once I get there I'm simply going
to take all this code and copy it let's
now create a new query so if I go down
here and say give me a flight query they
give you it's absolutely a blank query
for various reasons they probably should
just bring up the advanced editor window
here you know but now let's go ahead
take that and you know paste my work in
now all I really got to do is kind of
figure out you know what is you know the
URL inside here so if I now go back here
okay I guess everyone probably hates the
Eagles even if you don't know about but
you hate the Philadelphia Eagles but now
let's go ahead and kind of put that
inside there you know so for people who
are kind of willing to work you know at
this level of working with the EM code
moving things back and forth and lots of
other things that we do you know we're
just gonna be a lot faster it's ident
okay now
what we want is we want more advanced
editors now once again I wrote these
slides a year ago so back then you can
use notepad plus plus and there's one
guy who kind of put an extension and to
make that work Visual Studio code also
has M query extensions and you can use
what we're going to show here at the end
is that there is a power query SDK now
one wonderful thing that happened
recently with power bi desktop let me go
ahead and close the query tool for just
a second
and now I'm gonna go back and there's
this pretty cool feature where now
instead of just seeing the advanced
editor with a dumb text box that doesn't
have a coating or intellisense let's go
back up here to options let's go to
preview features and there is now M
intellisense once again it says preview
probably will you know go to GA sometime
over the next couple months so I set
that I'm gonna go ahead and close and I
better go ahead and give this a name
I'll just gonna call this n FL now well
once you change the Primo features you
have to close and reopen power bi
desktop and now that we have done this
inside here let me go ahead and open
that project one more time and so we'll
open the query editor window you know
and now when we go to the advanced
editor you know now we got the you know
color coding inside there and you'll
also see that you know if I go to
something like you know table and I say
table dot you know this takes us you
know way ahead of where we used to be
you know we just have to write the code
and hope it worked okay so now we have a
better editor that's just kind of built
into power bi desktop now why would you
learn em
you know many people the answer is going
to be no good reason I'm just gonna keep
using power curry as it is you know but
what you'll see is that I'm gonna show
you a technique where we use something
called a query function that just adds
an amazing amount of power you know to
me that would be your first motivation
of kind of learning things at this level
you want to perform calculations across
rows you know we're going to show some
examples but you know other things would
be attached to a SharePoint list and
what they do you know in the code that
comes out of the box is that they figure
out what is the grid of that particular
list and they hard-code that in in
SharePoint I often use this technique
where if you want to take a list with
10,000 items and delete every item you
got to deal with one-by-one is going to
take you know hours to do that or you
can just drop the list and rephrase it
and you can recreate the same the list
with the same title but it's gonna have
a different width so it breaks so you
could basically jump in and kind of
change the dependency you know from the
quiz of the list to the title once again
just another example of a technique
where if you learn to work with an atom
you can get there and if you're reliant
just on the you know the UI experience
you can't get there okay another reason
you know might be that we have the same
query logic and I want to use it across
you know 10 different power bi desktop
projects you know so writing the M code
put in a source file and checking the
source control you know means that I can
now take that code out and copy and
paste into ten different projects you
know so it gets to be a good way to you
know have you know golden query logic
that you want to use across projects
okay and stay ahead of the pack so let's
go ahead and kind of look at some of the
you know some of the fundamentals of
working with it so it will have a
functional programming language and if
you program with VBA or your program
with B B or C sharp you know their
procedural languages so the idea of a
functional language is that you will
write a you know series of functions
that basically get evaluated and I'll
write function one which calls function
two and when we evaluate function one it
has to evaluate function two and then
evaluate
punks you want you know but what we're
going to see is we're gonna miss Lee
write a bunch of different expressions
instead of writing statements now every
query you know is a single expression
that returns a single value and that
might mean not mean a lot now and
hopefully you know after half an hour
talking about this and looking at the
language you know that will start making
sense also every query that you write
has a return type now just getting
started
M is case sensitive you know so if you
have uppercase first location you get it
wrong it's not gonna work it's all about
writing expressions then also when you
write a query you know it can reference
other queries by name so let's kind of
start with something easy inside here
and that is let's say I go back here and
I want to create a new query yes I'll
say give me a blank query inside here
and I'll put something like you know the
number two inside here notice that most
of the queries that you have look at the
icon it's a table you know so when we
start using it
we just gotta expect every query to
return a table but now I have a query
that returns a two and you can kind of
see the type inside there if I you know
change this to something else
you know so we're gonna put something
like hello inside there and notice that
change to a B and C you know so every
query you know has a return type let's
go back to query one and in query one
I'm going to let me just for this simple
demo i'll create a new query you know so
here we'll create another blank one and
this one will put a world inside here
you know so this is a query 3 and now
let's create query 4 and let's kind of
put things together and what you can see
is that if I call everyone inside of
another query so back here we'll go
and create yet a another query and now
inside here we're gonna reference other
queries by name you know so there is a
query to and then when I want to parse
things together we're gonna use the
ampersand and we'll put a space in there
and now we'll have query 3 inside here
and you know what we should be able to
do is to put yeah thank you very much
you put that equals in front of there
yeah and then all of a sudden you can
kind of see we get things working
correctly inside there okay so when you
start referencing queries by name things
start working now the other thing we
have is this thing which is called a
flat statement so when you want to write
things that are more complex you have
lat and then you have this you know
series of you know I kind of think of
them as variables that you're grading
var 1 equals hello world bar 2 equals I
borrowed 3 equals I want to bounce
something you know to uppercase and then
the let statement the idea is we start
by evaluating what's at the very bottom
and in and quite often to evaluate that
it has to come back up and evaluate well
which triggers the evaluation of this ok
but remember this is a single M
expression that returns a single value
so there might be lots of different
calculations of individual expressions
inside there as it calculates you know
the final value of this state now also
you can see that when I write this kind
of code and I create a variable one
variable two and it's got an expression
if I look at applied steps I can see
those kind of match up to what's inside
there now here's where things get a
little bit ugly we can either have that
M code ugly or we can have the user
experience ugly so what's gonna happen
here is that if I want to have a step or
a variable and I want there to be spaces
inside to make it more readable the
language says you can do that but you
extra stuff so in particular if your
variable doesn't have
they could just have the name and the
encodes a lot prettier if you have a
space you have to then say pound sign
and put the variable name in quotes
inside there but you can see that I I
can out break one that's really long and
I can start referencing those things
inside there now when I said one side
has to be ugly or the other when you
have spaces the applied steps looks a
lot prettier but you can have spaces
inside here so when you're using the
tool they err on the side of making the
encode a lot more messy by putting
spaces inside there but then forcing you
know they have the you know that extra
pound sign and quotes around there when
I do you believe with spaces and
variable names are evil yeah but I don't
think you can use underscores so it's
really just well there's a series of
characters but underscores and I think
it's the same requirement but you know
as you go back to the UI and you click
click click it's going to be creating
these things that have the pound sign so
if I take over a script that I know I
just want it right by hand I'll get rid
of the spaces and get rid of the pound
sign and the quotes just to make this
script look a lot cleaner and you're
writing em code you know but it's one
side or the other now if we look at this
right here you know here's kind of a
common scenario where I've written a
query and I have you know going down
here and so when this is evaluated the
first thing they do is they evaluate
output but in order to evaluate output
they then have to go up and evaluate you
know text upper and now text upper bar
three they have to evaluate bar three
bar three you know to calculate its
value we have to calculate what's the
value of you know bar one and bar two so
in order to calculate output there's
this trigger that basically evaluates
bar one then bar two of the large
and an output so everything gets
evaluated and then then returned you
know a single value back now here's an
interesting one if you look at this code
bar 1 bar 2 bar 3 output let's say I
took my things and I reverse them with
this code work yes yeah and this code
will absolutely work well this code
confuse the heck out of the UI
experience and the answer is absolutely
yes yeah so if you want to just go and
encode and do nothing but write encode
this is fine you know so the order of
expression in the let block doesn't
matter to the mash-up engine but it will
confuse in this tool and then you won't
be able to kind of go back and use the
UI portion anymore ok next thing is that
when I start writing M code there's this
really valuable principle of query
folding and the idea of query folding is
that if I'm writing a query and I say
connect to that sequel box and then the
very next step I say let me choose
columns I don't want every single column
I don't want select start you know I
want select column 1 column 2 column 4
so right after I have the first
statement that gets the data from the
database well then I have select columns
and then I have a select rows so the
mashup engine is smart enough to say hey
we're getting rid of some columns we're
getting rid of some rows why bring
everything back so the idea of query
folding is it can create a single sequel
statement that selects only the columns
that you want and has a where clause to
only bring back two rows that you want
so in general you know query folding is
a very important concept and we like
that inside there
does that get broken though when you
start doing certain things in them code
so that query folding efficiency stops
in a certain pool yes absolutely
so this some of the things we have to
worry about you know for instance there
are some things that would stop query
holding from being able to occur so then
if you put your where clause down below
that it has to bring back all the data
and then kind of apply the filter
locally and obviously if we bring back
10,000 IU's to filter down to 100 that's
not efficient you know so generally we
want to have you know all the things
that support query folding up at the top
as close as possible
okay now some data sources support
folding in generally its relational
databases and Oh data data sources that
support query folding inside there okay
what happens when the data source
doesn't work for it building it just has
to bring back all the data and basically
you know do the extra work you know in
memory wherever you're pulling the data
back to okay what affects the way that
query folding works the way we structure
em code privacy levels can also affect
that and then also you're able to do
native query execution I just want to
write my sequel statement and push that
through if you're ever using native
sequel statements query fully won't
happen but then the converse is if I'm
writing my own sequel statement I've
probably got my columns and where
clauses inside there so I'm bringing
back the exact data that I want you know
so I've kind of got the same effective
query folding just by writing the sequel
statements in design so here's an
example I have the sequel database and I
connect to the database inside there and
I'm gonna say items equal customer so
that basically when I connect to this
database that's the name of a table
there's my select rows there's my select
columns I can also rename columns so
these are the main things that support
query folding and then what you're gonna
see is that the mashup engines not going
to you know create the sequel statement
until all these are done and it's able
to you know take everything inside of
each of those and create a single sequel
statement that brings back to exactly
what I want okay now I might want to use
native words you know so note that in
the previous slide if you say source
item equals customers you know there's
an entity where a table that you're
connecting to and so here we're doing
something a little bit different you
know we're saying query equals sequel
I'm with another sequel statement you
know so once again if you have anything
afterwards that's filtering or selecting
columns it's not gonna work you know so
native queries you know never include
any query folding and if you're gonna
use these you know I won't use them you
know
as my first choice you might find some
scenarios where you have to or you need
to call a stored procedure you know so
there cases where we find ourselves this
is our only choice you know but in
general if you just let the mashup
engine work with tables and let it
figure out the sequel statements you
know generally that is what's preferred
now as you're creating things inside
there now with a good type system and
that's just a programming language just
like VBA c-sharp other languages so it
has types you know we've seen that if it
can't figure out what the type is it's a
type any sometimes we get back values
that are unknown you know so there's no
logic to our false number text in binary
now we also have data types for time and
that second one should be date not data
but time they take time date/time with
the timezone built-in and also iteration
and then there's complex types that
we're gonna look at so one of the things
that I might want to do is work with
lists or work with a record or if I have
a list of Records I have a table you
know so list record table and function
are also types inside the language if I
go back here you can kind of see the
return type yeah that's table because of
the icon you know this one right here
you know is you know a number inside
here if you know I want to create
something one two and we get the right
character inside there let's see if I
can finally get the correct character
inside you know basically we're able to
create lists now there's also
user-defined types and the idea is that
you have something like a table
structure and you wanted to find a type
we'll see that in just a second you know
but here's writing M code
and you know here's a number here's a
boolean you know so true and false are
both keywords in the language if I want
to create a list inside here I basically
just have something that is inside the
curly braces now that's gonna be seen as
a list
one other thing let me spin back here to
this notice that when you write a let's
State Minh 20 writing this code by hand
every single expression in the let block
has to have a comma behind it except for
the very last one so that's gonna trip
you up when you do this you're gonna
copy some things out and you'll forget
the last one you know has a comma where
it should or one of these doesn't have a
comma so now we've seen that let's go a
little bit further inside here and here
is an example of initializing variables
for time you know or date and you see
that there's functions that have the
pound sign built-in you know so pound
time pound date and I can initialize
dates and times with that also it's
great a list you know so if I create
this list you know think of a one
dimensional array so I basically have a
string array of three elements inside so
when I create this variable right here I
can also use this syntax right here
where I specify the list and then I say
I want the first element sub-zero sub-1
sub-2 inside here and that's gonna allow
me to kind of pull things you know out
of a particular list now also what
you'll find is that here you know we
only have three elements you know so the
index goes from zero to two so what's
gonna happen when I say rat-pack for you
know that's gonna be an error however
there is this syntax right here where if
I have something that result in an error
but then I basically put the question
mark behind it it basically says if
there's nothing there don't give me a
runtime error just return a null so
quite often we kind of break that code
you know to be protective and make sure
that our queries don't blow up now
here's an interesting you know example
this one that Chris Webb came up with
and that is what something that I have
to write em code for I can't do through
the UI so let's say that there's this
issue where we keep pulling back data
from the database and our gosh-darn
users keep adding things you know like
the pound sign or the @ sign it blows up
so one of the things that I could do is
I could write a fairly simple query just
using the UI now I have no right now
code we says find the pound sign and
replace it with a blank string and find
the outside and replace it with a byte
string the problem is I can't think of
what people might do in the future
so instead of creating the list to
exclude things when we come up with a
list of what to include so what we can
do is I can basically say set 1 and also
this is kind of a tricky way to do a
list where I say give me everything from
this to that so now this is going to
give me all the capital letters A
through Z this is going to give me all
the lowercase letters A through Z and
now I can do something like this let's
transform I'm hoping I'm not stealing
the Thunder of who's going to come next
but now I get 0 through 9 and now what
are the other characters that I want and
now here's all the characters I allow
and now we'll say text select and it
looks through strings and any characters
that I have an additive or automatically
excluded inside it you know so once
again there's kind of a practical
example of something that if I'm willing
to work with the EM code we can kind of
get done ok now the next thing we want
to look at is records you know so
remember when I work with a list we use
the curly brace so what does it record
just like a record a database you know
it's a single set of data typically for
you know one particular entity that has
fields and so if I open the square
brackets and say first name equals and
last name equals well now I have a
record now when you want to access
things within a record right here you
can kind of see that I'll say person and
then inside the square brackets I
reference the field name inside there so
now that we have less than we have
records what's the next thing we want a
list of records
okay now another reason that we might
use records is that there are certain
functions that we like to call you know
so web content is a good example because
what I call web contents I passed the
URL there is a second options parameter
but for me to pass something there I
have to be able to correct records
because it takes a record as the
argument so here when I pass this time
you create the options record and the
header is equal and what are the headers
equal is another record here we're gonna
take a record and bury it inside of
another record so I can basically pass
that as a parameter now one other thing
I want to note is that when you call web
content it basically generates an HP
request goes across the network and one
of the things that you could do kind of
dropping down to the lever develop the
level stuff is that you can start
putting headers you know named headers
in the request that goes out so here if
I say except equals well now I've got a
record with the accept and this just
gets translated you know into the HTTP
request you know Heather variables that
are going inside there and there's one
which is OData max version and because
this has the - inside I've got to
basically play you know inside of the
pound side and things inside there but
there's just a practical example of I
want to call web doc config I want to
have more options I have the Bay of
still you know crank records you know to
be able to pass those parameters inside
there okay now here's a couple other
things you have this thing called the
concatenation operator so it's pretty
obvious with a string what that's gonna
do you know but look what it can do if I
have this list it has one element then I
have another list and I basically can
kind of make them together just kind of
gives me a union one list that has you
know all the elements of both lists and
also I have a record that has a and I
have another record that has B and I
merge them together and then I get one
record with both those fields you know
so quite often when you're taking data
for multiple places and
merging it together you know the tool is
kind of creating you know this type of
logic behind the scenes and sometimes
you know you want to be able to get the
point where you can write this type of
logic inside to okay now let's look at
tables so let's say at first we'll just
kind of hard code you in a table by hand
you know so customers table table front
record and then I just basically have a
bunch of Records there to have first
name and last name you can kind of see
the output inside that's you at this
point let me also go back to this demo
and instead of using this file I'm going
to open up a demo file you know so as
part of the demo files we have one which
is called introduction to M programming
and when you open this up you know
you're gonna see that there's really
nothing in the report other than a
message saying everything lives up in
the queries here no but now if I open
this up right here you know so this is
something that you can open up and now
you can kind of start looking at some of
the things inside here and you know kind
of seeing how you know we can start
working with some of these inside here
know for instance you know there is the
simplest also over here you can kind of
see the different icons you know if it's
a list you're gonna get this icon you
know if it's a table you're gonna get
you know this icon inside here and so
you know if I open this up you know
there's my table that I've gotten back
inside there okay now going a little bit
further here these table columns you
know are not strongly typed because when
they come back it says what is the type
and II don't like that I could go
through by hand and convert the types
you know but if I'm right in the EM code
I might as well figure out you know how
to write the EM code so that my columns
you know have strong typing inside there
so here's how you can do that you can
create a user defined type so here we've
created this type called customer record
type and only it was I basically defined
I have a record that has a first name
and a last name column and both of those
or
you know I can create other columns you
know of type number or type boolean
inside there and now what I can do is I
can say I want to have a type which is a
table which is composed of this record
type inside here now kind of putting
this you know into a more practical set
here let's go here and I create the type
of record that I want and then I create
the type of that and now down here when
I say at table I say customer table type
so now I've defined you know what the
column types are going to be so that's
how you can basically create strongly
typed columns you know as you're writing
your EM code inside there okay now the
next thing I want is to discuss
something which is called unary
functions now if I kind of do the
long-winded way first you have tables
Legros you have this customer table and
then what you want to be able to do is
kind of do a for each you know so you
have this kind of row and then you have
this operator right here and so what I
want to do is I want to select rows I
want to filter and so you basically
write a function here that looks at the
rows and a customer ID you know is
either equal to or less than ten return
it you know so here's kind of how I can
do the long-winded way and you kind of
see that you put a table inside here you
know here's the thing I want to filter
and then here is a function that we won
on each row to come up with a true or
false of whether to include that row in
the set floor to exclude it from the set
okay now watch how the syntax you know
gets easier maybe not quite as
understandable but now this is the exact
same code just with different syntax so
what we can say is we can say each I
want to go through there look at each of
these records and what you'll see is
that there's this weird little
underscore and basically that is the
record you're looking at so you go ahead
record number one record to record three
and yours
when I look at this record look at the
customer ID you know so it's just kind
of that and a little easier syntax but
now let's go ahead and get you know rid
of that underscore so because this is so
common that you're looking at records
but now you just want to look at one
field that the underscore is optional
but still you're looking at you know the
field of a record instead of a whole
record itself now here's another example
that I want to add column and as I add a
column I have a first name and a last
name but let's go ahead and add a
calculated column for display name if
you go through the tool and you
basically say add custom column like I
did before you know this is the type of
M code that they're going to MIT for you
they're gonna have in each there can
have these things inside so now I have a
new column name display name and it
simply goes row by row and for each row
by the first name last name within the
spacing between concatenates them
together now most of the time when you
use this you're working with a table so
you're looking at a record and you care
about one field okay here's another
scenario and the scenario right here I
don't have records but I have individual
strings so it's not something that has a
sub object it's the thing itself so now
when I go through this if I do list
transform and now I say each text upper
and so that refers to the item itself
the underscore not very readable you
know but once you understand what the
underscore beans you know it is the item
that we're looking through and it goes
through you know enumerations of item 1
item 2 item 3 and so on okay everyone's
having fun so far I can tell that let's
go ahead and look at some of the other
things that we can do with M code you
know so here is an example where let's
say that when I'm here I have a value of
124 but when I go here I want this to be
you know
some of these two inside here okay now
first of all you're gonna say why would
you ever do this in EM where we could
probably do that much easier index and
that's a pretty good argument you know
but there couldn't be cases where you
want to calculate a running sum and you
want to stop at some point or there just
could be cases where you want to do it
in your M code right I can't write -
Mike in my demo that would be one good
example so if I say not list dot range
what we can do is we can basically
figure out you know what the sales are
but also automatically you get an index
so when I look at a row in a table it's
going to give me an index if it's the
first item is gonna be zero so what
we're gonna do here is we're gonna say
you know when I get to this place right
here you know what we'd like to do you
know define this value you know is
basically to take all those values that
sum together you know so once again this
is something you could not do working
through the UI you know you'd have to
write em code you know if you want to
basically sum up things across rows
inside there now when we work with em
there is the power query language
reference that talks about getting
started it's a kind of a geek document
you have to kind of be a developer to
really want to look at that but then
there is a second thing which is the EM
function library so let's look at some
of the EM functions inside there you
know so let's say that I want to pull
data back from an O data feed inside
there you know so now we can go ahead
and put in something and now when we get
to this o date at endpoint you know what
is the name of the entity you know it is
customers inside there and what are the
columns I want to keep so now I put this
together
and I say table dot select columns and I
put the columns to keep it side there
okay and then I want to select rows and
you know notice that here with my M code
I kind of kept the variable names so
they don't have spaces so we don't have
the pound sign and the you know the open
quote inside there
okay now Oh data dot feed is something
that makes a call to get native data
back and then makes another call you
know to actually get the data so it
turns out that web contents is more
efficient than using a data feed you
know but now you're gonna have to know
more about OData so here's an example
where I call into this API and I have to
know enough that when I call into Oh
data and I want to get just the columns
I want you know we can say dollar sign
select here are the columns that I want
I can also put filtering inside there
you know so if you use Oh data dot feed
it can basically get query folding but
it's gonna have to make an extra round
trip to get the metadata before it can
discover the query quality so some
people like this because it's a single
round trip you have to know more to kind
of write the expression inside there you
know but once again if you know how to
make a no data call to get back just the
blows and columns that you want you know
if that's gonna be more efficient as it
goes inside there okay now the next
thing we're going to talk about you know
is the reason that some of you might be
inspired to go actually you know work
with the SEM code this notion of a query
function now query functions are simply
a query that takes a parameter inside
there now let me you know show an
example so I've got this data and let's
go back here and inside here in data
yeah I've got a couple different
basically unstructured text files you
know they have some data inside there
now what I want to do is let's open up
SharePoint inside here so first of all
let me go ahead
and acade and now that I've done that
we're gonna go to Portland embed
SharePoint calm I know
and so let's say that my data lives
inside of SharePoint so I'm going to
create a new document library and you
know inside of this document library
we're gonna put some data file now in
our case right here I'm gonna turn the
sound off because general it means I did
something wrong
let's give this a good time so was that
it really wants you to set your time
zone to it so let's create this new
document library and I'll just kind of
call this data it's gonna make the path
a little bit easier to get to okay and
then inside here what I'm gonna do is
I'm going to take three of these data
files not all four I'm going to put the
fourth one later and let's go ahead and
get rid of this and take these inside
here okay now let's say for this very
first query if I want to get something
out of SharePoint most people would
think is there a SharePoint connector
but because this lives in a document
library and you need an HTTP request we
have to use web contents so what I need
to do is first of all you know figure
out you know what is the path so let's
go back here and I'm going to let's go
ahead and just create a new project
before I'm gonna do inside here and as I
create this new project I'm gonna
basically write a query which is gonna
go pull data back from there so we're
gonna say I want to get something from
the web it's gonna bring up this box and
I'll put this inside there and then I
need the name of the actual file now so
let's say I go here because I'm lazy and
I'll just kind of copy and paste that
inside okay now we'll go ahead and we'll
add
inside and we'll go ahead and choose
okay so at first it's going to say that
you've never connected to the SharePoint
site you know so it's going to want me
to connect and Microsoft gives you a
dialog where many of the choices they
give you will absolutely result in
failure you have to know to pick
organizational account we're going to go
ahead and sign in with my organizational
account and once I have signed in and
then I should be able to now get to the
data and power bi desktop
remembers this and it's not going to ask
me it again but let's go ahead and
choose connect now when I look at the
actual data source you know here's an
example of a file that maybe got
downloaded from a mainframe and I have
to basically figure out you know how to
get the data out of the valid rows and
how to ignore the rows that aren't valid
this is something where we have fixed
with columns inside there you know so
this is 12 the next one is 12 the next
one is 24 so back in power bi desktop
what I can do is I can say hey I'm
dealing with a fixed-width file and the
first one starts at zero and goes to 12
and the next one starts at 13 it goes to
24 the next one goes to 48 and so on and
now the rows that have valid data you
can see that I can see those inside
there so let's go ahead and choose edit
and you know as I choose edit you know
let's call this one you know expenses
inside here and you know as we've got
that inside let's go ahead and rename
these columns you know so this one is
going to be the date the next one is
going to be the expense amount going
over here then we have the expense
category and then finally we just have a
description inside here now step number
one is let's get rid of those rows that
have valid data so an easy way to do
that is to go over to this first column
and say let's go ahead and convert that
into a date and some of these converts
just fine and some of them had nothing
in there so they convert to null and
some had strings that can't convert to
date so their errors so a very easy way
to deal with this is to say remove
errors and then to say remove things
that are empty you have to do it in that
order if you did the opposite order
wouldn't work you know but now I've kind
of gotten rid of the invalid rows of
data let's now go to a mount and let's
go move that over to fixed decimal you
know which is what we should do for
currency now for category you kind of
see there's some white space before and
afterwards you know so what we can do is
we can do a trim which basically takes
the white space off the front and back
and I don't need in this case but
there's also a clean that will get rid
of the you know the non-printable
characters the things that you can't see
let's screw you up every time you try to
do something with data okay so let's get
rid of those here and we'll get rid of
those here as well okay so now you know
I've basically got my you know first
query inside here and you can kind of
see that you know I didn't really have
to work with the advanced editor you
know but I've got this work done now for
this demo I'm gonna create one more
query and this query is gonna use
SharePoint so I'm gonna say create a
query and with this I'm gonna pick
SharePoint inside here you know so there
is a SharePoint connector and here I
want to pick SharePoint folder now you
saw that earlier when I went to this
SharePoint site it basically queried me
for credentials let's go back here and
when I do this I'm gonna put this
SharePoint site in but what you're gonna
see is that I have to add in the
credentials again because before I was
using the web you know
web content and now I'm using one of the
SharePoint connectors and they don't
basically you know share credentials so
now here I'm using an organizational
account you know but just for fun they
go ahead and switch it to organ is a
Microsoft account just to confuse you
you know but now we'll go through and
you know once I've logged in here then
I'm going to be able to use you know any
of the SharePoint connectors and they'll
already have my credentials cached
inside there okay now here when I pick
the SharePoint folder what it really
does is it basically does a site-wide
search and just to you know verify that
let me go ahead and choose edit right
here and let's go back to this site for
a second and let me go to a different
document library and let me find you
know some other file inside here so I go
here to slides and once kind of take a
slide and add this one side and so now
that we've added this other file inside
and there it is let's go back to our
query and refresh my query and what
we're going to see is that it's truly
doing kind of a site-wide
looking at every single document library
and every file inside so now what we're
gonna do is we're going to go to the
folder path because now this tells me
exactly you know what document library
is coming from and now I can filter and
say don't find things at any other
document library other than this one and
share points one of the things that
supports query folding so that will
basically send a request and not bring
back you know data about all the files
that will just bring back files for this
one thing inside there now for later in
the demo I need a file path to each one
of these files so what we're gonna do is
I'm gonna pick a folder path I'll come
back over to this side right here we
have the file name and I'll just
basically select those two and then
there is something where I can say
remove other columns and then I just
have those two so now what I'd like to
do is I'd like to kind of combine these
two together now it doesn't matter
which is on the left which is on the
right it matters which one I select
first I select this first then I hold
down either controller ship select the
second one and so now we're gonna
basically do a merge columns together
what's the separator none what is going
to be the name when I'm all done you
know and that is going to be file path
and now I basically just have one thing
that's the file path okay so I'm go
ahead and change this to expenses and
I'll change this to expenses too for a
second so I can come back and take the
other query ever this is the one that
actually does the work to take the data
and basically process it and put it I'm
going to change that to get expenses now
I'll come back to this one and I'll just
call this ones expenses now here is
where we're going to create a query
function and when I create a query
function I'm going to have to go into
the advanced editor and the way that we
create a query function inside here is
just by going to the very top and
putting in a parameter list open and
close paren and then I put the error
operator now which is the equal sign and
so inside here you know we're gonna say
file URL it's optional but it's good to
kind of say as text so you kind of imply
what type so now that we've done that
I'm gonna take this parameter and let's
get rid of this hard-coded file path
inside here so the idea is we're trying
to make this so that I can use this
function and call it repeatedly and just
pass a different file path and it's
going to give me a different result each
time now the good news is it's really
powerful the bad news is once you create
a function query you can no longer use
the UI experience so basically the
advanced editor is all you've got you
know so the recommendation is do as much
work as you can before you convert it
you know into the function query so you
kind of see I've already done all my
logic here
I just basically made it you know into a
function query at the end so now we've
done that let's go back to the first
query that has the file
so what I'd like to do is for each one
of these files I'd like to go ahead and
call that function query and pass the
file path inside there so the way that
we're gonna achieve that is by going to
add column once I get to add column I'm
gonna say I would like to invoke a
custom function so now and what is the
column name and it doesn't really matter
what I call this something's gonna call
it expense expand we're gonna get rid of
the column the column names gonna
disappear before the end but now what is
the query function and so for every
query where I've added one or more
parameters it shows up as a query
function so we'll go ahead and pick that
and because I've defined one parameter
it says pick one of the columns in this
list when there's only one and have that
be what feeds the parameter inside there
now I'll go ahead and choose okay and
basically saying information about
privacy so what I'm gonna do is I'm
going to come through here and I'm gonna
say let's make everything public you
know so that these error messages go
away inside there okay and now it's
going to run this inside here and it's
gonna make me still do more stuff inside
and make that public and what I probably
should have done is just go ahead and
turn the session option on to basically
get rid of you know those warnings okay
but now we have one row for each file
what we'd like to do is expand that so
now you can see that inside of that cell
for each one of these rows you have a
table and that table has multiple rows
so what we can do is we can now expand
this so that we get you know basically
one row per expense instead of one row
per file now when I do that here's the
data that's going to be inside there
let's go ahead and choose okay and now
it does the expansion inside now you can
see that when you do this it kind of
loses the type so I'm going to go
through here and I'm gonna put you know
the correct types back inside here okay
and now that we have done that and let's
go ahead and change that to text
and we can change this to text do we
need this anymore
then the answer is no you know we've
used this well in its golden locks a
thank you very much for playing you know
we'll go ahead and get rid of that so
let's kind of finish up the demo by you
know taking this and doing a close apply
and so this is now gonna run it on three
files and to kind of demonstrate this
let's go ahead and create you know a
simple report you know so we'll create a
chart and we'll look at the amount and
then we'll basically take the date and
have that be the access but now we're
gonna kind of use that neat feature
where it automatically builds out a
calendar hierarchy so I want to look at
each particular month right here okay so
now what we have is January through
September okay but what's really neat
about this query is that discovering
what files are there you know so now to
kind of finish off the demo let's go
ahead and go back to that data and the
document library currently this has
three files inside will now go back to
find that fourth file and let's drop
this you know down inside here and now
we have four files I don't to change my
query logic let's go ahead you know and
do a refresh inside here and that just
discovers you know there's four files
instead of three you know so holy david
together from multiple places you know
gets to be something that once you learn
how to use query functions you're gonna
be able to do some pretty neat things
you know think about i have a point of
sale databases you know I have 27 sequel
databases strewn out across America and
I don't need to get data from one of
them they have the exact same schema but
I need to basically get all 27 built
into one dataset so you create a table
that's got the you know query string for
each one and then you can do something
similar where you basically just move
through and run the query against
database one the database to database
three and it all dumps into a single
output table yeah so I've just seen most
people as they see M codes for me I want
to get into it this typically you know
if you
comes the motivation for that okay let
me take a pause take questions at this
point anybody have questions okay you
got to make these presentation materials
available yeah they've been available
for a year so at the very beginning you
know I only had this you know basically
this place right here so these slides
and all the demo code is available here
into this repository okay let me move
ahead to where I was okay so now I kind
of seen some of the basic things inside
here and finally get to query functions
inside okay now some of the other things
that you can do is let's say that you
want to create a query to generate a
sequence of numbers you know maybe
you're doing some type of mathematical
work or you want to make some kind of
sample data set so list dot generate you
know is one of the functions that's
built-in and accepts three parameters
and so the first one you know is a
function that takes no parameters and
has a single output they probably should
have made this a function you know so
you pass that inside the second thing is
the item that you're looking at and you
can have some type of a mistake then
inside there and then we're gonna say
each item you know is the item before +1
now one of the things that you could do
is you could kind of replace the second
and third parameter you know with each
inside there and the idea is let's go
through each record and the underscore
you know now becomes the item that
you're looking at okay and then also you
know I could split this up if I think
it's more readable inside here yeah so
you have the start function that's this
and this just returns a 1 that's my
starting point you have a test function
let's go ahead and test you know where
we should stop and then you have an
increment function also the idea that
this says
one that says keep going you know until
your item you know reaches ten we don't
want to be over ten and this basically
says take the item and add one for the
next one but you can kind of see that
you know mathematicians could do all
kinds of things if you want to break
like the Fibonacci sequence or something
like that you know this would give you
the ability to do that okay
who's created the Fibonacci sequence in
the last week or so nobody okay
Paul has Paul does it in his dreams
what's that five minutes okay so we also
have query parameters and the idea of a
query parameter is that you can define
something in a power bi desktop project
that then can easily change so one
reason we might make a query parameter
is that I don't want to hard-code the
database connection string you know into
my project because I have this one power
bi desktop project that can look at any
one of ten different sequel databases
that all have the exact stable schema so
I want to kind of pram ERISA way the
connection string so instead of having
to kind of dig into the guts of a query
and find the connection string I just
basically want to set up once per
project and then you know inside the
query you can reference the parameters
inside so query parameters you know get
to be a big thing now you can use query
parameters without writing em code you
know you can see that if I create a
query parameter I can then go into you
know many of the dialogues and say I
want to use a parameter as opposed to a
hard-coded thing inside there and then
what we're able to do is you know come
up with ways of allowing the user is
using the project to change the
parameter and there's different ways to
be able to do that inside now but what
you'll see is that parameters you know
get to be very flexible when you are
working directly with EMCO
you try and publish it well parameters
if I go back in year we're only
understood by power bi desktop so when
you publish them they totally disappear
crying and then because of so many
customers telling Microsoft I really
want to be able to control things they
now make it so that either manually you
know or through PowerShell or through
custom code that I write I can go and
change a parameter you know in a power
bi desktop project and what's neat about
that is I could publish pvx file and I
can have a piece of PowerShell that says
change the parameter that has the
database connection string in it and
then go ahead and do a refresh you know
so running PowerShell I can say take my
data set now the power bi desktop it's
already in the cloud switch the data
source and go ahead and refresh it those
are some of the things you know that you
can do with that decide they're the
power bi service API gives you access to
parameters and also the new PowerShell
support you know does the same thing but
they're supported in both you know I
would use the power bi think it's one
reason a parameter needs to have a
default value so that it can you can run
even if input isn't provided that's
correct
well there's a default value I think you
have to provide a default value when you
add them inside there in occur so
there's some fallback that it needs
inside there okay give them the time
constraints I think rather than covering
more material I'm just gonna open up to
a couple more questions about things
that we've covered here yeah go ahead
function to do a select from multiple
databases with the same structure I will
create a desktop will I try to is there
a trick to getting an F function that
iterates through multiple sources - yeah
I've heard of many problems the dimple
have had you know I can't speak to one
specific issue that I've heard about
that solves that particular problem you
know some have to do with privacy once
you try to string them all together with
function and basically a numerator all
at once it fails you know off the top of
my head I don't have an answer and then
in the core problem is probably that the
formula firewall doesn't doesn't trust
ya the the connections and it has to do
with privacy and authentication and the
the formula firewall has all kinds of
rules about whether whether or not
connections should be trusted
there isn't a silver ball an answer to
the to the question but that is probably
the core issue that you're experiencing
there's just a trick well if the new
trick would be a higher Chris Webb if
there's like a case mismatch in the
server name or something like that if
that if the server name and database
name strings don't match exactly you're
gonna get a Gateway problem even those
who haven't dealt with privacy of
firewall rules you know the problem
comes down to you have two different
data sources so you go query data source
one you get back a bunch of customer
names and then you send a query with all
those customer names and we're close to
something else and all of a sudden the
other thing is oh look at all this
customer information this is great you
know so it kind of errors on the side of
being conservative and locking things
down you know so the firewall rules are
really
tough to understand and that's
definitely something where Chris whateva
said the only blog entries that have put
any reasoning around how those work okay
maybe one more question anyone have did
you a question for the recursive
functions ir or if we want ideal
recursive functions for ya m does
support recursive functions and if you
look at the M language specification
they have an example of using recursion
inside there you know so you're able to
create a function that called itself you
know so if you wanted you somebody like
you know crawl a directory structure and
you usually recursion you do that yeah
but it does support recursion inside
their language you know supports it okay
well with that I think time constraints
Gregg is kind of putting the cane on me
now pulling me off seizure regulate
become very close
[Applause]
[Music]