Create your own Power Query Connector for Power BI & Dataflows (with Miguel Angel Escobar)

[Music] [Music] n [Music] [Music] [Music] [Music] n [Music] [Music] [Music] [Music] [Music] [Music] [Music] a [Music] [Music] n [Music] [Music] [Music] [Music] [Music] [Music] a [Music] [Music] hey good morning for me and um I think um it's about midday your time uh or is it also morning for you as well Miguel no it's basically 12:30 for me right now okay perfect excellent yeah well um thank you for coming on I good to connect with you and get you on to talk about some fun stuff in power Queer as well love this shirt by the way yeah how many years ago was that like that's that was 3 years ago was the 5year anniversary something like that I think yeah but I think that they were giving those away in I think this were probably like the the the last time that we met which was probably like 2018 I think 2018 or 2019 yeah it doesn't doesn't feel like that long um I I know your your camera setup has changed a little bit you got a drum kit and everything behind you now with uh like even even the sound dispersement um tiles yeah man that's basically like basically this is my my home studio for music and for just work as so pretty happy with it so yeah maybe it's something I didn't know about you I'm assuming you play at least the drums do you play any other instruments or yeah man like every single instrument that you can think of so guitars bass it's all in here so oh that's awesome so you can you can have like a full on like jam session with friends when you want to yeah pretty much that's that's sometimes what I do so it's it's all hooked up to my interface um so we can actually just record it whenever we want so that's that's fun as well nice that's awesome um I think a good similar to um to the other streams I think a good place to start as well would be uh giving you a chance to maybe introduce yourself to the people either tuning in now or watching in the future and kind of what your uh pivotal role has been with Microsoft and certainly with power Cy over the last few years sure so my name is me Miguel Miguel esquad uh from Panama I work as a senior product manager for moft I joined around two years ago and I primarily work on two areas one of them is the park rator so anything that you see in the park Creator um that's basically me that's what I own and when it comes down to custom connectors uh that's the other role that I have when it comes down to the car SDK so creating connectors for power Ai and data flows and for certifying those connectors I'm also U the product owner for those so that's primarily those two roles that I have uh before joining Microsoft I did have my own company and I wrote a couple of books so maybe you have read one of those books uh the first one was em for data monkey that was released in 2015 with Ken Ken PS a great friend from Canada and then also did a second edition of that book in I think that it was 2029 or 2020 something like that but it was Master your data uh with Parkway in powerbi and Excel so the the first one M is for data monkey what was that the first or one of the first like like full-on power query um books that I think it was the second I think that it was the second one so the first one that we noticed because the you know maybe it was released in in a different language that we probably didn't know of uh but the first one in English was uh written by Chris Webb which is also in marus of now uh so that was released with apress I believe uh that was 20 almost 2014 I will say 2015 something like that um and then we actually released our own um yeah that's how we started nice yeah it's like I I know way back when was I think between yeah you Ken uh Chris web and I think MK was one of the earlier bloggers but they're there was just a select few individuals including yourself who was doing a lot of those power query posts and everything and even uh um among myself and others like we learned quite a lot from you very early on So You paved the way for I think a lot of good practices and techniques with powery back when it had a lot fewer connectors and commands that were available from the UI like I remember the the stone age days where there wasn't even like a a from folder connector and you'd have to kind of write a lot of custom M code to be able to like extract in bulk from a a folder um in power pivot days yeah those were the days man uh but uh yeah but now instead of me documenting like how how to accomplish something now I have the ability to say like hey this is what we can start working on and make things even better so I'm I'm super excited to make this happen and we're grateful that I have this opportunity yeah I I I mean I feel like the the products at a good maturity level were 95 98% of what you need to do for most typical like Transformations can just be done with the UI um outside of a few extended like I know how to write mcode very well I don't honestly touch it that often outside of maybe putting some notes in there every now and again uh just because so much of the command interface now just gives you access to everything and it's faster to do a couple clicks then to write a few lines of code yeah it it's definitely even in some some cases it's always like a mix of those so maybe you actually start with the UI and then you just modify a small piece of the actual code honestly yeah yeah and that's basically what we Ken and I actually started doing uh we didn't really try to teach you too too much about m m language in that first book it was more about understanding the overall concept of the M language and how you can take advantage of how the UI tries to create the M language or the M script for you yep no that that that's a good point I think one of the few places left that I still do it is either if I'm creating a custom column or I start with like the a conditional if statement but because it's a it's a multi-part modifier that requires an and or something in between each line then I'll go and kind of I'll use that to create the template just to write half the code for me and then I'll just go and modify and kind of copy paste a few things in there definitely and that's one of the areas that yeah we can definitely improve on and show you a builder uh basically this dialog where you can have the an or or not and that sort of aers uh like conditions between conditions yeah but today right now if you want to have that sort of uh logic it is possible with M code only EXA you basically just you write your own calcul custom column um rather than than using the UI but uh progress slowly and surely with all this stuff I'm thinking also not just about the M code but also some of the um connectors as well there's a broad library of them like what's the exact count of connectors do you or close count that you know of um today it's is it getting above like 180 or so I know it's really high yeah it's above 180 uh what happens here is that it also depends on what we call the the product integration so if you're an for example it's different from what you're going to see in powerview desktop and even between powerview desktop is also going to be different from what you might actually see in data flows um so so it depends uh there's also custom Integrations that we have so for example in power VII data flows you're going to see AI what we call AI insights uh you're going to also see the uh R and python execution Integrations so those are actually functions uh that show up in in the M language but are specific for power desktop and for the power service and power dates specifically okay yeah and it's the I know this the the web service version of power query has a few extra bills and whistles today which at some point are coming to desktop um do you do you know how soon um we're going to be like because I know there's been talk for a while of the parody between the two um do you know like when the release rele of the of the bridge between those two is going to finally happen so what I can share right now is that we're internally testing uh bills or on how the new modern UI looks like inside of our desktop uh what we're aiming to do is release a public preview of that later this year I don't have Ana as to when it's going to happen but it's going to happen this year uh what we want to make sure is that when we release that is something that people are going to love and that we're going to continues to actually not only just release that and and call it done we're going to continually improve every single month and deliver incrementally uh once it's actually out well there's there's a lot of nice features I mean like everything from the little the I guess the query timer in the in the the lower right that um do yeah all of the different types of incremental or not incremental um the like five different statuses for query folding like we're we're 100% certain we're mostly certain we're kind of certain not really certain and then we just don't know depending on which con you're you're using but all of that extra information and the the dependencies Builder for Applied steps uh for people who are more of a visual learner and wanting to see the uh the path of um Step A to B all of that stuff that's in in in browser I I'm sure will be a welcome addition once eonic comes to desktop yeah there's quite a number of features and not just the features uh behind the scenes let's just call it you know this this new layer uh to actually render stuff it's going to it's going it actually runs smoother like that that's the the overall uh feedback that we're getting internally is that when you're actually using this version of the UI just things actually render faster so overall it's not going to be just a face leftt it's actually going to be so lightweight that things should actually just run faster and that's what we're aiming for it's not just facelift everything behind the scenes is also new so you should feel a big difference I mean outside of the M language it's the whatever in between the M language and essentially the UI like the the mechanics behind that is essentially a new a different engine that that's um exists there that's doing all this which is you mentioned like is going to allow things to load faster but that's partially I think the reason why it is taking as long as it is you're not just changing the the shape of a button and adding a skin like there is a lot of structural changes that are happening in the back end of powerbi desktop 2 to put this whole new interface in there exactly so it's it's not just the the change of uh you know of of the style or the UI uh it's really the overall application that is running it's it's basically different and we have to hook everything together so that it actually just works in in you know in back compat so that whatever you created before with the previous version of the UI Works in this new one and and you can yeah you don't have any any issues when when it comes down to back compat really exactly um and I think uh switching topics from the some of the new new UI stuff from parbi desktop and I think back to connectors and some of the things we'll be getting into today so SDK uh for one for the people who might have seen those letters for what does that stand for uh when you you refer to powerb SDK I think that it's uh it's basically an acronym let me let me find it out because I I keep using but it's basically a set of tools it's software development kit okay uh I see you showing it you don't mind I can also flip over to your your screen if that's okay yeah uh we can just go with this one but really uh the SDK I just Google it yeah it's fine oh yeah but but yeah uh the SDK just it's just a toolkit that we're giving you uh we could have changed the name but yeah it's sare development kit um when it comes down to the parkway SDK it's its own Stalone uh installation uh uh now we can actually talk about it so should we should we start or yeah absolutely I think we can uh you know move into the topic and and kind of talk about what we'll be getting into today with custom connectors awesome so so as I told you before so I have you know the park editor and I own the custom connectors so those are the two primary focuses that I have um when it comes down to the parkr UI uh the parkr editor in general it's primarily about what we call transforms so things that you can do to data that you already have in the power Creator anything that changes or transforms you know the data we call it a transform so if you're changing the data type of something that's a transform that is just a change type transform if you're doing a merch that's a merge transform when it comes down to connecting to data that changes the scope because it's not just about extracting data uh it's also about authentication it's also about the mechanism that you want to use to actually connect to that source and a lot more information that actually happens behind the scenes to actually extract the data from a source so when it comes down to you know you have a requirement where you have to connect to let's say a rest API because we don't have that connector specifically in U in power VI or just in data flows you want to connect to another service could be o data could be um R API be plenty other sources that are simply not in Parkway in one way or another the parkway ISC is the answer um that is again in the event that you don't have a connector for it power desktop or just in power in general so what can it actually help you with so I'm just going to give you um a simple idea of what we can actually do so one of the connectors that I created I created this one like probably like a year before we actually met in person with um and still people use it so it's been like like six or seven years and people are still using it um but this is the power VII API connector this is a custom connector that I created a while back I haven't actually uh updated too much but people are still using it and imagine that you create this connector with the SD uh we're we're going to call it magic so magic to actually create it but then you can use it here in power desktop and for now now I'm just going to connect to it um it's going to tell me that this is a custom connector it's still in preview so it's just a warning but I can click on continue it should have asked me for my credentials which is just all OD but since I already have my connections or or my credentials it's not going to ask me for that but I have a bunch of information in here um of our a bunch of objects that I can connect to there's a bunch of functions like I can actually connect to the bent activity log I just sare the last 10 days of data let's say I can execute some Dax saries I can use a generic get data uh function or I could potentially go into let's say personal workspace and that just give me the reports that I have available so I can click on this one and see all the reports that I have available so since this contains a lot of private information I'm not going to be able to connect to it but the idea is that you can actually create create your own connector and lay down everything that you want to show in it and just hand it over to someone else that could also consume it and that's what has happened with this custom connector so I created this connector a while back and it does have dozens or even hundreds of users out there that are just using it to cre the data from the rest API and just analyzing it on Dr own I remember way back um it does not feel like five years ago but apparent like five six years ago when you were first reaching out to you know some of the the similar channels that we swam in and just trying to get feedback on like hey is there anything missing from this but it it's still as you mentioned it captures a lot of what you need and allows people to basically um do self-service reporting um from a tenant perspective because I think we can both still agree that like Microsoft's gotten better at providing some admin portals for tenants but it there's still a bridge that could be crossed in terms of uh providing more information so people to a degree have to use plugins like yours or other stuff that other people in the community have built to bridge that Gap today yeah so just like there is you know that's one that I created because back then we didn't have anything um or we didn't have anything so compelling you know for someone that doesn't know po shell someone that doesn't know code uh someone that really doesn't know anything about it and just want to consume something inside of powerbi this was the best back then um or this was one of the options that they had back then um but the the overall idea is that I created something back then for powerbi ver API you can potentially create something new for a different service let's just call it like read Heaven service if you actually had your own API you could create your own custom connector and just hand it over to people to actually create it and that's I mean today it's still used a bit for for like weather.com and other places that have apis you know that you can connect to but there's there's no built-in connector and that's when you need to make a custom connector essentially yeah so we do have for example we have generic connectors so we have for example the O data connector we have the web API connector or what we call the web connector in in power but there's a necessity for you to create a custom connector when for example you have to implement some something that has to do with pagination or something that has to do with your own o uh flow you know if you have that set of requirements to connect to your data or you have to submit some sort of uh body or post request uh to that Endo of your RPI that's when you start needing to actually use the SD because we do have some uh considerations when you actually have to use the generic connectors and those are primarily security and privacy reasons is why we might actually block you from not let's say not being able to read the response header or why you might actually have to uh Implement your own um pagination mechanism and things like that and that's when you have to rely on a when a connector would you mind just um digging a little bit further into uh the the the concept of pagination as it applies to like a connect like what what would be a a source that you connect to that would involve something pated to which would then need to rely upon a custom connector so I think that back in uh this was this was 2018 when you created the YouTube template remember that one yeah yeah yeah so for for that we you actually use the uh the Youtube connector that I that I created and that one specific for the Google apis we basically require uh we make our so imagine that I I get some sort of response from you and it's only let's say a 100 records but you also give me you know a pointer to where to actually get the next 100 rows so that pointer is what we call the the next page or the pagination or or the cursor and we can only get that in in the YouTube API response it's actually in the header so with just generic Hari desktop you Cann out actually look at the response header or that specific header from the response header but with the SDK we can actually do that so we can read the whole response header and you can use it however you want but the idea is that from any single request that you get U that you make you get a response and with that response there's a cursor or there's a there's a next page link next your link that tells you where to get the next set of uh set of Records so in that sense it's like you're actually reading a book and you're just going page one and then tells you continue to page two and and you keep going and going until you finish book to think of it like from a database perspective or I guess just from a code perspective it's it's it's a loop like a for each Loop and it just continues to to paginate through until it I'm guessing something just it finds at the end where it's hit the end of the result and doesn't need to go to the page 15 because 14 was the the final page 14 of 14 whatever that might be okay um but it it can essentially detect how many times needs to do that like there's going to be five pages or two pages or 15 um but that's only something as you mentioned that can be done um with building a connector vers is just creating a simple query to to access those things yeah so the over logic is soling M sure but to to make it happen and not hit any of the limitations or considerations that we have from a security or privacy perspective you have to use the SDK so okay that's good to know so yeah so yeah excellent so I think that we can probably go with the idea like let's let's actually talk about the SD because we we have talked about you know we we seen acous connector uh it looks like any other connector really like if if you ask anyone you can probably just tell them that it's just a native connector because they look the same uh and that's the overall idea is that whether you create it with the SD or not it should just behave similarly to any other connector that we have in fact if if we actually check some of the connectors that we have in powerbi today some of them are actually certified connectors so that's are connectors that were created by a by the owner of that service and then they just publish those so we can go for example with um use Cube let's go with uh this one actually Markus te person analytics this is something that is actually a um a custom connector that got certified uh we can go with solar winds um there's quite a few so CIS uh open search fax set and I think if um if you just search for the word beta does it filter to just all the beta ones uh so it does I think so but the only thing is that some of them like for example like IVM INX it's not really a custom connector true um is there any is there any way to tell just from from the UI which ones are or are not custom connectors in the new UI yes in the new UI that we're going to be using it's going to tell you exactly like if it's actually a third party and then if it's actually telling you that it was part of the certified connector program so that you can actually tell them a where you know which ones are actually a built-in connector like SQL database uh and some others that are yeah created by someone else and we publish it's it's kind of a smart move honestly like I I know over the years I've had plenty of people ask me with my Google analytics report is the why is this connector s like so it's not labeled but Microsoft does not like you they're they're not solely responsible for making that a lot of these things and their performance is up to the company who's making them who's giving it to Microsoft to publish so uh yes Microsoft makes a lot of connectors but um performance issues often connecting to third-party companies usually are more of the responsibility of the third- party company providing or a lack of providing a lack of good quality access to their data especially if they're trying to force you to maybe use their own platform sometimes yeah and that's part of the reason why it's we believe that it is a better uh a more scalable program because it is the owner of that service that has full control over the connector so the behavior that they want to see they can implement it uh if if there are any considerations that they can make either at the connector level or at the data source level they have full control of it so they are able to fine-tune everything so that it provides the best experience possible yeah it's it's for For Better or For Worse because as you you're mentioning like they they can make the fastest most performing connector like it was a sad day for me when we switched from ga3 to ga4 for Google just as a quick tangent because you could export nearly a million rows uh with E3 and it's now there's like a 200,000 row per hour limit for G like it it massively neutered the um the ability to export detailed data and I think they're just trying to force people to go pay for Google Big Data or use their own internal reporting services so it's it it went from easy to hard now to actually get data out for for analysis in parbi which is sad but I'm sure they're not the only company that's probably done that especially when they have a a paid reporting service that they would prefer people to use um but it it's entirely up to the company to determine how freely they want to expose their data out for other tools to import them with yeah so that that is you know a decision that they can make and it's completely up to them so whether they want us to consume the data or how they want the user to consume the data it's completely up to them exactly what are some of your fa uh among the beta custom connectors that are in your do you have a a favorite or anything that you would want to call out is good information for somebody to know that it exists today I think that they're all my favorites they're all my children I love them equally yeah yeah yeah yeah so I really don't have anything specifically I I can tell you one thing so if you have issues with the Microsoft themes personal analytics connector I actually created this one before I join Microsoft so if you have issues with this one uh you can believe me with the team s disc connector uh but this one should work this is the one that I I can call like my own because I created it um and that that yeah that happened before I joined marus kind of crazy how things s around like I created this one got certified and I actually have to certify other connectors right it's you you going to be on both halves of the fin so I've honestly never used the team's personal is that just is that statistics in terms of like when you're online offline like messages or what's the what data comes out of that g a try give it a try so there is also um an application template app I believe that it's uh primarily launch in set of teams so if you use teams uh there's probably a an accelerator that allows you to create a report in a matter of minutes with just all information going from this connector but it's primarily you know how how many Mees you had in the last 30 days um amount of time that you actually spent on meetings like perhaps this one you know this might actually show up in that report that that sort of thing um but it's a it's a fun report that allows you to see okay so this is actually my usage inside of themes and it's it's pretty fun so find it I I do see there's there's a Microsoft learn to just analyze your personal activity but um I because I would like I'd love to share this connector's link with with the audience and feel free to either do it now maybe now if you have it ready or just like what would I Google to get this specific thing it's not showing up at the immediate top results I have to figure it out uh let me see if I can actually do some some searches but I'll yeah because there's a CD data software connector but I don't I'm assuming that's not yours no let me see if I can actually find a teams analytics so when Theo isn't go ahead uh Bess teams cqd no uh analy your teams usage in the power app for teams do the one let me see where I can paste this see there we go so this is one oh got it okay and then copy link done all right there we go share it out with the with the audience perfect thank you so this is the one and it creates this report so you can oh it is this one okay yeah yeah excellent yeah so this is the one I definitely have to give this a try then yeah so I didn't create that uh report but I did create the connector so yeah if it doesn't work you can blame me perfect yeah going back to to the SDK um so the the SDK again is just a set of tools that allow you to write the connector and it's all based on really it's all based on on parkour so in M language so if you know M language you're you're perfect if you don't know M language you're still really good because we we we try to bring you samples and we try to bring you all the doation that you need so you can create a simple connector you don't have to make magic to make something work the only situations where you're going to require more test or some more time it's when you're trying to implement your own connector to have thre query capabilities and sometimes it's it's pretty easy to actually just add add the direct quer capabilities but yeah it's it's it's something pretty simple and I'm going to show you how simple that is so at the moment we have two versions of the par SDK one is on Vis studio and the other one is on Vis Studio code we are encouraging people to use the VIS Studio code one so I'm going to show you the one on Vis Studio code so you just have to download visual studio code and you can search the marketplace for the power SDK so the power SDK uh has a couple of components one of them is it basically going to allow you to download the engine to actually run the um the power C scripts and you actually evaluate power script uh it has the components ins of the actual extension or the addin for Vis Studio code so on the left and it also comes down to yeah we're we're going to see it later but it's the power query language service so this is also another BS code uh extension uh but this one has many many more years and many people have been using this one um Prim just for styling or formatting their own M code it doesn't allow you to evaluate the code but it does allow you to just write it and yeah take advantage of everything in terms of code formatting that we actually have in in this specific um yeah yeah this was one of the first ones that I installed um and I believe this this exact extension is one of the ones where I I did the name's alluding to me but last year I did a live stream where we went through about 10 or 15 of the like essential add-ins for visual studio if you are going to be doing power query or um powerbi stuff and this was one of those cornerstones that you should have installed so the good thing about this one and just this this is more of a mention but imagine that we're working on improving intelligence and syntax highlighting inside of the new parkare UI so for parkare inline it's also going to reflect in part of desktop and it's all going to be based from this language service so the behavior that you actually have in here it's going to be exactly the same behavior that you're gonna have in the park rator and formula bar in the advanced area uh so if you want it'll be nice to know because yeah go ahead yeah we're gonna have it this year so that that is that is something that people are working on at the moment so I'm super happy to say that um and that was something I hadn't thought about but the like that is actually going to be a big upgrade I I know that definitely for some people a paino is the the autocomplete intellisense mentioned which works most of the time but every once in a while it fa it fails to like populate a list when you're typing um in a formula bar or something like that so using this is a lot more intuitive so it's nice to know that essentially this this logic is what's being moved over yeah so this logic and again if you give it a try and you have any feedback do share with us because whatever is going to modify this Behavior it's also going to impact whatever we're going to have in in the future version but the the over idea is this language service so the the same behavior that we're going to use in the S because the S uses this uh has a dependency on this Behavior it's also going to be reflected in the par cray editor in par cray so the overall idea of of the new UI it's what we call Convergence so in every single power crate product that we have or any single product interation that we have we will have the same experience so whether you're on a Excel for Mac because we do have power qu in there you're in for Windows you're in power V desktop you're in power VI data flows or you're in fabric data flows it's going to be the same UI across every single one and it should be the same expectation so even in terms of formatting of the code it's going to be exactly the same uh that's awesome yeah so it's gonna be familiar regardless to where you are so if in the future we're GNA have like a verion like an application for par crate on your iPhone or something it's going to be exactly the same um the uh I I know that's been a a request a couple of times over the years because it's such a great easy data transformation tool just to have like a essentially just the power query editor just as a standalone like thing to allow you to to go from location a to location B and just have it be a transformation um tool but the Cur curiously on the the taking it back to the comment that you mentioned earlier about providing feedback where would be the best place for them to do that if they were to install this inval studio code they wanted to give feedback for for for this um where should they go to so there's a couple of ways that you can you can do it you can do the rating and review uh which is just going to allow you to send a a a basic you know comment or what you can do is you can actually go to the GitHub repo that we have I believe that it's from here in Project details so you can click here on the project details it's going to take you to the GitHub repo and instead of this GitHub repo you can click on issues and create a new one okay okay perfect I will make sure to include that link funny is the top two reviews in there was from uh Stefan and Alex Powers two individuals that I know quite well so those were I think those were the last two ones so yeah if you want to write the review or report an issue through this page you can always just go to the G rep that we have and actually make it happen here so the last one was open by Emma um two days ago okay yeah so they like the it is fairly active in here which is good yeah so either we either fix it or we try to tag it as an enhancement so that we can actually address it in the future uh but we're actively working on this and and yeah people who work on this are pretty pretty active um one of them one of the engineers that is working with this is called Jordan so Jordan is like this this is his baby basically and he's really excited to make this happen no that's awesome I'm glad to know that uh at least the the feedback is welcomed and get get makes it very easy to to do all that so um yeah get encouragement for people to install and provide any commentary or uh um feedback testing as possible yeah absolutely so I'm going to I'm going to give you a quick example on how to create the connector and this is going to be super super easy so I'm going to go with one of the easiest ways to actually create a connector or one of the W RS that we recommend so this is called uh the pin tutorial and what happens here is that we're gonna show you how to create the connector so first uh let's go with the first one first part just this one creating a basic o data connector so we creating a basic old data connector it's going to have some of the images right right now that are on the oldest SDK in Visual Studio but just going to create it super easy let me see if I can actually paste this in the chat so you can share the link as well uh people can follow if they want I'll grab it from you there we go so imagine that I have I have the Studio code open and this is this is basically what it looks like so it's it's super simple super lightweight that's that's the whole idea behind BS code and I'm going to go to the section that says power CK it's already installed so it already installed everything that it needs to have and I can click where it says create an extension project so here I have to input the name of this new extension project so I'm just going to call it Tri um let's just call it 20 24 so tripping 2024 is going to be my connector name I have to find exactly where I want to have this so let's say that I create a new folder and this folder is going to be called trip pen so that's what we call the workspace which is effectively where you want to um host all of your files and it's going to create this it's going to use a template to start the project and that template what it has is a bunch of um images just pgs these are just placeholder images that we use to represent the connector so these are super simple uh this actually pretty small stuff let me see if I can actually show it yes I'm going to show it to like that great but it's here it's just an image of a connector and then we're going to have basically the two most important files one of them is the PQ file this is where I have all of my logic to actually run the connector and this one that we call the. PQ is the one where I can try the connector or basically evaluate it to see how it works so those two are the most important ones you're going to find some other files that we have like a B file where we actually host um the the published connector which is the MTH file and then we have some information about the settings on how the SDK should run for this specific connector for now we're we're not going to talk too much about those let's just talk about tpen so tpen if we go back to the documentation it's effectively an old data service so first thing that we need to figure out is what is the URL for that s service and this is it so it's this little URL so if I put it here I can actually see a result so what this is telling me is that it doesn't really require any sort of credentials so I could potentially create something super easy without any credentials and just test it out so I'm just going to grab this from here just going to copy I'm going to go back to my P studio just going to paste it here so this is actually changing uh the names it's actually called tripen now instead of tripen 2024 no issues with that then we go with the data source kind this is basically a record and what it's telling is that basically like an attribute telling that this function that we're saying here which is called tripen fed is going to be using the data source kind tripen so when we run this function tripping. feed is going to use the credentials for this data source and what this data source is the triin is this information that we have here so trien as a data source kind it's telling me that it has an anonymous Authentication and it actually has a label for triping part one this label is just going to show up as a title when you get the authentication or asking for so it's it's just that and then we have something called the rep published which is more around how are you going to see this connector in the get data um browser or like connector list so it's going to tell you that yeah it's going to have the beta beta equals true so it's actually going to have in in parenthesis is the beta it's going to be in the category called author and then the text for that button is going to say tripping o data or tripping o data you can actually pass yeah let but in this case we're super cool with what we have at the moment I'm going to save it I'm going to go back to the qu the PQ and I'm going to just change this because it's actually not tripping uh 2 for contents what is actually tripping. feed now that little thing can I I can actually just copy it here as well she just going to copy it or we can actually use tripping. feed and then pass this URL I'm going to save this one I'm going to evaluate and basically build my connector so I can run things against it so I'm going to go to the teral I'm going to say run build task I'm going to say that I'm going to use the make pqx it's going to compile it and now it's going to basically overwrite the mes file that I had here now that I have this I can do two things look at what happens at the park for SD section it has now a set of actions you can click here where it says set credential uh I can just the enter here so it's going to be for this one it's going to be just Anonymous think that it's missing something right you can actually just clear all credentials set a credential it says look like it was failing to delete the credentials yeah let me see what we can actually missing was going some kind of an error unable to connect or something like that yeah let me see what could be happening here oh yeah it's actually the the Json the XML that I have here now no no no no I think that What's Happening Here is that since I actually used Tripp in 2024 yeah I thought that it was wasn't going to have affect me but yeah it's it's affecting so let me let me try this again so I'm gonna change this let's go ahead and evaluate it again there we go I have something open that I shouldn't have give me one second no worries so I'm going to go with settings let me just disable the service host there we go there we go let's try to evaluate now yeah it's going to require me P test found there we go it's very good there we go we are good I'm going to open this evaluate just set the credential first verus kind there we go we can evaluate this now okay so we're making progress so it's now actually showing me perfect the error there we go failed uh triin 2024 is actually this is basically the the new way in which you're going to see um the yeah that you're actually going to see the data preview so you can actually see everything that is actually capturing here so this is a full error credential Mission Miss this is data source this is a summary of what actually happened so I'm going to make sure that we built again going to make it happen make pqx there we go so now we're good and we're going to make this set a credential [Music] there we go so now it's actually showing me I can actually close this one just this one and let's just click on set credential now it's actually showing me the tpen as a data source saying that it's Anonymous there we go we can list the credentials so it can actually show up down here there we go so it's tell me that for the tripen kind this is the path that it actually use which is this URL and for that path we have the anonymous authentication so now when we run this it's going to actually show me the the data that we need so you're not going to have to actually enable or disable the service host that was something that I had I should have actually disabled that one before okay that makes sense just going to tell me what is the output of that feed and really is a navigation table so it's just going to tell me that hey we have the airlines table we have the airports table we have the get nearest airport a function and other tables so this is what we call uh navigation table so the idea with this uh connector or this guide is that it's going to allow you to create h a better or just a guide to create a connector on how it might actually look like um let's say that if you have uh that resource so I'm going to change this part um we can have side by side window of course let me just change this part of the test actually change this as well so we have this one the credential can remain the same but what we're saying here is we're going to connect now to that service we're going to go to the source which is the previous step and navigate to the people record and navigate to the data column so this table that's actually here and select columns is just going to select a few columns from that table so it's going to be just username first name and last name I can just right click this uh document and say evaluate current Park rify and now this output is just going to change to show me the data from the people table and just those three columns username first name and last name so it might actually take a while um this just because it's just doing some Revelations behind the scenes but you can always just check what's happening um username fault of type which is not allowed that one's actually a different I never seen that one before but this is a 500 internal server error usually means the airor is happening on their end not your end right yeah so that one that one's yeah that that was funny but let's let's go ahead and and try to make it happen again to see in the meantime let's see what else they running the test I don't another thing that you can do and and this is just because it's just park right you can actually change this one to be instead of Select columns you're can actually make it just people so let's just navigate to the table instead of actually doing any set of Select column but what we were trying to do with that approach is that behind the scenes uh it should fold so if you're using something like the O data feed uh connector or or just the O data connector it actually tries to fold all the the resources that it has not sure what's actually happening with this one username nul equals FS of type EDM string has no value which is not allowed I'll have to take a look at the to this one uh but for now let's just say that for Source it should evaluate correctly I definitely have to check on why this this giving me those isues there we go so it is working here um but this is just again just giving me the the list of the tbls yeah yeah okay I could potentially try this with something else as well so let's just say that I use the norwind service this is another one that doesn't require um any sort of you know anything um in terms of authentication so I can just say let's comment those out those two let's remove this coma put this here and I'm going to change this to be tripping the feed it's actually going to connect to the norwind service if we do this uh it's going to tell me that it doesn't actually have a credential set because this is a different link so it should error out and tell me that hey we don't have a a credential um it's going to tell me that I'm both the console and also in this preview so to add that credential I'll have to actually go into set credential and make that happen in here so let's just see how long it takes doing the operation devaluation there we go so CR are required I'm going to go ahead and set a credential it's going to tell me that it's going to go for test file this is the test file just going to say that it's Anonymous there we go so gener in credential and can go to the console and see that it's actually making that credential and it created the credential if I still want to see a list of all credentials going I always just click on list credentials and it's just going to list all of them I do have have two now so one is for norwind and the other one is for tripin so let's just go ahead and see the one that we have for uh norwind so I'm just going to close this one and click on evaluate current file and that one same thing is just going to give me a navigation table of all the tables or entities that we have available in that service in Northland um the Major Impact or or for this specific case why want to create it against a old data source is that you want to actually package this and distate it with your own branding that's another one of the big reasons okay so if you own a service and you really want to make sure that people know that this is your service uh that's another reason why you want to create a a connector so you can just create with your own o data source the packaging or the creation of of the connectors should be pretty straightforward it's like using the triend fe uh or or the tri walk through um but yeah that's that's another good reason why you want to go with with that approach so I'm going to go ahead and I'm going to go with the next approach and this is instead of just using the old data feed let's say that we want to actually look at something different and that is using the web. contents approach so I'm going to change um what I have in my connection definition and that connector definition is going to have instead of using old data feed it's going to be of the comp I'm just going to change this down here and there we go so it has a definition here called default request header so it's missing that so we got to figure out where that is I think that I made need some uh yeah I actually went a little bit further but let's go with request headers which are this ones and we can Define that just down here so we got tripen value the replace type there we go default request headers application and then the old data Max version 4.0 so this is just a record field where we passing it as the headers and we're using the web. contents approach so now if we want to evaluate before we can evaluate things again we have to actually make sure that we change this sure but we also have to build because this is a new version of my connector so I have to build this connector and then test it out so I'm building this version of my connector and then I can use the other one well this is actually happening let's just go back to the sample and let's go ahead and try this with this specific sample C so there we go this is stom fit for now don't don't really struggle too much about this weekly um line or red red line underneath uh we're working on effects for this but okay this will notice this will not stop you from evaluating the current Park file so we can click on that uh it's using still the oh we're going to get required to actually enter a new credential because this is actually a new URL and this URL is actually going to go against the airlines so just going to click on run test and let's just say that I'm G to cancel it I can always cancel it whenever I want I think that it actually worked um because it used the the main path the main route but you can always add another one that is specific to that URL so I can go here and say seta credential go to trien this one Anonymous there we go just generating the credential there we go and this is quite specific to that basically that URL which is St lines so I can click on list credentials let's go ahead and click on L credentials and now I see all of them which are three so we got Norwin this one Trippen the root and then Trippen but just the airline Sandpoint and if we look at what was evaluated we can see that the evaluation gave me the Earline code and then the name so if we look at the table expan record it does say that we got the airline code and the name we can always look at the summary the summary is going to give you quite quite interesting information so if you want to look at how long it took to actually gather that information from here just 10 seconds um so you can also see that duration and just look at how long it actually took and if you want to look get information about what what was the data source that was tested you can also do it here the included outputs and tabs are are are pretty nice to get quick uh quick information about it yeah you can actually change it uh the position as well so it's completely up to you how you want to see it but that's the that's the beauty of of V Studio code but yeah we we can continue with with this and the overall guide is just try to to to look at what's actually being implemented here the M code that we have here it is explained in this walkth through and the overall idea is to show you what scenarios might actually need a custom connector why you know it's important to actually use a custom connector how you can customize it for something like a navigation table because you can actually have your own hierarchy of objects and you can Define it exactly as you want um and then more stuff that is quite important like pagination if you want to check the schemas and make sure that uh you don't make Power C do extra uh calls that it doesn't need to do and then just Diagnostics and you know connection testing when it comes down to using this in the powerway service because you can use the custom conectors in the powerway service and that is really not uh yeah not a block not limitation the only thing that you need to do is just make sure that you implement what we call the test connection and test it with your gateway and then you can make it happen in the Gateway any questions here I um yeah I had a couple actually that I had queued up um so let me pop up uh the first one here so when using a custom connector consuming a rest API you do still need an on- premise just data gateway to use this connector in the service um are there any ways to work go around the data Gateway requirement at the moment uh no um what you need uh or the only path forward is if you want to make this happen and you want to ship it a spot of regular power vi just built in you have to go through the certification process so the certification process is where the owner can go into this and make it available to everyone without having to load it somewhere uh the need for the on premises Gateway right now is so that you can have somewhere that definition of the custom connector uh so that it can actually evaluate it so the Gateway is actually it actually has the power cor engine in it so that's where things are running and that's where the custom Lor needs to be um should you want to use it and that makes sense I've had to to tell clients a few times on that it's it's due to the requirements of where the as you said the engine is ran and the location of any custom connectors need to be put because it can't necessarily be injected into the the cloud service itself exactly y the and then one other from Air the the teams connector can we pass the um I guess the one that you buil can we pass the in the teams ID or user IDs dynamically or is it restricted due to privacy feel like you've been ask before with a smile I don't think that we can actually pass anything uh it's just it it was meant to be as something super super straightforward so that you just sign in and you get your own St statistics so it's just analytics um so you can still you know filter it but it's not going to just get the data from that one it's always just going to give you the analytics for every single site or channel that you have access to okay no I I I was figuring similar um for that as well but um it was a good question and um as we're getting close to wrapping up if there are anybody else who has questions in the chat feel free to to drop them in um to ask Miguel or myself as we start to wrap up but I think this has been very informative and I also just like the the integration that Visual Studio has I think it makes it a lot easier than I I mean it's been a minute since I've written a custom connector but I know before we uh we had such a um a wide use of Visual Studio there it was it was a lot harder to write this um without the uh the automatic uh testers and all the the outputs that comes with the plugins plus the the mcode intellisense that's built into it so it is still a technical thing to do um there's no way around that it's yeah it is something that requires uh some amount of mcode writing but at least there's a lot more system tools to make it easier to build your own when needed yeah I actually got a couple go go ahead and we have a couple more questions when you're done yeah the other thing that I wanted just to to um you know to not is that we do have quite a number of samples so if you're going to I'm sure if you want to share the screen right now but uh when it comes down to samples we we have quite a bit so we have functional samples we have walkthroughs um we have other connectors that you can actually take a look at um so that's that's always a good good thing if you have any questions about it um we we do have a community so in the the power cor isdc uh uh GitHub if you have questions about the SDK or just customer in general that's the best place to actually ask questions about it so so definitely take advantage of the GitHub repo that we have uh we do have a discussion section for that so any questions are are welcome excellent and then yeah I'll continue to encourage to check out the GitHub link that I dropped in um the chat a little bit earlier and let's do a couple more questions and I think we can use that as a good wrapup so so yes another good one from Amir for the deployment of the connector how and what is the best way to deploy for an entire workspace to allow users to leverage the connector it it really depends um so we typically just let people Define what is the best for their organization um but what we have experienced in the past is that there really just an handful of people in the organization that do require the connector and let's just say that they can use that connector and deploy a solution and then the rest are just consumers of those reports which is pry pry common so it really depends if if you only have a handful of people that are using your custom connectors internally um probably just send them an email or just send them the connector file which is a mess file however fed but if you are the owner of the connector you're the owner of the service and then you're Des servicing this to your own connect your own customers let's say uh which is something that some companies are doing today what we have found the best is if you just have um a simple documentation page where they can just download it from with some instructions and then uh the customer can find out what will be the the best way to deploy it or just to share it in general because at the end of the day it's just a a file that you have to put into a folder inside of your machine running power desktop okay no that's um excellent information and one last one which actually I think is is a great question to finish this off where can we use custom connectors so I'm going to translate this from just data flows is can we use custom connectors anywhere outside of like powerb desktop any of the cloud versions of power query are they able to leverage custom connectors so we we are working on enabling custom connectors for power data FS should should happen this year uh but yeah so the overall goal is whatever you use power we try to standardize as much as possible The Experience so if you can use custom connectors in bar desktop you should be able to use them in data flows and maybe someday in Excel uh yeah so oh yeah yeah there's also a few places in Azure where power query can be is it and I I don't use Azure nearly as much as fabric C can you use it to ingest into synapse as as well I know there's at least data pipelines for synapse so that one that one's a bit different because that is the integration with the ashro data Factory and it's called was called rankling Data flows now they're called Parkway data flows uh behind the scenes what's happening is that they're not using the M connectors or what we call the par cray connectors but what they're using is their own connectors which are I think it's actually on um spark base connectors or spark friendly connectors and they're leveraging everything that happens in the UI in the park rador to be translated into spark so it's not really power cray running it's like a it's really just spark running behind the scenes it's just that power cray is able to help you create spark code okay okay so it's not really Parkway running and what we're trying to yeah what we're trying to encourage people to try today is data fion 2 so if you have a fabric license go ahead and give it a try if you haven't tried fabric just yet definitely this is the time to give it a try I feel like this is one of those situations where were similar to when power VA started it's it's quite a a new thing that we actually have so if we have you just fabric the mic comp so that's all you need just fabric the micc and should take it's really easy to click the button if you have permissions to start a trial and the one thing that I continue just to mention to people is when they think of fabric they think of powerbi Premium um a lot is an analogy and like the cheap premium is 5,000 a month the cheapest fabric capacity is 250 or something it it's a much lower bar of entry for anyone and you get an easy 60-day trial to sign up and poke around on it and decide if you want to use it or not so um it it the the um any any level of inertia or difficulty to get into it is is pretty minimal if you want to just poke around with it definitely and you don't only get like data flows and power yeah you get so much more so definitely one of those yeah just just amazing how many things you actually get for that price um yeah so definitely give this a try if you can look for fabc this a trial so give it a try uh do we have any other other questions anything else I wish should go I don't I don't think so just an appreciation for answering the questions and um yeah otherwise uh overall people in in in enjoyed the uh the session today so this has been really good let me switch back to us here yeah um but as always I appreciate you coming on for this I like talking shop with power query um between you and I think Alex P is the other one who loves to talk de Deep dive stuff um with that and it's it's one of my favorite tools it's to this day it's still the one one of the easiest costs of Entry when I try to mention to people like you know why is it worth going from like Excel reporting everything into you using power BR or anything else is the the amount of time saved from automating manual data transformation processes for a lot of companies like alone will handsomely pay for any additional cost to use uh these services so it's still one of the reasons why I think someday they're could be a benefit of just having like a standalone tool that just lets you transform data because it saves you so much time versus old school methods which a lot of companies still do with vlookups and using Excel as a database beautiful um but yeah otherwise I just want to yeah thank you for coming on taking time out of your Friday for this and um as we continue to evolve the product and more awesome announcements and releases comes out I'm sure there'll be plenty of opportunities to get you back on in the future to talk about some of the other exciting changes as this product continues to evolve yeah I know uh look out for what's going to be set during the fabc conference so in a month from now it's going to fun times I'm assuming you you'll be there yeah I'm hoping to be there I'm hoping to also be yeah to be in Seattle for the max of MVP Summit as well so ex a couple yeah a couple of weeks I'll see you a few times then uh next month I'll be here for the summit of course and then I'll um myself and Kurt buer will be coh helming a a presentation on on fabric uh at the fabric conference in Vegas so two two times at I'll see you and see bit in between it's next month's going to be a little rough yeah yeah hey thank you for having me man yeah absolutely enjoy the rest of your weekend everyone um enjoy yours as well uh I'll have recorded videos continue to go up uh on the channel but I'll be offline for a few weeks I'm going to be in Japan for about two weeks so no live streams for for a bit but um I'll see people back online again in March all right take care and yeah have a great weekend cheers bye thank you so much for watching please consider hitting that like And subscribe button and if you want to help support this channel take a look at our Channel memberships or our merchandise store for cool swag and last but not least please consider sharing this video on your social media platform of choice to help our Channel grow so until next time

Channel: Havens Consulting

Views: 1,190

Keywords: Power BI, PowerBI, PBI, DAX, Data Modeling, Visualizations, Tips & Tricks, Power Platform, Power Query, Power BI for Beginners, Power BI Training, Power BI Desktop, Power BI Best Practices, Power BI Relationships, Power BI Dashboard, Power BI Tutorial, Power Query Excel, Power BI Versus Excel, Power Query Tutorial, Power Query Functions, Power Query Parameters, Power Query Editor, Power BI Service

Id: 6Byf-VzahfM

Length: 80min 45sec (4845 seconds)

Published: Sat Feb 24 2024