good morning everybody my name is Matt
Mason here today to tell us to talk all
about to the M language which is now its
official name by the way no more power
for a formula language it is now
officially called M yeah so I do have a
deep dive top that I usually do the REC
ording is online of it from ignite
Australia was the last time I did it
it's the full 75 minute session today I
decided to do a bit of a pivot on that
talk so instead of just focusing on M
I'm going to show an overview of M and
sort of some of the key things and then
drill into building a custom data
connector using M and so I'll be using a
mix of Visual Studio and power bi
desktop to show you how to create a
custom connector and sort of talk about
the M language as I go through it so yes
agenda today I will cover just go over
data connector extensibility is one of
the things that we launched last month
we now set up build lots of people very
excited about this because it's a way
for you to create new data sources for
power bi I'll side with the end language
and then we'll go actually demo creating
a data connector from scratch which
breaks one of my personal rules about
never writing code up on stage so we'll
see how this goes my assumptions for you
guys that you're familiar with power
query you've seen it before you sort of
understand the concepts of connecting to
data and doing the manipulation you know
that there is a language called em
behind power query and that you are
comfortable seeing code and formula I
won't have pretty visuals there will be
a lot of code and individual studio and
if that's going to bore you I'm sorry
but this is what we're going to do today
okay so quickly about data connectors
like I said this was announced that
build last month
we're not the Developer Preview we've
announced and we have a full public
preview in power bi desktop the current
build for the June bill that just came
out it's actually a preview switch that
you can turn on and what custom
connectors allow you to do is create new
data sources for power bi for power
query all right so you can yeah expose
new data sources
most commonly what people have been
doing what we've seen this used most for
is connecting to REST API source a
services right basically providing a
nicer interface than making a raw web
request and getting back Jason and
having to manipulate it what a custom
connector allows you to do is present a
nice navigation table over the source
format the adjacent response or XML
response in a way that makes sense to
the end-users apply schema and
formatting and do all that stuff
automatically within a custom connector
custom connector is actually written in
M so it's just like using power query
you can think of a custom connector as a
set of saved queries almost for doing
manipulation okay another reason people
have been using custom connectors is to
implement custom off loes it's one of
the challenging things to do today in
power bi or in power query different
websites will use oauth2 they call it a
standard but really every site does
slightly differently and what this model
allows you to do is implement those
flows within em using it as a building
block for power bi apps and content
packs my colleague Youssef has a session
right after this it will go into
creating content packs and focused on is
V is creating public content packs and
typically most of these sites and
services require a custom connector to
do the custom off and formatting and
things like that and finally this is
more of an advanced scenario that I
won't be covering today but you can use
an M connector to wrap an ODBC driver to
enable direct query capabilities I know
if you guys saw the zoom data demo
yesterday but this is exactly what they
did we've been working with them on that
where they basically have an ODBC driver
we have an EM wrapper on top to
customize the behavior of the driver set
different capability flags that can't be
set automatically as part of ODBC and
basically enable direct query for power
bi oh that's a lot of things you can do
with a data connector so roughly how it
works you have a data source that you
want to connect to in power bi and the
built-in functionality the built-in base
functions maybe don't provide the best
experience for you so use a combination
of
Visual Studio we have a new power query
SDK which is an add-in for visual studio
2015-2017 you can download from the
store or sorry from the vs marketplace
and use a combination of vs and power bi
desktop to iterate and author this
extension and basically the demo that
I'm going to show today after that you
get an M extension so this file and that
file can contain your your logic your M
queries icons
branding strings the visibility creates
as branded experience and branded
connector you then install it into power
bi desktop it automatically shows up in
the get data dialog like any other
connector and then other people can
consume it and that's pretty much how it
works and what I'll be showing today so
one of the core reasons why we did this
will M extensibility thing was that we'd
like to leverage common data API as much
as possible right so if you have a fully
full-featured data API like Oh data for
example your connector becomes really
simple it's just a couple of lines of
code and then icons and branding you
basically point to your odata service
and we automatically get things like the
schema and the navigation table and
query folding and all that stuff you get
that all for free by being based on the
M language another wrapper approach is
over an ODBC driver same we can ask an
ODBC driver
what are your capabilities what what's
your sequel syntax and all the different
things we can automatically determine
from the driver so at the M level we
know what sequel to generate and pass
down to the underlying data source right
and then the M wrapper on top lets you
do additional customization of the
sequel that I could generated and things
like that and finally you can create a
pure M extension so this is what's
commonly done for SAS services or rest
services you're basically using built in
M functions like web content to make the
web request and then
JSON document to parse the JSON into a
structure and then format it as a table
and return it and one of the things I
love about M and the extensibility
mechanism that we have is that it gets
incrementally more
feature-rich I want to say difficult it
gets more feature-rich as you go so you
can create a really simple connector
that just connects to a site returns as
a JSON but you can incrementally add
more capabilities like a navigation
table like passing down parameters and
filters that in query editor if somebody
says you know filter on country your
expansion can get that context and
figure out how to pass that country
context and the country filter down to
the backend datasource connectors don't
necessarily need this so it's very easy
to get started and you can incrementally
add these more advanced features as you
go all right
and so you see samples and documentation
we're still working on getting the full
range of documentation out but at least
in theory you should be able to create
an EM connector that does full direct
query capabilities over a back-end data
source being able to handle all the
different types of transformations that
are possible in analysis services and in
power query and be able to pass that
back down to underlying data sources
with custom query languages so it's a
very powerful model but yet simple to
get started with right so we're in
preview today by the time we hit general
availability we're hoping to have
following things integration with the
office store sort of like what custom
visuals has done with power bi today the
story is you have your M extension and
you email it or send it to somebody and
they just manually copy it into a
certain folder and load in the future
we'd like full integration with the
office store so developers can share
their extension in the office store and
you can just search and acquire directly
through there today the extensions only
work in power bi desktop but we're
working to enable it across all power
query experiences like Excel 2016
ssgt for analysis services tabular and
all the other places that power query
will eventually show up power query is
taking over the world
we have support for the gateway so today
it only works in the desktop I
definitely need to enable extensions at
the gateway so you can do full scheduled
refresh and direct query and then better
development experiences and
also versioning and dependencies
ultimately what we'd like is if you open
up a power bi file a GPX file it says
hey this is a custom connector would you
would you like to download it from the
store and will automatically go and
download that as well as any
dependencies that it might need so
automatic tracking same as the Gateway
you go to refresh something in the
Gateway will automatically download all
the expenses that needs from the office
store since great so let's go over the
EM the language really fast and then I
will go into my demo
so at a high level of design tennis for
MI like including this as Liam was m's
been around since about 2006 and I've it
erases on and changed over time the most
the current incarnation of mmm was
really meant to power the power core
experience in Excel and so to suit that
audience they made em a functional
language based on formulas it's easy to
write easy to read there's not much
syntax involved it's basically a formula
language right it's really targeted at
the advanced intermediate to advanced
Excel users and they kept a lot of the
concepts the same between Excel formulas
and Dax and M but just different enough
to make all audiences unhappy like zero
indexing rather than one indexes and
case sensitivity and things like that
but um anyway so we are where we are and
I like em the way it is so general
language flow for M evaluation flows
from one step to the next except for
when it doesn't and so the results of
the previous step or you typically use
in the next step so call this out
because this is the way that power query
will generate M right obviously you can
see that but basically you have that
first line you're connecting to a web
page parsing it with sorry are connected
to a web page with web contents you
parse the results of web page that turns
it into a table with all the tables on
the web page I indexed into that table
to pull back a specific table sort of
that is the web table variable and the
finally I passed web table into a
function called table rename rename
columns which is going to rename column
one to rank and call
- to 2013 I say you see this flow from
one step to another and that matches the
way the power Cory experience works
where I do incremental step by step
transformation and it can drill back
through and see all the different steps
now although power query generates
things this way when you're writing em
by hands you're not limited to the step
by step saying so for example in this
bottom thing the first line I'm
connecting to a sequel database during
that this source variable and then I use
that source variable three different
times to pull out three different tables
from that database I pull product
category subcategory and then customer
and store them in other variables and
then on my last line I do a nested join
joining subcategory and category on
category key and returning that is
joined so you notice that dim customer
is never actually referenced and that
means that line will never actually be
evaluated so M is lazily evaluated all
right so I say it flows from step to
step but it's actually the opposite it
pulls on data so I'm looking at this
evaluation plan it will look at joined
and to get joined I need to join these
two tables and so we'll evaluate those
things it sort of goes backwards kind of
like a T sequel query plan right but if
there is a item that is never evaluated
or remember I looked at it just get
stripped out and never actually run this
can be interesting when you are doing M
extension development because if you do
things like logging for example and you
have a logging line and you're not
actually using that logging variable
that's being logged then it never
actually gets emitted and it took me
actually awhile to figure that out M
types M tries to keep a very simple type
system there's a small number of types
you'll notice say for example there's a
number types there's no in 32 Biggins
double float it is at the primitive
level there is only a single number type
right so all the types these expect as
well as complex types so M natively
supports the concepts of records lists
tables and functions as types and values
that can be passed around so example you
examples you are setting types on
function parameters which is something
you do very often when you're writing a
custom extension so I have my first line
there server as text so I'm saying that
that parameter value has to be text M
engine doesn't do runtime type checking
except for on parameter calls and some
other cases so there's also concept of
ascribed types and I have this blurb of
text at the top here from the M language
spec basically you can think of an
ascribe type as a format right
underneath the M engine only understands
as primitive types but you can say this
number is actually an n64 type right now
for the M engines point of view it still
thinks it's a number it doesn't care but
for things on the outside like analysis
services like power bi desktop it can
ask the number o are you actually an n64
and it might make a decision accordingly
all right so in power bi desktop and the
data model it will store columns
differently based on their their types
let's say there's a concept of ascribe
types which still kind of hurts my head
when I think about it but yeah think of
it as a number format okay
examples of simple values an M the most
interesting things here would be the
Constructors so the hashtag date hashtag
time and date time these are simple
constructors to create these types
symbolic operators so all the operators
that you'd expect but there's a couple
of things I want to call out
concatenation is interesting
so in M you can concatenate values with
the ampersand other languages it's plus
here it's ampersand and you can
concatenate text and text lists and
lists records and records tables and
tables and date and time what are the
only combinations that you can combine
if you try to concatenate a date and a
number you will get a cannot convert
number to text error you have to wrap it
with a number to text function I'm sure
she lost people nodding heads you might
have hit that in power query before
other interesting things are the field
access I don't think power query ever
generates this syntax but basically if
you're accessing a field of a record
you include a question mark without the
question mark and if the field doesn't
exist you get an error thrown if there
is a question mark it will return null
instead so the Basel II conditional
access same things were when you're
doing lookups and lists and tables that
works the same way let's talk quickly
about the structure of the complex
complex values and types that's scary
okay complex values and types as the
lists are with curly braces alright so
you define a list and put a bunch of
items inside of it lists can be types or
they can be just typed to any by default
they can have any data type inside of it
records have square brackets this is the
exact opposite of jason which uses yes
the opposites again we've tried to make
people unhappy with this language syntax
but yes M records have square brackets
and the format is you define a field and
then a value and again this has a record
with name field children field which is
actually a list and then a happy field
which is a logical value true and then
tables you can actually define tables
with the hashtag table constructor the
format of defining the table is the
first parameter is a list of text fields
that become the column names and then
you have a list of Lists inside with
each list being the row values right so
that's what it comes down to some other
syntax for complex values you can have
functions functions are a type and a
value so you can actually pass functions
around as a parameter you can declare
new types for example if you're typing a
table or assigning types to the table
and there's actually a binary there's a
whole binary library and you can parse
raw binary files the demo that I used to
do with parsing header information out
of a PNG file I've seen some very
interesting demos or script show up in
the wild about parsing zip files or xlsx
file there's some very interesting
things out there lots of clever people
out there we're doing things with binary
one of the core concepts in M
very important when you're writing a
custom extension is thinking in tables
everything that's a table you always
have to keep this visual mental model of
the data that you're connecting to and
how you want to manipulate it and I'll
show that when I'm writing when I'm
writing the extension I just want to
show a couple of slides about what a
table actually is and how all the types
sort of fit together right so the M at
the top here this is something that pyro
query would generate in this case I have
a file contents file that content is my
data source function it's going to
access a file it's going to load the
sales
xlsx file and return it as a binary
which is going to be parsed by the Excel
workbook function Excel that workbook is
going to take a look at the binary parts
that as an excel file and return a table
that contains where each a table of
tables the navigation table and each sub
table is going to be a sheet on the
excel from the excel workbook I save
that's a source and then source and then
iterating in this is basically a search
a lookup where I'm saying from that
source of from the navigation table find
me the row where item is equal to orders
and kind is equal to sheet and then I
want to access the data field from that
row and I store that as my table and
finally I return my table which is the
view down below basically how the M
functions work so what is a table if you
look at a table a single column in the
table is a list all right so if you
refer to my table that product key you
basically get a list of all the values
in that column a row an individual row
is a record in the table so if I index
in to pull the third row right where 0
index based if I pull the third row from
the table I get back the following
record and a table is a list of records
all right so is yeah the table is
basically a record for every row of the
table
ask em concepts I want to talk about
this took me a while to wrap my head
around to do this is one of the only
places where we have the special
syntactic sugar for each each expression
you'll see this in power quarry often so
a lot of library functions on em take a
single parameter so for example the
table dot select rows function right
what it does is it will go through all
the rows of your table and then select
the rows that meet the criteria that's
specified by this function so what this
is saying is basically it's passing the
table is my variable R is going to be my
row and row goes into a function that
checks where the manager field is equal
to the buddy field and then if that's
true it will select that row so what
they've added is this special syntactic
thing called each which basically
replaces that R goes into syntax right
and then you have underscore to
represent that that parameter has passed
in so in this case the row and then
finally for conciseness and to make it
closer to the deck syntax you can
actually omit the underscore it's sort
of implied so when you do each manager
equals the Buddy that means for the
current row compare manner JH equals
buddy so all three of these statements
are equivalent they're the exact same
expression they evaluate to the same
thing which is different ways of writing
it you usually don't have to use the
underscore but you might have to when
you're writing custom code because for
example if you want to call another
function and pass that row value in
you'd use the underscore to pass that
through ok so that's first pretty much
the only complicated part of M I'll go
into my demo I was really happy about
this demo so this demo is about
connecting to a Web API you know common
thing my the demo I decided to do is I
have a big problem where I have so many
games on Steam PC games and most of them
I haven't actually played yet and
ultimately what I want to do is do cross
look up so I could see the ratings of
the games sort by games I haven't played
yet and highest rating for a noble game
to play next alright so you know very
important problem I was happy with the
demo until I sat down with people
yesterday who are doing AIDS research to
identify
children that hiv-positive and need
medication and they needed a custom
connector and it made me feel very
superficial and shallow so I spent the
day working with them on that custom
connector okay so this is my demo like I
said I usually have a personal rule
where I don't write code on stage so
we'll see how this goes
actually before that let me talk about
how I set up the demo so steam is a
platform for PC gaming they have a Web
API a REST API that you can call there's
a specific method that I saw that called
get owned games that will give me back
my list of games that are purchased
basically the the process I went through
to connect to this API is the same that
you'll go through I find the API online
I read a bit about the dock so check out
okay what's the authentication in this
case I just have to pass in an API key
on the con part of the query string I
read about the return values in the
format and then what I need to do so
basically what I had to do was go to the
scheme site sign up for an API key
developer account so now I have my own
developer key I know to call these
methods I first have to get what's
called my Steam ID I have a profile name
I need to do a lookup to get the ID that
they use internally and then I'm going
to call that get own games function to
get all my games and then have to
process the Jaison result if I were to
extend this demo I could do the lookup
of ratings and things like that I won't
have time to cover that today but I have
a post all this code is up on github if
you're interested in checking it out for
yourself so let's go over to this like I
said the first thing I have to do API
wise I'm going to have to look up this
steam ID before that when I set up my
machine there is a github site Microsoft
data connectors github repo which has
all of our documentation on how to get
started with data connectors you can
check that out in Visual Studio I
downloaded this power query SDK which
adds M syntax highlighting and
telephones the autocomplete compilation
everything like that to Visual Studio
which is what I'm going to use actually
develop my extension
so here I'm in Visual Studio 2017 going
to create a new project you see that
I've installed the power query SDK I
have an option to create a data
connector project is going to give me a
skeleton data connector okay that's able
to connect to a data source let's call
this scheme it's going to give me if I
look at my project doesn't let me zoom
over there okay if I look at my project
it's going to give me 2 dot PQ files one
is going to be my extension which is
basically I'm going to turn into my data
source and then the other is a test file
that I can run in visual studio to test
out my code so I don't have to always go
to power bi desktop so let me make this
so you guys can see that great so the
default template that it gave me it's
basically a hello world if I ran this
would just give me a simple message I'm
going to make some minor changes is
basically declaring my datasource a new
data source called steem I'm saying
authentication is explicit because for
scheme it's I can access as anonymous I
just have to pass in the API key so I'm
going to leave it as anonymous I could
pass in my profile ID as an argument
should I which would send a prompt up
for me but for now I'm just going to
hard-code what my profile is this is L
it's my gamer name and that will be fine
and so if I just say source is equal to
profile extra semicolons source okay
the people who know m-might are from
power query i'll call it a couple of
things when within power query where
you're authoring is an expression it
starts with a let and ends with an in
and in is can be thought of as a return
statement so an in is actually an out
great
you'll notice semicolons there are no
semicolons in power query because the
format of this often is called the
section document and the section
document has a bunch of statements it
doesn't have to be each of those
statements is an expression so it's
formatted slightly differently that's
pretty much the only difference and then
I have this keyword called shared and
what shared does is it marks that
function as something that should be
exported all right so then that's how
the power query engine knows which of
those functions in the file it's able to
access and they able to call and able to
expose okay so basically I've done here
is I've exposed a function called steam
contents I've associated with my data
source client called steam and then the
published thing is basically the
information it needs to register in the
get data dialog so if I run this in
Visual Studio first time I run it I get
prompted for credentials right just like
you would be in power bi desktop
I say anonymous set credential run and
now I get back my boring value I just
returned my profile name but at least I
know that it's working great so let's go
back to the things I had to do first
thing I need to do is there's this URL
that has a profile name in it and I need
to extract this value all right common
things that you'd need to do in em where
in power query in this case I'm just
going to write it so let's call this
gets profile ID cursor I get scheme ID
equals and we'll pass in the profile of
text and I will say source is equal to
web contents and I'm going to pass in
the string in this case I will
just concatenate string
and then return the value whoops and
source and I know this is returning an
XML document so I'm just going to call
the XML tables function over that to
parse it okay now when I run I need to
update my actual function to call it
that helps get scheme ID and passing
profile so now when I run it I get a
table of the XML all parsed remain so
this is a table so I'm going to want to
do is iterate into that first row and
then grab that steam ID value so I'm
going to need so let's do that so I will
say first row is equal to table dot
first I can do actually do first value I
for that I could do that that's exactly
what I want
source isn't intellisense and syntax
highlighting wonderful yes
great now I have the value that I want
okay so that's going to be my Steam ID
okay next up looking at the Steam
developer API this is the call that I
want to make it shows an example URL and
all the parameters that I can pass in I
have a little cheat sheet here because
there's a couple of things that I need
one I have to pass in my API key I'm
going to pass in the steam ID which I
already have and I want to get my API
key so I have this helper function now I
could hard code in my steam ID house
where I took hard code in my API key
into my into my file and that that's
definitely a valid way of doing it but
instead what I'm going to do to make
this more reusable is I have this API
key file all this is is a text file with
my actual API key
because it's being recorded I won't show
it on screen otherwise I'd have to
regenerate my API keys and then I have
this helper function here that's going
to call the special method called
extension dot content and what
extensions that contents lets you do is
load files from your extension
so basically when I build this extension
it's going to create a dot m ez file or
meds file and that meds file is
essentially just a zip with all the
contents of my project inside of it all
right so I can load any of those files
as a binary what I do is I load it and
then convert it to text that will give
me give me my API key one thing I need
to do is set the build action of that to
compile so that it gets included in side
of my meds file and then I'll call it so
API key is equal to load from resource
and pass in the name of my file API key
great I'm my API key and what else do I
need I want to call this so let's create
a new function let's call it skits
I'm in game and I will pass in my Steam
ID as text this is the format that I'm
going to want so say source is equal to
web content so I just paste that in
there's a bunch of parameters here now
rather than hard coding in the string
well web contents lets me do is actually
create a query record so I'm going to
create a record with each of the
individual parameters so first I need
key and key is equal to API key and next
up I need steam ID steam ID is equal to
the steam IDs and then one of my other
parameters I have formats and include
app info
so format is going to be equal to Jason
and then include app info is equal to
one everything has to be a string though
for this and then I can pass in query as
a parameter here again if i forget i can
you know hover over hot doesn't show i
can hover over and see the intellisense
and the tips and things like that with
the function and arguments the result of
this is going to be a JSON document so
let's just do jason document like that
in source let's run this
and again I get the same value because I
didn't actually update my function to
call it so here's the sim I'll say games
is equal to get on games and pass in my
source should make this more clear let's
call this steam ID steam ID right when I
run this I get back a Jaison record now
I could manually type the m2 expand and
do this stuff but that's not fun and
that's hard so instead when we're going
to do is copy this over to power bi
desktop to actually manipulate the data
now that I can actually get data from my
source in a raw form I'm going to use
power bi desktop and the power query
experience to format the data into a
shape that I want and then I'll copy
that code back into my extension so I
actually have this handy utility here
called auto deploy and that's going to
copy the output of my build my Steam mez
file into my custom connectors directory
so that when I build my file shows up
here and when I launch power bi desktop
it should automatically show up in the
get data experience unless I did
something wrong but hopefully it works
great more I type scheme and it shows up
connect I didn't have any any parameters
on my function so I didn't get prompted
if I had to ask for scheme I my profile
ID for example it would have shown a
profile prompt or a prompt to enter that
value I get my credential prompts here
for some I'm connecting I say yes
connect as anonymous and now I have you
know the standard power query so I have
my Jason response I want to drill into
the record I want that list of games
this gives me a list of records I
convert to a table
expand this now I have my data let's set
data types to make it nice and formatted
so my name
these icon URLs will do something with
that later from the documentation it
basically says it gives you this good
value that you have to use to figure out
what the Earle is why they don't return
the Earle I don't know but and then
finally this the last one is a
true/false or logical value I also see
that this API returns null for false
which is a bit weird so I'm just going
to do a replace values and all standard
stuff you do in power query right
there's something else I saw here some
of these values have have special
characters in it like trademark symbols
and stuff and I'm worried that's going
to mess up my report with my other
formatting so I'm just going to replace
them with blanks it's going to go
through and I think I saw another one
down here with a yeah that's the one
FarCry replace values and I'm gonna
replace those symbols well okay so I've
a set of transformations I'm pretty
happy with the shape I you know I could
do other things like making nice your
column names and stuff but for now I
think I'm happy with that
I'm gonna do is open up the advanced
editor nevermind the syntax hide it hide
anything is coming at any day now on I'm
sure that's Miguel about that and now
that I have the transformation code I'm
going to go back to here and add the
rest of my code so let's see so I just
have to fix that up I've pasted that in
great so if I run this now in Visual
Studio now I have this nicely formatted
table of data and yeah I'd be happy with
that connector but since I do have some
time let's show some more advanced stuff
and show off some more em stuff so I
said those go back to here to take a
look there's these icon Earl and logo
Earl things that are these values I'm
looking at the docs I know I have two
prepend a full URL and then append jpg
to actually make the Earl and I could go
in to here and do an add custom column
and do the calculations and it's always
easier doing things through the power
query experience rather than an M but I
like a challenge
so I'm going to just write it by hand
let's just close replace so I can type
it easier and what I'm going to do is
call to do logo first table that column
pass in the previous table this is going
to be logo URL and then I have to pass
in a column generator function now I
have a helper that is going to take that
base image URL that is in the
documentation and do all the appending
that I need based on the app ID for that
game just add this function in here and
what I want to do is so each so for each
row of my table I'm going to call them
make image URL and pass in app ID which
is one of my columns and then I have to
pass in the this is logo right IMG logo
URL and if I look above I can see that's
there let's try the right one and I'm
missing it brace and this is going to be
you RI dot type setting the type and
then we're trying to logo I'm sure that
works and if I scroll over I see okay
great at the logo URL and while I'm here
I might as well add icon and call this
icon call this icon return icon and then
replace this point great so I think I'm
okay so now I've updated my connector so
what I need to do is I'm going to close
power bi desktop rebuild my thing
automatically copy is the new meds file
over let's run this and we'll make sure
the image URLs are working I realize
that so let's connect again
remembered that I had credentials last
time so it's through let's just load it
directly the only thing I need to do in
the data model is flag that one I don't
need these values anymore so let's hide
and hide and then I want to flag that
these URLs are Oh actually yeah I made a
mistake I I added the same column name
twice so just overrode it but anyway so
I'll just use the I'll just go with the
one here I have my web URL for icon and
I'll go back to my reports and so now I
can drag on this and play time and you
are the icon whoops
do that not
did I not set that right oh that's right
image URL now I have images in create a
very useful report identify which games
I haven't played my boss is in the
audience I'm not going to show how much
my play time actually is for some of
these games okay any questions on that
before I jump back to the slide no yeah
note so the auto deploy is an additional
extension I actually link to it as part
of the getting started documentation
because I found it was very useful it's
a third-party extension on the vs
marketplace they ask that's very very
handy call out if you've tried out the
custom connector stuff in the may build
the instructions are a bit different in
June may you have to set an environment
variable and things like that in June
you no longer have to do that so all the
steps will be on the github site
question back there yep
so the question is about concatenating
and building dynamic URLs and that
failing in the service mean in the
Gateway can't do it yes we're working on
improving that so basically so the
Gateway can refresh and refresh the same
set of queries that you can write in
power bi desktop one of the advantages
of using the custom connector model so
the reason why it fails in the Gateway
is because the static analysis that we
do on the query can't determine what URL
you're actually accessing because it's
built dynamically when you create a
custom connector anything you do inside
of the connector doesn't really matter
it's completely hidden and isolated to
the connector from the gateways point of
view all your accessing is steam so you
can do any sort of Erle combination and
dynamic manipulation of the URLs within
the connector from the outside just
looks like like Steam so custom
connectors are actually a way to do this
sort of dynamic building in a gateway
friendly way when the Gateway actually
supports extensions ok great so let's
switch back I'll cover the last of my
remaining slides or two I think there's
just a summary and some links and then
I'll open up to questions so yeah the
summaries are the demo that I showed
it's creating data connectors with the
power query SDK you don't actually need
the SDK you could just manually create
these files put them into a zip rename
it to dot mu Z and it works but use
doing everything through the SDK does
make it a lot easier as we showed custom
connectors are written in em and they're
based on base M functions if you're
interested in the technical reasons why
we decided take this route more than
happy to talk about that but I think
this model will create a lot of
flexibility in terms of being able to
deploy the office Store to our services
basically making things work end to end
for end users today you switch back and
forth between Visual Studio and and
power bi desktop to do the power query
experience now that we actually have a
power query experience in Visual Studio
as part of analysis services you can
imagine that eventually the power query
SDK will have power query UI built into
it there's no switching back and forth
you can just do everything within your
development environment
actually one of the main reasons why we
decided to do an extension for the full
version of power bi desktop as full
version of visual studio rather than BS
code or some web-based experience as I
said custom connectors today are only
supported in power bi desktop but we are
working on enabling it across all power
query experiences and the Gateway over
the next few months okay so some links
for the things I showed today we have
the github repo that has all of our
samples and getting started
documentation and the technical
documentation for yeah for the whole
extensibility model that's under a kms
data connectors the power qui SDK you
can find it from the vs marketplace when
you go to extensions from within visual
studio where you can download it
directly from this website on the I have
my own github site or github repo for
power for example the steam connector
that I just created the full version is
there it actually does look up for
scores by doing screen scraping on
another site you can go I didn't want to
show that on a video you can go take a
look at the code there it's a couple of
different ways that you can do it the
deep side video that I mentioned earlier
if you look for the power of power query
at ignite from ignite 2017 ignite
Australia is there's a full deeper talk
about those end slides that I went over
and finally we're putting up a step by
step tutorial based on the tripping
odata service for creating custom
connectors and this basically takes that
step-by-step approach where you create a
very simple connector at first for OData
convert it to a rest-based connector and
then you increment you added you add a
navigation table and then you keep
incrementally adding more capabilities
to it so those are helpful links rest
remember submit submit evaluations your
some other talks at the conference up
next if you want to find out more about
creating content packs or your an ISV
you have a web service you want to
create a global power bi app or content
pack for power bi there's a session
coming up next and other great power
Corey sessions and
that's it I'll open it up to questions
I'll also be in the acid experts booth
starting at one o'clock until the rest
of the afternoon so you're welcome to
come by and talk with me there
[Applause]