Introducing the Power Query SDK

hey folks how's it going thanks for checking out the video i'm johnny today i'm going to be talking about the power query sdk [Music] so what is the power query sdk if you're not familiar with the acronym sdk stands for software development kit the power query sdk is an extension for visual studio what is visual studio i hear you ask it's microsoft's integrated development environment for creating computer programs websites and apps if you're from an enterprise bi development background like i am you're probably familiar with using it with sql server data tools once again microsoft provide a community version of the software that you're able to download for free once you've installed the visual studio software then to add the power query sdk extension go to the extensions menu in the ribbon select manage extensions search for power query in the search box and select download from this first option you'll need to close visual studio and restart before the installation can take effect now my understanding of the power query sdk is that primarily it's for developers to create power bi custom connectors i don't think i've ever even used a custom connector let alone written my own so why might the power query sdk be useful well it also lets you create power query files and the interface for doing that is its very own m client which allows you to write and test your own m as you go let's take a look firstly in visual studio you need to create a new project to do that go to file new projects and here you'll see a template for a blank pq file select that click next give your project a name click create this launches a blank query screen where we can start to write our own query to begin the query we use the keyword lab then we define our steps the first step i want to create is connected to my data source which is a sql database and the server is just my local installation and the database is a contoso retail dw [Music] to return the step use the keyword in and then specify which step you want to return hit f5 to execute the query and the first thing will happen is you'll get this error message don't worry you haven't done anything wrong it's just asking you for credentials to be able to connect to the database so if i select windows from here and set credential now i want to hit f5 i'm good to go now my connections my database is working the next step i want to define is which table i want to work with i reference my previous step then specify which schema which table within my schema and i want to return the data and make sure that i return this step f5 and there's my results effectively what i'm doing now is live querying my database using m as a query language let's head back in and manipulate the data some more this table's a little bit too wide at the moment so let's specify which columns i want [Music] [Music] so now i'm returning just the columns i want to be displayed in this particular query and then if i want to i can start manipulating that further too for example this store name and store description both returning the same piece of data my entire data set is contoso though so that prefix seems a little bit redundant what i'm going to do is i'm going to trim the text from store name to get rid of that contoso prefix keeping the full description in store description but just having a friendly name for that store name so this step is saying to look in the previously defined select column step look for the value contoso with a space replace it with a blank and do that in the store name column always make sure you change the step name reference at the end to get the most recent step name [Music] and f5 to execute and as you can see i've manipulated the store name column to no longer have that contoso prefix where's the value though so far i haven't achieved anything that i couldn't do just in the drag and drop experience using power bi desktop and this is where m as a language ends up being a bit neglected for me certainly it's always been the weakest link in my power bi chain the trouble is the drag and drop experience in power bi desktop is too good why bother trying to actually learn them when desktop does everything you need my only real answer is that knowing this stuff a little bit more in depth makes you a better developer it was anywhere i started to play around with the power query sdk that i felt i started to understand m and power query better for example i started to mess around with nesting steps so for instance if we return to the mquery we've written so far did you know that instead of doing this in four distinct steps you can actually do it in one let me show you i can replace this reference to select columns with this code here i can replace this reference to table with this code here [Music] and finally i can replace this reference to source with with this code here i can then delete these steps and just have this as one big long line of code and if i hit f5 now i end up with the same result i can even take that piece of code [Music] i copy that go to the power query editor in power bi desktop and select a blank query go to the advanced editor and paste my code here you see that i'm able to load that data in power query using only one applied step personally most of the time i still use the power query interface whether that be in power bi desktop or the online experience using data flows but i did find the sdk a really great tool when i needed to do something a bit more complex and i do like to use it when i'm creating my own m functions and i don't know if you spotted it but all of the intellisense in the power query sdk works as you expect not the little gremlins that you get in the power bi desktop interface now i don't think that this sdk is actually going to replace that existing power query ui but as a means of exploring and experimenting with them in a bit more depth i think it's a great addition to your tool belt so let me know what you think have you used this before if not now that you do know about it to think you might use in the future i hope that's been useful as always if you've got any questions or feedback please do head into that comment section below if you have enjoyed the video please give it a thumbs up and if you'd like to follow along for more power bi content please do subscribe to the channel thanks once again for watching and i'll see you next time [Music] you

Channel: Greyskull Analytics

Views: 5,709

Keywords: Power BI, PBI, Power Query, Visual Studio

Id: uuSSimLWe3g

Length: 11min 1sec (661 seconds)

Published: Tue Jul 27 2021