>> Good morning, good afternoon and good
evening, everyone. Thank you so much for joining us in any time zone that you're in. We really appreciate
your time today. Today we have the amazing
Ted Pattison from Microsoft. He is a Program Manager with the Power BI team and he
leads is Dev Camp series. We also have Daniel
[inaudible] , our producer, who'll be on standby, he running this fabulous call. My name is Kelly Kaye, I'm the Community Engagement
Lead for Power BI. Today, Ted is going to take us through his Power BI Dev Camp, I think, it's session
seven or six, is that right, Ted? >> This is session eight. >> This is session eight.
Great. Session eight. So take it away, Ted. >> Hello everyone. Welcome
to Power BI Dev Camp for our monthly discussion where we're going to talk
about M programming today. Just a couple quick things
I want to point out. One is we have a portal
for Power BI Dev Camp. I just put something
inside there. But we have a page for each
of these sessions and we have some particular links that
you could basically get to. For instance, if I go
ahead and click on here, let's see if PowerPoint
will let me get out here. Then once we get here, if I go to today's session, someone took exception
with the Word, warrior, in my title, so I've changed it to
Intro to M Programming, so it's very friendly
for everybody. Let's go here, and if
you go to the session, here you can download
the PowerPoint slides. There's a couple of PBIX files that I'm going to show
during the session. I just wanted to give everyone a quick way to get
at those things. Now, what we're going to look at is basically programming with this programming language called M. We'll do a little bit
of background first. So everything is built on top of this technology that Microsoft
has called Power Query. At the base of this technology is something called the Power
Query Mashup Engine. That's the thing that
executes your query. Now, what's really neat about this Mashup Engine is
that it's very flexible. You can use a tool
like Power Query and not know much about ETL, but still be able just
to figure out how to do some pretty neat magic as
far as doing the extraction, transform load logic and put
it into a Power BI dataset. You'll see that we could also use the exact same language and tool set to take data and
push it into a data flow, and it basically works
in Power BI Desktop, it works on the Power
BI Service and will basically sometimes
run on a gateway, and it can also run
inside of Excel. Power Query Mashup
Engine is everywhere. Now, what you're probably
familiar with doing is working with Power Query just in the regular Power
Query Editor window. What we're going
to look at today, it's a little bit more
under the covers. Note that in the Power
Query Editor window, you can turn on and
off this formula bar. For instance, let's say
that I go over here to one of the samples and
I'm going to show it today, and I go up to the
Query Editor window and I go to one of my queries. What we can see here is that
there's the formula bar. If you don't see it,
it's just something that you can turn on and off. We turn it off if we're afraid of M code
never want to see it. If we're embracing M code, and that's probably the
reason you're here today, we're definitely going
to turn that on. Now, when you use Power
Query as a general user. You basically do an operation
by clicking a button or right-clicking a column
and choosing something and what gets created
is, what we call a step. Every query is just basically a sequential list of the steps that get
processed in order. Now, another thing about queries, because quite often when you
get involved with a project, they can get a little bit messy if you leave them
all at the same level. If you look at this
example right here, here's something where
when you create a query, you're able to take it
and move it into a group. So basically structuring
your queries in these groups can
really help out. Just wanted to say, in
case you've never seen that some of the examples
are going to use that. Now let's get into the
M programming language. The first time that many Power BI Desktop report
builders and dataset builders encounter the M
programming language is when they want to go and create a conditional column
inside the Query Editor. Now I have an if, then, else statement, and what
language is that in? That's in this language
called M. Now, we can also get into the
Advanced Query Editor, so we're going to spend
quite a bit of time there. If we look at the
Query Editor today, let's go back here
and let's take one of my particular queries and
with that particular query, I'll go into the Advanced Editor. The Advanced Editor used
to be just a textbox, so it didn't really
have any smarts. But over the last
couple of years, they've put color-coding
and IntelliSense in here. It's a much better experience than it used to be
as far as being able to work with the M code directly
in Power BI Desktop. Now, why should you
learn M programming? Basically, it's to
accomplish things that you can't do in the
Query Editor alone. Being able to create
a query function, perform calculations across rows. If I want to create a query
on a SharePoint list, and I find out that the
people who are making to the SharePoint list keep deleting it and recreating
it with the same name, but the GUID changes. I just want my query
to have dependency on the name of the
list, not the GUID, so that would be another
situation where you have to find yourself going
into the M code. Now, another big benefit, one of the problems that we have and it's taking
us a while to address, is that a PBIX file is really just a big black box as
far as versioning goes. If you want to version
queries independently, you can simply take this code, put it in myquery.mextension and then you could check
it into source code. Just having the golden copy of your queries might
be another motivation, and then you can basically
just pull that logic out and copy and paste it
into a new query. Let's go back here one
more time and let's say that I go to the Advanced
Editor window and I copy that. I can go in this project
or in another project. Let's go back here and
say, I want a new source, I want a blank query
and I can simply open up the Advanced
Editor and I can copy and paste a query across. As long as you're going from
one project to another, if this query dependent
on other queries, you didn't bring
those it would fail. But that gives you a way, it's a lot of click,
copy and paste. But it does give
you a way to move query from one
project to another. Now, one of the things that I like to see is that there are so many
things that you can do, if you start writing the M code. This is just a gratuitous
picture I created years ago, when I created this session
for the first time, but the idea is, you're really able to do some
pretty neat things. Hopefully, we'd
see some of those. Also, I have to
point out that there is one person in our industry, Chris Webb, I'm very lucky in that I get to work on the
Power BI Camp team with him. He is everyone's authority on Power Query and
writing M code. So if you go look at his blog, he's got 50 amazing
blog posts that goes so much further into M than anyone else has
ever been able to do. Once again, if you're
serious about Power Query, this is the blog you need. Now, the demos that I'm going to be showing
as we go through this hour include this Query
Design Demo right here. I'm going to show one neat
thing about query design. A lot of times it
becomes creativity. Let's say that you import data. But when your boss
looks at a report, she wants to see, when was this data refreshed. There's this common
trick that we use, and you can see that this is not a very complicated query. Let's take datetime,
fixed local time. Now, when I see that time in the Power BI service is going to be
Greenwich Mean Time, so I'm going to push
it back five hours so it shows East Coast time. But the idea is that, now we have just a query
that runs itself and basically gives us a timestamp of when the data was refreshed. If I go ahead and close this up, now we go back over
to the report itself. One of the things
that we can see here, is that now we can put a
timestamp in front of the user. Once again, just
by coming up with some ingenious way to use M inside of some
particular example. Now let's go through the M programming
language fundamentals. M is a functional language, and what that really means is lines of M code do not
perform operations, they basically
evaluate something. It's really evaluation, and
what you're going to see is that one step can't be evaluated until a previous
step is evaluated. M doesn't really
support changing data. It works in terms of
immutable data structures and the idea is that when
you're going down a query, you continue to build a
new set of variables. Now every query has a single value it returns of a particular type and we're
going to get more into that. Now, other things to note is, M is case-sensitive, so it's not VBScript,
it's more like C#. It's all about writing
expressions and figuring out how to write an expression that evaluates to what you want. Now query expressions can reference other queries by name. Here's an example where
if I create Query1 and a query could just return
basically a string value, so let's go back here. Now here's the other demo. This one is called
Intro to M Programming. Note that when you get
to the main fore window, there's not going to
be anything there. Yet you got to go and click on "Transform Data" and get to this window before it's
going to be interesting. But let's look down at all these queries that I'm
going to be demoing through. You've got to see that
this one has ABC, meaning this product
returns a string. What does the query look like if it's going to return a string? Basically, you can
just see that it has values and returns
back inside there. Let's talk about one of the first things you need
to get your head around, which is the let statement. A let statement is a single expression that
returns a single value. But within the let block,
there's a lot going on. Each line in the let block, you can think represents a separate expression which is then assigned to a variable. You'll see that every
single line in a let block, except for the last one, has to have a comma behind it. Let's go to a regular
query right here, and just to make some comments, let's say that we go to
sales running total. Let's go ahead and open that up. Notice that there
is a comma here, but there's not a comma here. What happens if I
remove that comma? Basically, you got an error. Let's go ahead back
here and fix that. Let's go ahead and
put one at the end. Now, we're going to
get the same thing. It's all about you are making sure that every single line, except for the very last line, has a comma behind it. That's how we start
structuring things, and now that certain things
start working again. The next thing I
want to do is let's talk about variable names. What you're going
to see is, first of all, we're going
to add comments. If you want to add comments, comments are ignored
by the mash-up engine, but there is a
later, so one can go back and see what you
were trying to do. Also, note that we
have variables, and if you have a space
in your variable name, your M code becomes
a lot more ugly. What you can see here is, let's go ahead and look at one of these particular
variables right here. If I now go back here and we look at the advanced
editor, that looks great. What would happen if
I come back here? I'll go ahead and rename. We'll go ahead and rename that. What you can see is it had to make this variable
a lot more ugly. What you can see is that the steps are your
variable names. If you put spaces
in your step names, it makes the applied steps
thing look better yeah, but it makes the M
code look a lot worse. I'm in a habit of after
I've created a query, to basically go back
through that query, let's see if I can find one here. If you've got to
see all my queries, I have just this need
to take the spaces out. I come back here and let's go back to the
advanced editor. You can see it's a
little bit ugly there, but now I'm going
to go back here. Once again, this is a
preference on my part, but I don't really care
about spaces here. I'm much more concerned with the M code being
more straight ahead, easier to deal with. Once again, that's a
good reason to avoid spaces in the names
of your steps. Now, let's look at
this basic concept. Let's say this is a query.
What's going to happen? The first thing that happens
is the mashup engine tries to evaluate what's at the very bottom after
the end keyword. As it evaluates this, it then has to evaluate
its expression. To evaluate that expression, we have to evaluate
something before, we have to then
evaluate var2 and var1. There's this triggering of expressions where as this runs. It starts from the bottom up, but then the evaluation goes
var1, var2, var3, output. Now, also note that what would happen if you
had something like this, will this M code work? If I come back over here
and we saw the first one. That first one, and
now it looks fine. Let's go to this one right here and inside of this
we'll go backwards. The mashup engine is fine. But what happens when you
do this is it confuses the applied steps so the power query design or
can't really deal with that. If you were writing M code by machine or by hands and not dealing with the
designer this would be fine. But, generally, we
should avoid that, just because we like
the designer being able to going to
show us the steps and the logical progression. >> Hey Ted. Just wanted to jump in quickly before we go onto
the next section. We do have a couple
of questions here. Is that okay if we just answer a couple of those from
the previous section. >> Yeah, I can see some
of these questions here. >> We've got, "Would it
make sense to expose the internal row numbers
so developers would not necessarily have to
add index columns?" That's one question from Loots. >> I'm going to answer
that when I get to index, which I'm going to get
to that in a little bit. >> Excellent. Then the next one. "Is there a way for us to hide M functions that are not
supported on gateways?" Like and he put a SQL
database link thing. >> Now, [inaudible] I think
it is can we do something in Power BI desktop,
flick a switch, so I can't write code that would not run
in the Power BI service or the gateway? Unfortunately, there's
really not any type of robust enforcement
in the tool. It really has to be you
writing the queries, test them in Power BI Desktop, then pushing them to the service and seeing if they
run there or not. >> Then Chrissie, she asks, is it also possible to run M queries index studio along with the query editor or is it the best practice to run them
in the query editor? TIA. >> I would run them
in the query editor. >> That's a yes? >> Yeah. I don't
really know that. I've run a DAX Studio, Dax functions, I just don't know if it also supports M code. I can't answer that question
because I don't know. >> No worries. Danny,
if you'd like to take the base player
conversation on to our kids personal
Twitter, feel free. >> Super. >> He says you're based
on the background thing. >> Now, let's get into some
of the type systems here. You can see that there are
types that are built-in. In addition, we have
complex types that we want to take a look
at in this session. What is the list
type, record type, table type, or function type? You could also define
the user-defined types, which gets to be a very robust
way into pull data back, especially when your data files sometimes have up to 12 columns, but sometimes they
don't have that many columns or it can't automatically
interpret the type of column as it's
ingesting the data. We'll see some of the
examples of those. Here's just some examples
of if you're writing M code and you assign var1. That's a number, true, false, boolean values, you have text values, and
then there is null. Now, if I wanted
to create a list, you can think of it as an array. You can see we have the
curly braces right here. In addition to the
curly brackets, that we have here on the list, how do we access simply
inside the list? You use the same curly brackets. Other languages use square
brackets or parentheses and because M code
uses this to work, to basically dig into the array and get
something out of it. We also can create records, and you can now see
this record syntax of being able to put the
square brackets at the end and the beginning and then just have fields and values. You can also see that if you have a record and you wanted to
pull a filled value out, that's the syntax right here. We're going to look a
lot more at tables. Here's just some simple syntax to get started with the table. Note that many of the
functions that we have in the language are stringed to us because they start
with a pound sign. But you get used to that. Here's a quick example
of a function. When you define a function, you define a parameter list
of one or more parameters and then you define the function
body, how it evaluates. Now, let's look at some of these in a little bit more depth. Here's just for your reference, if I want to create a time or date and either hard-coded or dynamically
build it in M code, we have these date
and time functions. How about catching errors? This gets to be a
really important one. Let's say that you
have this date. What I wanted to demonstrate
here as you know, we have some dates over here. With these particular dates, what you can see
is February 30th. Hey, that's not a
date. Cinco de Mayo, it can't figure that out. Let's say we take this
approach of the first one. What you can see in the first one is that we're just saying take the raw date and convert that into a date and we're
getting errors here. However, if we look
at the second one, and now we're going
to have a try-block where get the date, if there's an error,
just leave it null. They're not the same. You see the difference you
have between date one, where I didn't have
the error-checking and date two, where I did. The try otherwise, is basically the main way that you're going to deal with catching errors. Let's move on now. We're going to look at some of the richer data structures. If you're going
start programming M, this is definitely a very
important part to understand. >> Hey Ted, we do have
a couple of questions. Sorry for interrupting here. But we do have
couple of questions. One is, should we write output or var4 at the end of
the Hello world code? >> Either one will work fine. >> Then do you have a guidance on best practices for
writing DirectQuery code, and if so can you put the
link in the chat for us? >> I do not. I
can't think of any. Most of what we're looking here, is going to work with import, but your M-code is very
limited in DirectQuery. In fact, all you're able to do is write M-code that they have to eventually translate into
SQL that runs at runtime. Once again, it's a lot trickier to work with M-code if you're
not using import mode, if you're using DirectQuery
mode. Good question. >> Thanks Sammy
for that question, and then Danny said would like to know your
thoughts on when to use a merge query versus
join in data models. >> Well a merge query is
a join query, basically. Merging is generally done when
you have the primary key, a table with a foreign key and you want to merge them together. I'm not really sure what the
difference is between those. >> Okay. Thanks so much Ted. I just wanted to strap
off to [inaudible]. >> Okay. You can
just turn and get the questions out of
me when you need to. You know how to do that. >> Okay. Thank you. >> With lists, you can say that if I wanted to create a list and grab data out of it, there is syntax for
being able to do that. Look down here at the bottom. Let's say that you've
created this array and the elements go
zero, one, and two. If you say RatPack four, that's basically
going to be an error, and it's out of range here. However, look at
this cool syntax. If I put a little
question mark afterwards, this is somebody to say if there is an error because
it's out of context, just return a null value. Once again, it's important part when you're writing your
M-code to be able to do that. Now, here's something that Chris Webb came up with
and I steal from him, but the idea is, let's say that you have people putting
comments on your website, and what you'll want to
do is you want to harvest these comments and show them, but they keep putting
characters you don't want, so your first approach
was to do find a replace. If you find this character, replace it with a blank string. However, they keep coming up
every week with new things, so let's change our philosophy. Instead of assign what
to omit character-wise, let's go ahead and
define a list of characters that we will accept. Right here, you can see here's some cool M-code
to create a list. Notice this little
dot dot syntax. The first set is
uppercase A through Z, and then lowercase a through z, and now we need the
digits zero through nine, but we need to not have
them still be numeric. We have to convert them to text. We're going to take a
couple of other characters, look at the ampersand
here when you have lists. You can just put them into
a character's allowed. Now, let's take our
input and allowed cars, and the ideas that
any character that's not in one of those four sets, is going to be stripped
out of the text. Once again, you can see that if you get down and
dirty with the M-code, there's some pretty
neat things that you can do in terms of
data cleansing. Now, let's look at records. You're probably not going to
write code that does this because you're just going
to query a database, and it's going to give you back a table structure that has records. But just to see things
in a conceptual way, I've created these three
different records here and you can see that when
it's time to access a record, you can basically access
it by the record name and then the field name
in the square brackets. Now, one of the
things about records, is quite often if
you're going to make a call to one of the functions as part
of the M function library, they quite often make
you create a record for the particular
parameter value. Now, there is request
headers here. The ideas that when
I use web.content, I want the accept
header to go out, so the first thing I do, is I create a request
header record. Now here are the two
different request headers. Note that because this
one has the hyphen, we've got to use the
pound sign right here. But now what we're going to do, is we're going to create a
second record and now what you'll see is the request
header's record is nested in the options record
which is ultimately passed when we call web.contents. Once again quite often, whether you want to deal
with records or not, you're forced to because you have to create one to
make a function call. Now, you've seen a
couple of examples here, but the ampersand
is very flexible. We call it the
combination operator, because it works with text, it works with lists, and even works to merge
records together. So it's a very flexible thing. Now what we want to do is let's
look at creating a table. There is a Table.FromRecords. One of the things
that I could do, is I could create
this list of records, you can see that after table
from records open paren, we open a curly brace and
that's closed at the end. That is a list, what's
inside the list? A whole bunch of records. You can create a table from
a list of records. Now one thing about this, is that look at the ABC123, your brain should immediately
tell you bad, bad, bad. What we'd like to do, is we'd like to make sure
things are strongly typed. Now, here's another example. Look at this top line of code. Type, FirstName equals
text, LastName equals text. You really define the
schema for a record. Now, I can also down here, you can see that we have table, type table, and so
here's the type for a record and
that's inside there. Now, what can we do with that? Let's look at this
example right here. One of the things
that we could do, is we could create
the table first, and once we've created the table and we strongly
typed the columns, then we can basically
add in content. Because what you can see here is here's the table function, now here's my type
and that's going to strongly type my
columns right here, and then after that, well,
hear from the records. Another common scenario that we see this used is
that there's some Excel workbook that's posted that we have to go
pull data out of. It usually has 12 columns, but sometimes it comes in
and only has nine columns, and so the query
just decides what columns are going to be there by looking at the Excel file. Then when we get
to a second file, we're trying to append, it breaks because through the
columns aren't there. Defining columns and
strongly typing them, and then appending data
from a data source into that becomes a very
common strategy. Let's look at
something else's need. Let's say I want to perform
calculations across rows. I think someone had a
question about the index. What I'd like to do is, have a sales running total. Here's our starting point. Note that if I go to Add Column, and we go ahead and add an
index column inside here, it's simply adds an index column. Now, I've already done
that so I'm going to go ahead and remove
what I've just done. But now the trick is that, if you add an index column, every single row is numbered
and that can benefit. Let's go back and take
a look at this right here and see that because
we have these numbers, we can now write M expressions to get a running
total as we go down. Back here. The way it works is that you have an index number and so we're going to
assume index is zero. We're going to have a list range. I want all the records, starting at zero and coming
up all the way to where I am, and then we'll say List.Sum. Once again, if you
want to perform calculations across rows and common things are that
you just want to look at the previous row and
see if you're up or down or running totals. I know that a lot
of this would be better accomplished by Dax code, the M-code in many scenarios, but there are scenarios where you're basically running a query, and then you basically
just want to stop the query once you
hit a certain number. Digging for data, this can be an effective way if
you're going to build a running total so that you know when you can stop
pulling data back. Now, another thing
we're going to look at, is the Each keyword. This takes a little bit of
conceptual explanation here. Many of the functions that
we would like to call, and in particular, the one
that we're looking at here, is the table row here. Sorry for my drawing
problems here. The idea is that, the
first thing takes a table, the second thing takes a
function with one parameter. Now, you can tell your friend
that you have a function with one parameter, but instead, if you tell your friend, "I have a unary function,"
they'll be like, "Oh, that sounds really cool just to function with one parameter." The idea is, when you
call select rows, it wants you to pass
a function that takes a row and then
you're going to look at that row and make an evaluation of whether
it should be included or excluded from the
resulting evaluation. Instead of using this syntax, which is completely valid, what they decided to do is to basically give us
the each keyword. The idea of the each keyword is it simulates this
part right here. When I use the each keyword, this is a function, but you don't have to worry
about the parameter. The parameter has this
name of underscore. The idea is that when
you say underscore, it is the record and then you
basically ask for a field. Now, one of the things
that they decided is that when your unary function
is looking at a record, they decided to make it
so that you can include the underscore or you could
just include a field name. Each one works just fine. Now, there's one case
that I want to point out that's a little bit
tricky and that is, let's say that I have
a unary function, and here's the unary function that we have down
here, each to upper. However, the thing I'm looking at is not a list of records, it is a list of strings. That's not an underlined record, and I don't want to pull
out a field from record, I just want to pull
the thing out. You can see that here, there is the
underscore right here. Once again, that's the way
that the each syntax works. One little gratuitous
example here. Let's say that I want to
generate a list from scratch. List generate, you
have a function that takes no parameters but returns
value for the first one, that I'm able to say, let's create a list that
starts at one and goes to 10. This would be the basis if you wanted to create something like a date table using Power Query instead of Dax or instead of in your database. It's completely capable of generating lists
with advance logic. Any questions for me, Bailey? >> Yes, we do. >> Now I'm coming back
to you for the question. >> Yes, we do. Is it also possible to evaluate parentheses, it doesn't have a hard to understand
environment constraints. Is it possible to have a
workaround so it can be used more freely so the expression don't evaluate in parentheses? >> I'm really not
sure what that is. We can look at that
question and I can try to answer later
but at this point. >> Yeah, look, can you add
some more context to that, and then recursive functions run into memory issues quickly. When pulling pages
from API data sources. Is there really no way to
have global variables? >> Now, you can't have
a global variable. You can have a
query that runs and returns a value and have
other queries look at that. But recursion is for those of you who are
not sure recursion is when a function calls itself. If you're doing
something like writing code to enumerate through a folder structure and find all the files
and child folders, it would be a technique for that. But depending on
your data source, everything has to be loaded
in memory at one point. Recursion will only go as far as the memory that
you have available. But it is possible, but very tricky to use compared to the using it
in a language like C#. Let's look at query folding. Now, the idea of query
folding is that you want the Power Query Mashup Engine to be as
efficient as possible. The Mashup Engine will push
work back to the data source. Meaning that if I have
a 100,000 records, I don't want to pull
all the records to where the Mashup Engine is and then filter for just the customers
in Rhode Island. I want to send the Where
clause for the back-end or I want to send the Order By
clause for the back-end. You also query folding is
all about writing queries, so the Mashup Engine, when it calls to a database, it can add the Where clause. It can select just
the columns we need. Maybe even rename columns, maybe tell the database to do the sorting so we don't have
to in the Mashup Engine. Query folding is mainly support
for relational databases, tabular and
multidimensional databases. Also, if you're calling
the OData web services, that also support some
level of query folding. But basically, whatever connector you're using supports query
folding or it doesn't, and that's something
where typically it's relational databases or OData. Now what happens when
the data source doesn't support query folding,
it's not as efficient? It's got to bring all the data back and
then do all that work, wherever the Mashup
Engine is running. Now, what affects whether
query folding occurs? Definitely the way you
structure your M code, also privacy levels
of the data source, and also native query execution. Let's look at this.
Imagine that I have this particular query. Notice that I go to a database and then
I filter the rows, I select the columns that I want, maybe I'll even
rename the columns. What happens is that
the Mashup Engine is smart enough to take
everything you want there and basically put
the logic for getting back the data just as you've asked for it in a single SQL statement. However, you have
to keep things like the table select rows and tables like columns very
close to the source. Because if you put some steps in the middle where it
needs all the data, it's not going to be able
to do the query folding. You can also use something like the SQL Performance
Monitor to look at things. Now if you're using
native queries, here's an example of executing
a query on the back-end. What you'll see is we
say query equals SQL as opposed to up here where
we have query equals item. Here, if you want to table
this item equals customers, and it generates all
the SQL we need. Over here, with query equal SQL, the idea is that it's not going to do
query folding for you. Now, on the other hand, you probably don't need
query folding if you're able to write a SQL statement because you're going to
have the Where clause, and Order By clause inside there. Now, when I started researching or preparing
for the talk this week, I thought the slide
would end the topic. But this weekend I was reading our good friend Chris Webb
and so one of the things that they've done now is they've
introduced the ability to run a native query and to basically
also get query folding. But unless you use this
special technique right here, you're not going to get any
query folding now. Questions? >> Yes, we do. We have updated question here. Luke said, "The
Salesforce Objects Connector supports
limited Query Folding. It produces the appropriate SOQL when the developer
applies filters. It would be fantastic
if we could write our own custom SOQL." >> That's right. I just don't
know enough specifics about the Salesforce connectors to
be able to comment on that. Once again, whether Query Folding is deported to any degree and how it works is really a characteristic of the
underlying connector. You can't really make
general rules across connectors as far as how Query
Folding is going to work. Using that special query language for Salesforce would be great if there
connector allows it, but I'm sorry, I just don't
know the details of that. >> Thank you. >> Now, when you
start working with M, one of the things that
you'll want to do is basically figure out what are the functions that we can call. What I'm going to do
here is let's say that, here's a reference to the
function library inside, and there's just tons and tons
of functions inside there. In fact, let me come back
here for one second. This is not the way you should do a
PowerPoint presentation. But what I' going to do is, let's go ahead and just
follow this down here. Just wanted to show
you this resource. What do you want to use? I want to use one of these particular functions that does this or that, you know, so if I go look at things like the list function
values, splitter tables. Once again, this gives you just a really good
exhaustive list of all the different
functions that are available. Now, what I want to look at in this next section
is let's compare two different functions and one is going to be using OData. Let's say that I have
this website right here. If I go to
http//subliminalsystems.com/api/Customers, I can basically get data back. Now, what we can do is I could go into the
query tool and I could say, I would like to get
this data inside here. Now this, since it's
going to OData, OData has a discovery mechanism
where now Power Query is looking at the
table and figuring out what it is and now I can
basically, bring it in. What I want to do is I want to compare two
different approaches. The first one is
going to be OData. If we look at this OData
example right here, let me go back to view
and open this up. It basically is able
to get this and then go through and
deal with the data. Now, the problem with
OData is that at runtime, it has to make metadata calls to basically get the metadata and that makes the second call. Some people, instead
of using OData, would rather use working
with web contents directly. Now, let's go back here. So here's web contents. What I'm going to do
it, let's take this and I'm going to go
ahead and duplicate it so I can make a
point inside here. In fact, down here, if we take a look at this. Inside here, what we're going to do is we're
going to create source. Because this is a
RESTful Web Service, I'm going put together the URL, so I don't need Query Folding. Web contents does not
support Query Folding. But the idea is that
you could create a URL, especially an OData URL
that uses things like dollar sign select and
dollar sign filter. Now we're going to
get our data back. Now, let's go back here. What I'm going to do
is let's go ahead and delete everything after this. So let's say that we've used WebdotContent and we've gotten, I have mess that up by not
saying delete until end. Now where would we want to be, here's this point where with
that particular source. Now we have this
particular record. I'm going to do the demo
one more time. Forgive me. Now JSON Document,
that's what we want. So what's going to happen? Let me go ahead and
delete this now, is that when you use the Webdot contents and
you have basically, a URL comes back or you have adjacent
document comes back, Power Query doesn't really
know the structure. While it's more efficient, it's going to be a little
harder to deal with. Because now I have this list. What does it? It's
a list of records. Once I get to the
list of records, now we're going to table. Now that I've gone to table, I can go ahead and expand the columns and then go through and set the
column names here. One of the things that
I was trying to show is that this is a
harder thing to create. But it can be more efficient because it's able to
pull all that data back in a single call as
opposed to the other one. Let's see if I can demo
this a little bit more. If I come back here and
can spring this up. Give me one more second as I
demo this to try to demo it correctly and remain
attaches to Web Contents. Now that we've done that, let's go ahead and Enable load. Now we'll go ahead and close and so what I wanted to do as we'll bring out
this tool, Filter. If you don't know about Filter, it's definitely a fun
tool for developers. But the idea is, if I come back here and we
look at the OData feed. Now what I want to do
is refresh the data. What we can see here is look at all these calls that
OData's making. In fact, it looks at different metadata endpoints that aren't even supported
and when those fail, it finally comes back here. Now if I compare that to
a call to Web Contents, boom, one quick call
across the network. Once again, the OData
connector comes with a price. It's nice in the fact
that it figures out what the shape of
the data is for you. But those extra calls, if you're looking to optimize
things might be helpful. Two more topics. Let's
roll through these. Okay, Kelly, we can roll through and then hit
questions at the end. >> Sure. Okay. Great. >> Let's do that because then I can finish on time and if we go a couple minutes late for
questions, that'd be good. >> Great. >> Great. Now the next
thing we want to look at, is a function query. Now the idea of a function
query is that you take a query and you parameterize
it to make it reusable. Let's look at a scenario. Let's say that here I want a function query and
earlier I showed you the code to clean text. But now what's going to happen is when you want to
create a function query, you really just open the
Advanced Editor and you put a parameter list of one or
more parameters input as text, and then you use the arrow. Then you can just have the
let statement afterwards. The idea is that this
is a reusable function. Let's go down here and
I've got comments. What I'd like to do is
let's go to column. Let's go to Invoke
Customer Function, and so this will be something
like cleaned comments. >> Comments. What
do we want to do? We want to call the function
query called clean text. What do I want to pass? I want to pass this
comment to it. The idea is that now I can go and start stripping
characters out. The idea of clean text, is that you'll create
a reusable piece of mLogic that you can reuse across many different
queries in this project, and you could also take the same function query
and use in other places. So just gives you a way to modularize and reuse
your functions. Once again, creating the
function query is as easy as creating
the query at first, and then opening up the advanced editor and basically adding a
parameter up top. Then somewhere inside, you also have to use the parameter. What I mean by that is that you have a
parameter defined here, but you really have to use
the parameter somewhere in the query function for it to
have some particular effect. You've seen me walk through. Once you've created
the function query, being able to call it, to basically do some
processing on a column. We're into the home stretch now. Let me get through this
section once again and then we'll take all the
questions that we can. Last thing we're going to look at is designing query parameters. What is a query parameter? Some people call them dataset parameters, kind
of the same thing. But the idea is that parameters
which change between different projects can be added at the query level
or PBX project level. Now, when you want to
create a new parameter, you have to go to the
Power Query window. You'll see that if you're
at the other window, you can't create
parameters mirror. You'll have to come here, and then you're
going to be able to open up the manage parameters, and start adding
parameters inside there. Now the idea is that let's say that I added
a parameter name state. Once I add a
parameter name state, if I don't want to
write M-code directly, there are ways that you can
reference the parameters, just in many of the dialogues that the Power
Query designer has. But also, we can use
them directly in code. If you don't have a parameter, you can use those
values directly. For instance, let's
say that I had a parameters for
the DatabaseName, the DatabaseServer,
and the state. Those are common examples of
why you'd have perimeters. The idea is that I have a generic database and 100 of my customers
have the same database. I'd like to create a project that once I push it up to the service, I can just go change the
parameter values to point to a different database and maybe do some
different filtering. These parameters can be
updated in the service, or they can also be updated through PowerShell or through the Power BI service REST API. They give us a lot
of flexibility. Another thing that not
everybody knows about, is that if you
create a parameter, you can actually use it inside of a report or inside of DAX. What I mean by that is, let's see a simple example. Let's say I go back here. Would go to manage parameters. Then I have something
like report name. What is this going to be? When you create parameters,
don't leave them as any. Strongly type them. Now we have my report title. Now that you've done that, I'm going to go down into
this query right here. I'm going to right-click on it. Note that when you
create a query, enable load is set
to true by default, and you have to turn
it off if you don't want it. Parameters
is the opposite. Enable load is
automatically disabled. But if I create a parameter
and I call enable load, and now I go and
push it inside here, what you're going to see is, "Hey, there's the report name." Now I could do something
like take a card and put that report name on
that particular card. Maybe I'd go ahead
and swap that out, so it doesn't need a category
in place right there. The idea is that now I can go back to
the parameter settings, and set that to my
other report title. Once I do that and
apply the changes, we should be able to
see, sometimes it takes two refreshes before you
actually see the changes there. But now, you've got to see that once you create parameters, you can use them in your M-code. You can use them
in your DAX code. You can service them
on your reports. One more topic here.
We've got to go quick. Once you create templates or
once you create parameters, sometimes it makes sense
to create a project file. Let me show you one more example. There are three examples. The one I want to show right
here is the PBIT file. The idea of a PBIT to file is that you have a
parameterized project, and then you export
it as a PBIT file. It's not useful in and of itself. We use it to create new projects. When I open this right here, the way I've designed this is that you can pick which
NFL team you want. When you open it and
it has parameters, and they're basically
creating a PBIX file from a PBIT file, it then is going to
make you pick a team. Let's say that I
picked the Buccaneers, and now we'll go ahead. Now from the logic I have, it's able to find
the homepage for that particular team.
It has a roster. It extracts the data. Now this one, I'm going to
bring up this view right here, and just show that this is just, for people that
really want to get their heads wrapped around how to design with M. What you can see here is that I have
things like the team details. I have the teams themselves. Here's an example where
I just hard coded every team and also the HTML file
for each of these teams. Then what happens is that
we have a parameter value. If I come back here and we take a look at the
parameter window, we just have one
parameter which is team. I've created a list. Once you create a team list, you can use that. Once again, this is not a table of one
column. It's a list. What is neat about this, is that if I go back here
to manage parameters, I can say I would like
a list. What type? I want to take this list. Now that's how we were able
to give them a choice. Then once they pick that, what you can see here is
for the team details. Now given the fact that
you've picked a team, I can now go and pick things up around other things inside. Then we have something that
is going to actually do the extraction from
the individual table. Once again, what I'm
trying to show with that demo is how you
can create parameters. Then what gets to be common is to parameterize the projects. You can create many
different PBX files that use the exact
same data model, query logic, and report. We just swap out one or
more parameters to point them to a different database or filter a different set of rows. That's what takes us to
the end of our session. Now, Miss Kay, if you would. >> We have some questions. Thanks. That was amazing. We do have some
questions from people. I'm new to this. Is this something I should know before learning
about query folding? >> That might have
been contextual of if it was asked
at a certain time. Well, query folding was one of the things that we
taught here today. I think as you learn
about Power Query, even whether you're digging
into the M-code or not, you should understand the
basics of query folding, if you're going against
regular SQL databases. If you're pulling back data
from an Excel workbook, the other is no query folding. So depending on your data source, if your data source supports query folding and you need to write queries
that are efficient, yeah, you certainly need
to know the concepts. >> Awesome. Any tips, do both have the
same performances? Basically should we do the query folding in
the applied steps or move all of it in SQL? Do both have the
same performance? >> Well, in general, I think the creators of PowerQuery really don't
want you writing SQL. They want you using their tool and giving them the freedom to write what they feel
is the most optimal SQL, and the exact same query
when it was run last year verse today versus next year might result in
very different SQL. There are some situations where they have a
report and they have some SQL statement
that somebody has been writing and maintaining over 10 years and there's no way
we could redo this in M, those are typically
the situations where I just take the SQL statement that the ID target gave us and we put it in and we get the data
back in the right shape. It's not a sin. Each case, you should make a judgment of whether SQL statements in
there is reasonable or not. But in general, I would
start without using SQL. Then if you want to use it, you have to justify
why it makes sense. >> We are out of time, and is there a place
where people can contact you for the rest
of the questions? >> Yeah. I think at Ted Pattison. My Twitter handle is fine. >> A-T-P-A-T-T-I-S-O-N. Twitter. I think we're going
to call it right now. Thank you so much again, Ted, for an exciting, fabulous,
informative session. We look forward to next
month's and please look at the Power Platform and Power
Platform YouTube channel, on to PowerBI because
we'll be posting those by about Tuesday or Wednesday
next week the entire series. We look forward to
seeing you next month. Daniel, our producer,
thank you so much. >> Thank you. >> Thank you, Daniel. Thank
you, Kelly. I appreciate it. >> [MUSIC]
Would be awesome if they could compile this Dev Camp series in a YouTube playlist