Power Platform dataflows

hey this is Matthew with bi-polar and in today's video we're going to talk not about power bi data flows but power platform data flows bringing power query online and that full integration and connectivity stack into the power apps experience with integration into power bi as well I hope you'll join me if you're a frequent viewer of this channel you may recognize there's something a little bit different about my appearance that's right today I'm not wearing a shirt with a sword on it the reason for this is today's content today's video is going to be a little different as well because we're not talking about power bi we are talking about data flows though specifically we're talking about the new power platform data flows capability that's been nga for around a month it's been in preview for about half of the year this year we're gonna take a look today at how power query online fits in with power apps how we can use that familiar data flows capability to load data either into the common data service or into Azure data link storage gen 2 and then we're gonna look at how we can integrate this in with power bi as well let's jump in and take a look hands on come on alright with power platform data flows we're going to begin in the power apps makers portal this is where we would begin making an app this is also where we'll begin making a new data flow in power apps so if we come here first of all notice that we have all of the options that we would expect to see as a power apps citizen app developer I could create a new what happened create a bunch of things but the main thing that I want to do for this video is I want to come down here in the data category in the left navigation menu and I want to choose data flows and then I'm going to choose to create a new data flow now you'll notice that we start off with an option to give our new data flow a name which is pretty obvious but we also have an option that may not be super obvious if we haven't looked at Power Platform data flows before and that is do we want to choose to load analytical entities only now the label here isn't super meaningful to me at least but if we select this then the data that is produced when we refresh our data flow is going to be put into an azure data like storage gen to account that we've specified in our own subscription if we don't select this which will be the default if we don't select it we will be the loading entities in this environments common data service instance and this is where we're going to begin so the first thing that I will do is to give my data flow a name and with that name assigned I will choose create alright at this point data flows in and power apps are probably looking a little bit more familiar here we see that same data source selection page that we would expect to see in power query online if we are creating a power bi data flow and here I will choose as your sequel database as my data source I will provide the credential information and because I have connected it to this data source before I have my credentials cached so I will simply choose next now here I'm connecting to our adventureworks sample database and what I want to do is I want to begin with the product table and I will choose transform data to use this as my starting point well do some basic things like giving it a better name let's come over here let's get rid of some of the columns that we don't need let's expand out this product category column because we want to have the name for the category included with our product information and over here we will give this a better name and now we are ready to go we've defined a single query so we have one query defined to build one entity and when I choose next I am now asked what is the entity that I want to map this to now if you've used the map to entity capability inside power bi data flows even though it looks similar we're performing a different operation here the key thing to remember here is that when you're using power bi data flows and you say to map to a standard entity you're performing a metadata operation when the data flow and power bi is refreshed you're creating a CDM folder in the underlying Azure storage account and the model that JSON file for that CDM folder is now going to contain references to the standard entity and attribute names and metadata inside the data flow metadata definition what we're doing here in our power platform data flow is different because the common data service that we're loading the data into this is a single instance data source the power apps environment that we've selected has one instance of the common data service in it and what we're doing here what we're doing is we are saying into what entity in the common data service do we want this data to be loaded so here if I say I want to load to an existing entity I actually have a list of what are fundamentally tables that exist that I can load data into similar to a data source destination in an integration services data flow what I want to do instead is I want to say load this to a new entity so I'm actually going to create a new table and we'll fix the typo here as well there we will create what's essentially a new table in the underlying c.d.s instance and I then will need to come through because we're actually loading this into a table I need to say what are my key fields so I will choose name as the key field and that will also choose name as the primary name field this metadata is used it's stored in the common data service and is used by power apps and other applications that use CD s as a data source with this done I can also tweak in tune additional data types as necessary but I simply want to stay next and here I can either refresh manually or I can set up an automatic refresh schedule as well for this example I'm going to choose to refresh manually and I will create the data flow in my power apps environment now as you can see here as the load is progressing is that data flow refreshes there are additional steps being performed because the destination is not the data lake it is the common data service so the progress information is going to be a little bit more detailed with more steps involved but under the hood we have that same mashup engine with the same connectors and the same connectivity stack that you use in power bi and power query online the same capabilities are being used here as well it's the destination that is different you now that the load is complete let's take a look and see what was actually done so well she was done we'll come here and we will look at the entity is in our common data service instance the easiest way for me to do this is to sort by entity type to put the custom entities at the top and here we can see that we have created a new product with category entity inside of common data services and this will now be available for use by any power app or other application that is built that uses the common data service inside this environment and the data will be refreshed each time that our data flow runs let's create a second data flow to look at the second path that we could take when we created the first data flow we left this analytical entity is only option cleared this time we will select that checkbox and instead of loading the data into CTS we're going to load the data into the azure data like storage instead what I choose create the experience will be the same and I will choose the same options if I'd been thinking I would have copied the Emma script from the entity that we just created it as it is we'll go through the same steps very straightforward in power query online notice that we're not being asked to map this to our to an existing entity in c.d.s and we also now have the option to send refresh failure notification to the owner I'm going to leave the default settings selected here we're just refreshing manually we'll choose create and now we will save and refresh this data flow now that the Refresh is complete we can look at this analytical data flow data in two different ways the first way that we want to look at it is in Azure because power apps for power platform data flows it does not have the same concept of built in Azure storage like power bi data flows do in order to use analytical data flows in power apps so analytical power platform data flows we need to configure our own azure data Lake storage account so in order to set up this demo I needed to have an azure subscription I needed to create an azure data like storage gen 2 resource following the instructions in the documentation and once that was done power apps set the necessary settings and permissions on the resource and it created this power platform data flows file system in that resource that I have configured now inside here I now have an environments folder inside this I have the environment with the auto-generated name that power apps has created or defined and inside this I can see that adventureworks analytical data flow that I'd created so when I double-click on this I see the same contents that I would expect to see inside a power bi data flow I see the model dot JSON with the metadata I see a folder for each entity that I've created and when I go into that folder I will see the same data stored in CSV format because it is a CDM folder that's being created under the hood now once it's here in the lake I can work with it using any Azure tool as I normally would and I can work with it power bi as well let's jump over into power bi desktop to see what the experience looks like there all right Here I am in power bi desktop I want to begin by getting data and I don't want to choose power bi data sets or power bi data flows so I will choose more and then I'll come into the Power Platform category you'll notice that there are now connectors for both power bi data flows and Power Platform data flows and I will choose the second it will automatically connect to me using my organizational account my asher Active Directory credentials and inside the navigator that appears I have two different options if I were to choose workspaces I could see all of the power bi data flows that I have permissions to access so even though I have selected it the Power Platform data flows connector I can consume and work with power bi data flows inside this same flow no pun intended but what I want to do here is I want to expand out the environments folder and inside this I will see all of the power apps environments that I have permission to connect to that contain data flows that I have permission to access so the one environment that I see is the one that I expect it's the one that I've created for the blog and inside it I have any one data flow that we created earlier and when I select and choose load or transform data I will get that same data consumption experience that I would get from power bi data flows as well now as we've seen today Power Platform data flows are a new capability that's complementary to power bi data flows and it gives app creators and power apps users the ability to perform their own self-service data preparation to bring data into the system through power query online so using the tools that we're already familiar with to bring that data either into the common data service where it can be used for operational or analytical purposes or to bring that data into Azure data like storage Gen 2 for analytical purposes either in power apps other power platform tools including power bi or any asier service with permission to access them love to hear what you think about this new exciting addition into power apps and the power platform in general and what you think about having data flows in another location can't wait to hear what you think we'll see you next week yeah that's right if there's one complaint that I've heard since data flows were introduced in power bi is that people around the world say why won't Microsoft use the name data flows for more features in more places in more services we only have them in power bi and Azure data Factory and the other feature in Azure data Factory and sequel server integration services as well isn't there some way some other service where we could have data flows - the answer is yes Microsoft is listened today we're going to take a look at Power Platform data flows very similar to power bi but we're gonna bring the power of power query online into power apps let's take a look let's see how this works

Channel: BI Polar

Views: 5,805

Rating: 5 out of 5

Keywords: Power BI, dataflows, Power Query, Power Query Online, video, Power BItes, Mashup, Power Platform, demo, Power Apps, Power Platform dataflows

Id: 4QxjZP--0so

Length: 18min 34sec (1114 seconds)

Published: Mon Dec 23 2019